EF4 Cascading Left Outer Joins Null Exception

I have this query

Select p.Name,p.Street from person p
left join address a on a.address_id = p.address_id
left join Order o on o.order_id = a.order_id

But when i try to convert it into LINQ query,

var q = from p in Entities.Person 
        from a in Entities.Address.Where(a=>a.address_id == p.address_id).DefaultIfEmpty()
        from o in Entities.Order.Where (o=>o.order_id== a.order_Id).DefaultI开发者_运维知识库fEmpty()

I am getting a Null exception since for some combination of address_ids there are no addresses and it blows up in o=>o.order_id== a.order_Id clause(since a is null).

Please let me know how to do multiple left joins in EF 4, the correct way !

Thanks !

If you have modeled correctly you do not need to explicitly do left outer joins.

Select p.Name, a.Street from person p
left join address a on a.address_id = p.address_id
left join Order o on o.order_id = a.order_id

The above query can be converted as follows

var projection = Entities.Person.Select(p => new {p.Name, p.Address.Street});

EF will automatically add the joins to retrieve the fields.

You can manually do the joins as follows

 var projection = from p in Entities.Person
        join a in Entities.Address on p.address_id equals a.address_id into outer
        from a in outer.DefaultIfEmpty()
        select new {p.Name, a.Street};