Search code examples
c#.netmstest

The entity type 'List<string>' requires a primary key to be defined in my mstest


While making some unit tests with mstest this error showed up when I set up UseInMemoryDatabase. Important is that the error does not show up when I run the app. Only when running a test. It seems to come from here:

public List<string> WordProgress { get; set; } = new List<string>();

The error is gone when I add [NotMapped] above, but this makes the column dissapear.

Context:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<FinalWord>()
        .HasMany(c => c.Games)
        .WithOne(e => e.FinalWord);

    modelBuilder.Entity<Game>()
        .HasMany(c => c.GameWords)
        .WithOne(e => e.Game);

    modelBuilder.Entity<Game>()
        .HasOne(c => c.FinalWord)
        .WithMany(e => e.Games);
    
    modelBuilder.Entity<Word>()
        .HasMany(c => c.GameWords)
        .WithOne(e => e.Word);

    modelBuilder.Entity<GameWord>()
        .HasOne(c => c.Game)
        .WithMany(e => e.GameWords);
    
    modelBuilder.Entity<GameWord>()
        .HasOne(c => c.Word)
        .WithMany(e => e.GameWords);
}

GameWord.cs

    public class GameWord
{
  [Key]
  public int Id { get; set; }
  public List<string> WordProgress { get; set; } = new List<string>();
  
  [Required]
  public Word Word { get; set; }
  [Required]
  public Game Game { get; set; }
  public bool Finished { get; set; } = false;
}

And my test setup.

    public UnitTest1()
{
    DbContextOptionsBuilder<LingoContext> dbOptions = new DbContextOptionsBuilder<LingoContext>()
        .UseInMemoryDatabase(
            Guid.NewGuid().ToString()
        );
        
    _context = new LingoContext(dbOptions.Options);
}

    [TestMethod]
public void GetAllGames()
{
    var repo = new SqlGameRepo(_context);
    Game game1 = new Game();
    Game game2 = new Game();
    _context.Game.Add(game1);
    _context.Game.Add(game2);
    _context.SaveChanges();
    
    IEnumerable<Game> result = repo.GetAllGames();
    
    Assert.AreEqual(result.Count(), 2);
}

Anyone knows the reason why?


Solution

  • Entity Framework is treating the List<String> as a navigation property, e.g. it expects there to be a table of string which it can join to the GameWord table.

    Relational databases do not support a column being a 'list' - you have to create a separate table and use a foreign key and a join, or map the column to a different type e.g. convert the values to a comma-separated string or a json string

    As a separate table:

    public class GameWord
    {
        [Key]
        public int Id { get; set; }
    
        public List<Attempt> WordProgress { get; set; } = new List<Attempt>();
    
        [Required]
        public Word Word { get; set; }
        [Required]
        public Game Game { get; set; }
        public bool Finished { get; set; } = false;
    }
    
    public class Attempt
    {
        [Key]
        public int Id { get; set; }
    
        public int GameWordId { get; set; }
        public GameWord GameWord { get; set; }
    
        [Required]
        public string Value { get; set; }
    }
    

    As a comma-separated string:

    // The Entity that you have defined doesn't need to be changed
    public class GameWord
    {
        [Key]
        public int Id { get; set; }
    
        public List<Attempt> WordProgress { get; set; } = new List<Attempt>();
    
        [Required]
        public Word Word { get; set; }
        [Required]
        public Game Game { get; set; }
        public bool Finished { get; set; } = false;
    }
    
    // Just specify how to convert the C# property to the SQL value
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GameWord>()
            .Property(c => c.WordProgress)
            .HasConversion(
                attempts => string.Join(",", attempts),
                csv => csv.Split(',')
            );
    }
    

    As json:

    // Specify a different conversion
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GameWord>()
            .Property(c => c.WordProgress)
            .HasConversion(
                attempts => JsonSerializer.Serialize(attempts, (JsonSerializerOptions)null),
                json => JsonSerializer.Deserialize<List<string>>(json, (JsonSerializerOptions)null)
            );
    }
    

    When you see how easy it is to add a json or csv column to a table with Entity Framework, you might think the first approach of adding a whole new table is overkill. I would still recommend starting with that approach, and only switch to a custom conversion if performance is affected by the join operation (which I think is unlikely here). The reasons being:

    • Navigation properties with an ORM are common and easy for devs to understand. Custom conversions are not. They are a specific feature of Entity Framework Core, and there are some subtleties in how a conversion affects the change tracking done by EF
    • If you decide in the future that you want to store more than just a single 'word' value, maybe the 'word' and the time at which the attempt was made, you have to write a funky migration script to unpack the values in the column and convert them to a different format. With a dedicated entity type and child table, you can just add a new column.
    • Entity Framework usually can't translate SQL filter operations on a converted column. If you want to search for all games where the player has attempted the word 'Fruit', you would have to write the SQL yourself. If you use a child table and navigation properties, this is simple to write:
    context.GameWords.Where(
        gameWord => gameWord.WordProgress.Any(
            attempt => attempt.Value == "Fruit"
        )
    )