Navigational Property not materialized in Linq Query Syntax also Lazy Loading didnt work

Question

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.

Answer 1

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;