LINQ to Entities, how to return all records from the Parent table

left-joinlinq-to-entities

I am unable to select all the records from the parent table using Linq to Entities.

This is a simple DB design (image below):

Image Link (Dead Link)

This is the exact output I want using Linq to Entities or Linq to SQL (image below):

Image Link (Dead Link)

When I use Linq to Entities or Linq To Sql, I can only get the records from the child table that has a foreign key relation. I am not able to get the null values as shown above.

I want to have the null values show just like when you use left outer join.

Thanks for any help.

Best Answer

from entity in MyContext.EntityType.Include("ChildEntitiesNavigationPropertyName")
select entity;

This returns all instances of EntityType, plus ChildEntitiesNavigationPropertyName when/if it exists. For tabular form use an anonymous type:

from entity in MyContext.EntityType.Include("ChildEntitiesNavigationPropertyName")
select new {ParentProperty = entity.ParentProperty, 
            ChildProperty  = entity.ChildEntitiesNavigationPropertyName.ChildProperty};

For a 1..* property:

from entity in MyContext.EntityType.Include("ChildEntitiesNavigationPropertyName")
from child in entity.ChildEntitiesNavigationPropertyName.DefaultIfEmpty()
select new {ParentProperty = entity.ParentProperty, 
            ChildProperty  = child.ChildProperty};
Related Topic