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?
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:
context.GameWords.Where(
gameWord => gameWord.WordProgress.Any(
attempt => attempt.Value == "Fruit"
)
)