I am having trouble querying a view that joins two tables that have a one to many relationship. I have a keyless entity mapped to the view that contains navigation property for the principal table. When I include the principal entity and then include the dependent entity the results of "ToListAsync" have missing items, but If I do a "CountAsync" on the query I get the expected count.
My entities are:
public class Foo
{
public Guid Id { get; set; }
public string Category { get; set; }
public string Note { get; set; }
public ICollection<Bar> Bars { get; set; }
}
public class Bar
{
public Guid Id { get; set; }
public Guid FooId { get; set; }
public string Note { get; set; }
public Foo Foo { get; set; }
}
public class FooBar
{
public Guid FooId { get; set; }
public string FooCategory { get; set; }
public string FooNote { get; set; }
public Guid? BarId { get; set; }
public string BarNote { get; set; }
public Foo Foo { get; set; }
}
My DBContext (with test data seeding) is:
public class TestContext : DbContext
{
public DbSet<Foo> Foos { get; set; }
public DbSet<Bar> Bars { get; set; }
public DbSet<FooBar> FooBars { get; set; }
public TestContext(DbContextOptions options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Bar>().HasOne(x => x.Foo).WithMany(x => x.Bars).HasForeignKey(x => x.FooId);
var foo1 = new Foo() { Id = new Guid("32574fcd-8dd0-459f-9929-9468ea9c604e"), Category = "FooA", Note = "Foo 1" };
var foo2 = new Foo() { Id = new Guid("71d05f57-8289-44e1-a549-7a3d2e913cf5"), Category = "FooB", Note = "Foo 2" };
var foo3 = new Foo() { Id = new Guid("c5dc7180-4550-4a2e-8b3d-e5b4ef0a8cfb"), Category = "FooA", Note = "Foo 3" };
modelBuilder.Entity<Foo>().HasData(foo1, foo2, foo3);
var bar1 = new Bar() { Id = new Guid("de194889-d38a-4a1b-aa0f-73dacc2eee62"), Note = "Foo 1 > Bar 1", FooId = foo1.Id };
var bar2 = new Bar() { Id = new Guid("8f041289-9cd2-49c9-a1e4-e537df6927eb"), Note = "Foo 1 > Bar 2", FooId = foo1.Id };
var bar3 = new Bar() { Id = new Guid("326e4044-0f65-4d40-a136-fa8c97d23a49"), Note = "Foo 2 > Bar 1", FooId = foo2.Id };
var bar4 = new Bar() { Id = new Guid("c9c71310-4569-455d-bd9b-033f6b57a6ba"), Note = "Foo 2 > Bar 2", FooId = foo2.Id };
modelBuilder.Entity<Bar>().HasData(bar1, bar2, bar3, bar4);
modelBuilder.Entity<FooBar>().HasNoKey();
modelBuilder.Entity<FooBar>().ToView("view_FooBars");
modelBuilder.Entity<FooBar>().HasOne(x => x.Foo).WithMany().HasForeignKey(x => x.FooId);
}
}
The create view SQL is:
CREATE VIEW [dbo].[view_FooBars] AS
SELECT
f.Id AS FooId,
f.Category AS FooCategory,
f.Note AS FooNote,
b.Id AS BarId,
b.Note AS BarNote
FROM
Foos AS f
LEFT JOIN Bars AS b ON f.Id = b.FooId
And the query in this code is what is giving me unexpected results:
static async Task SelectFromFooBars(TestContext context)
{
var types = new string[] {
"FooA",
"FooB"
};
var fooBarQuery = context.FooBars
.AsNoTracking()
.Include(x => x.Foo).ThenInclude(x => x.Bars)
.Where(x => types.Contains(x.FooCategory));
var fooBarCount = await fooBarQuery.CountAsync();
var fooBarList = await fooBarQuery.ToListAsync();
Console.WriteLine($"Found: {fooBarCount} FooBars");
foreach (var fooBar in fooBarList)
{
Console.WriteLine($"FooBar.FooId: {fooBar.FooId}, FooBar.FooCategory: {fooBar.FooCategory}, FooBar.FooNote: {fooBar.FooNote}, FooBar.BarId: {fooBar.BarId}, FooBar.BarNote: {fooBar.BarNote}");
Console.WriteLine($" Foo.Id: {fooBar.Foo.Id}, Foo.Category: {fooBar.Foo.Category}, Foo.Note: {fooBar.Foo.Note}");
foreach (var bar in fooBar.Foo.Bars)
{
Console.WriteLine($" Bar.Id: {bar.Id}, Bar.FooId: {bar.FooId}, Bar.Note: {bar.Note}");
}
}
}
I am have read the EF Core documentation, and haven't found anything that indicates this code is incorrect, and haven't found a good explanation for what is happening. Any insight on this issue would be greatly appreciated. Thanks!