0

Below is a join using linq:

var query = from a in TableA
            join b in TableB
            on new { a.Id1, a.Id2 } equals new { b.Id1, b.Id2 }
            select a;
var entities = query.ToList();
foreach(var item in entities)
{
    foreach(var b in item.B)
    {
        var propValue = b.SomeProperty;
    }
}

Assume that TableB have matching records for items in TableA. But after executing query there was no value present in navigational property B of each A.

Also when I try to access it in inner foreach loop it do not send a request to DB to load those entities(lazy loading).

Am I missing something or this the way entity framework is supposed to work.

Because here in this case I expected that lazy loading will work and load the related entities when accessed.

Is there a way to materialize those navigational property while using the LINQ Query Syntax without loosing the ability to write clean sql query without sub queries.

Ashutosh Singh
  • 609
  • 6
  • 21

1 Answers1

0

You shouldn't join in LINQ to Entities. Instead use your Navigation Properties.

var query = from a in TableA.Include(a => a.B)
            select a;
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • This does provide with eager loading. But the query which is generated out of this is not as simple as the one which is generated using join in query syntax. Which sometimes results into performance overhead. – Ashutosh Singh Jul 23 '18 at 14:44
  • It's a different query because it actually does Eager Loading. Otherwise they should be similar, returning one row for each matching (A,B) pair. – David Browne - Microsoft Jul 23 '18 at 14:55
  • I think we can say that we loose the ability to load related entities eagerly while using LINQ(Query syntax). The reason I am moving from Include syntax to Query syntax is to improve the performance of the query which I think can be achieved using a better SQL query generated out of LINQ query. Also What about the 2nd question? Why lazy loading is not working in my case? – Ashutosh Singh Jul 23 '18 at 15:06
  • Why do you think you are improving the performance of your queries in any way _other_ than eliminating Eager Loading (which will affect your query generation)? – David Browne - Microsoft Jul 23 '18 at 15:08
  • Sorry for the confusion. My target is to improve performance and hence doing eager loading along with keeping the queries simple(when we use query syntax with joins its quite efficient queries). Why lazy loading is not working is a by product of this exercise which I expect to work as per the EF implementation. I am not planning to use lazy loading since it will definitely hamper the performance. – Ashutosh Singh Jul 23 '18 at 15:15
  • "doing eager loading along with keeping the queries simple" You can't really have both. Consider turning off Lazy Loading, and loading the entity sets in separate queries and letting the Change Tracker fix-up the Navigation Properties. – David Browne - Microsoft Jul 23 '18 at 15:19