question

BenP-1732 avatar image
1 Vote"
BenP-1732 asked DanielZhang-MSFT commented

EF Core 3.1.15 Keyless Entity Unexpected Results When Using Navigation Properties

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!






dotnet-entity-framework-core
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BenP-1732,
I suggest you refer to this document to configure the keyless entity type in OnModelCreating using the HasNoKey API.
And try to check if it contains data by querying the database view in the standard way.
Best Regards,
Daniel Zhang


0 Votes 0 ·

Thank you!

I've read that documentation several times, and it says keyless entities support only a subset of navigation capabilities, but I don't think I'm falling into any of the specific limitations. If I don't include the dependent entity I get all the expected records in the results.

 //Gives expected results.
 context.FooBars
     .AsNoTracking()
     .Where(x => types.Contains(x.FooCategory));
    
 //Gives expected results.
 context.FooBars
     .AsNoTracking()
     .Include(x => x.Foo)
     .Where(x => types.Contains(x.FooCategory));
    
 //Gives unexpected results.
 //It is only when including the depedendent entity in the one to many relationship that
 //the results are unexpected.
 context.FooBars
     .AsNoTracking()
     .Include(x => x.Foo).ThenInclude(x => x.Bars)
     .Where(x => types.Contains(x.FooCategory));

The CountAsync return even indicates the correct number of results.

0 Votes 0 ·

It is also important to note that the Bars table is included in the view I'm mapping to the FooBars keyless entity:

 ALTER 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

Without this join in the view, the results make sense. This does seem like an unusual scenario, but I'm working on a project where this scenario was used, and it is causing some results to be missing from a query used on a search screen.

0 Votes 0 ·

Hi @BenP-1732,
Please change your following code

   modelBuilder.Entity<FooBar>().HasOne(x => x.Foo).WithMany().HasForeignKey(x => x.FooId);

to

  modelBuilder.Entity<FooBar>().HasOne(x => x.Foo).WithMany(x => x.Bars).HasForeignKey(x => x.FooId);

Best Regards,
Daniel Zhang



0 Votes 0 ·
Show more comments

0 Answers