EF returning old data

[Matthieu]

Hi everyone,

I'm making a very simple CRUD console application using EF and sqlite.
I have 3 domain model classes, being: Player, Tournament and TournamentEnrollment

    public class Player
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string FirstName { get; set; }
        [Required]
        public string LastName { get; set; }
        public string FullName => $"{FirstName} {LastName}";

        public ICollection<TournamentEnrollment> TournamentEnrollments { get; set; }
    }

    public class Tournament
    {
        [Key]
        public int Id { get; set; }
        
        [Required]
        public string Name { get; set; }

        public ICollection<TournamentEnrollment> Enrollments { get; set; }
    }

    public class TournamentEnrollment
    {
        [Key]
        public Tournament Tournament { get; set; }
        [Key]
        public Player Player { get; set; }
    }

In a DAL layer I created a repository with a few methods.

public class Repository : IRepository
    {
        private readonly TmDbContext _context;

        public Repository()
        {
            _context = new TmDbContext();
        }

        public Repository(TmDbContext context)
        {
            _context = context;
        }
        
        public Player ReadPlayer(int id)
        {
            return _context.Players.Find(id);
        }

        public Tournament ReadTournament(int id)
        {
            return _context.Tournaments.Find(id);
        }

        public IEnumerable<Tournament> ReadAllTournamentsWithParticipants()
        {
            return _context.Tournaments
                .Include(t => t.Enrollments)
                    .ThenInclude(te => te.Player);
        }

        public TournamentEnrollment ReadTournamentEnrollment(int tournamentId, int playerId)
        {
            return _context.TournamentEnrollments.Find(tournamentId, playerId);
        }

        public IEnumerable<TournamentEnrollment> ReadAllTournamentEnrollments()
        {
            return _context.TournamentEnrollments
                .Include(te => te.Player)
                .Include(te => te.Tournament);
        }

        public void DeleteTournamentEnrollment(int tournamentId, int playerId)
        {
            TournamentEnrollment tournamentEnrollment = ReadTournamentEnrollment(tournamentId, playerId);
            _context.TournamentEnrollments.Remove(tournamentEnrollment);
            _context.SaveChanges();
        }
    }

And also my DbContext

 public class TmDbContext : DbContext
    {
        public DbSet<Player> Players { get; set; }
        public DbSet<Tournament> Tournaments { get; set; }
        public DbSet<TournamentEnrollment> TournamentEnrollments { get; set; }

        public TmDbContext()
        {
            TmDbInitializer.Initialize(this, true);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlite("data source = tm.db")
                .UseLoggerFactory(LoggerFactory.Create(lb => lb.AddDebug()));
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TournamentEnrollment>()
                .HasOne(te => te.Tournament)
                .WithMany(t => t.Enrollments)
                .HasForeignKey("TournamentId")
                .IsRequired();
            modelBuilder.Entity<TournamentEnrollment>()
                .HasOne(te => te.Player)
                .WithMany(p => p.TournamentEnrollments)
                .HasForeignKey("PlayerId")
                .IsRequired();
            modelBuilder.Entity<TournamentEnrollment>()
                .HasKey("TournamentId","PlayerId");
        }
    }

last you can find my Program.cs file for the console application

    class Program
    {
        private static IRepository _repo = new Repository();
        private static bool _exit;

        static void Main(string[] args)
        {
            while (!_exit)
            {
                try
                {
                    ShowMenu();
                    MakeChoice();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }
        }

        private static void ShowMenu()
        {
            Console.WriteLine("1) Show all tournaments");
            Console.WriteLine("2) Remove tournament enrollment");
            Console.WriteLine("3) Show all tournament enrollments");
            Console.WriteLine("0) Exit");
        }

        private static void MakeChoice()
        {
            Console.Write("=>: ");

            if (!Int32.TryParse(Console.ReadLine(), out int choice))
            {
                Console.WriteLine("\nPlease enter a numeric value!\n");
            }
            else
            {
                switch (choice)
                {
                    case 1:
                        ShowAllTournaments();
                        break;
                    case 2:
                        RemoveTournamentEnrollment();
                        break;
                    case 3:
                        ShowAllTournamentEnrollments();
                        break;
                    case 0:
                        _exit = true;
                        break;
                }
            }
        }

        private static void ShowAllTournaments()
        {
            Console.WriteLine();

            foreach (var t in _repo.ReadAllTournamentsWithParticipants())
            {
                Console.WriteLine($"[{t.Id}] {t.Name}");

                foreach (var te in t.Enrollments)
                {
                    Console.WriteLine($"\t- [{te.Player.Id}] {te.Player.FullName}");
                }
            }

            Console.WriteLine();
        }

        private static void ShowAllTournamentEnrollments()
        {
            Console.WriteLine();

            foreach (var te in _repo.ReadAllTournamentEnrollments())
            {
                Console.WriteLine($"Tournament = {te.Tournament.Name} | Player = {te.Player.FullName}");
            }

            Console.WriteLine();
        }

        private static void RemoveTournamentEnrollment()
        {
            Console.Write("\nTournamentId: ");
            int tournamentId = Int32.Parse(Console.ReadLine());

            Console.Write("PlayerId: ");
            int playerId = Int32.Parse(Console.ReadLine());

            Console.WriteLine();

            _repo.DeleteTournamentEnrollment(tournamentId, playerId);
        }
    }

When I delete a TournamentEnrollment record (using option 2 in the console application) and afterwards I request all tournaments with their participants (using option 1 in the console application), the enrollment is no longer visible, just as expected.
Now here comes the problem I run into. When during the console application is running, I delete a tournamentEnrollment record manually with SQLite studio (a desktop tool for browsing and editing a sqlite file), and after the commit I request all tournaments with their participants (using option 1 in the console application), the enrollment is still visible. Now I know it has nothing to do with the desktop tool because when I ask to show all TournamentEnrollments (using option 3 in the console application) there is one less so I'm really confused why one query shows the correct result and one an old result. Has It something to do with caching or...?

I hope I have been able to outline the problem, cause my english is not that good .

Thank you very much!

 

[Skydiver]

As a quick aside, if you are already using EF, there is really no need for the Repository pattern anymore.

The Repository Pattern is Dead If You Use Entity Framework - Edgeside Solutions LLC