C# EF - Left join on a Included entity with a filter

Question

I have the following Table Structure

Orders
 OrderID PK

PartyAddresses
 PartyAddressID PK

Then I have a join table that joins PartyAddress to a order

OrderAddresses
 OrderAddressID PK
 OrderID FK to Orders
 PartyAddressID FK to PartyAddresses
 AddressType

The sql statment I am trying to convert is

SELECT 
 * 
FROM 
Orders o  LEFT JOIN OrdersAddresses oa
       ON 
    o.OrderID = oa.OrderID
       LEFT JOIN PartyAddresses pa
          ON
       pa.PartyAddressID = oa.PartyAddressID
WHERE
    oa.AddressType = 'SHIP'

I am having trouble figuring out to replicate this into Linq lambda statements

I have been been able to retrieve the data I want by starting at the OrdersAddress table

OrdersAddresses.Include("Orders").Include("PartyAddresses").Where(oa=>oa.AddressType=="Ship");

But I need the results in terms of a IEnumerable<Orders>

Update

I guess to rephrase my question - given the following repository function

public IQueryable<Orders> OrdersWithAddresses()
{
    return _context.Orders.Include("OrdersAddresses").Include("OrdersAddresses.PartyAddresses");
}

How would i add to the expression tree to only actually return order addresses that are "SHIP"

What is the point of the first left join when you filter on it anyway? — DavidG, Jul 28 '14 at 17:52
possible duplicate of [EF: Include with where clause](http://stackoverflow.com/questions/16798796/ef-include-with-where-clause) — Gert Arnold, Jul 30 '14 at 15:19
I think the way to go would be the answer by Gert Arnold at http://stackoverflow.com/questions/16798796/ef-include-with-where-clause — WantToBeAnonomous, Jul 30 '14 at 17:15

DavidG · Answer 1 · 2014-07-30T20:35:32.220

0

This is off the top of my head but I believe it should work. Note that I've removed the first LEFT JOIN as it is just as easy to do a normal join due to the WHERE clause filtering out the NULLs anyway:

var results = from o in db.Orders
              join oa in db.OrdersAddresses on o.OrderID equals oa.OrderID
              join pa in db.PartyAddresses on oa.PartyAddressID equals pa.PartyAddressID into pas
              from p in pas.DefaultIfEmpty()
              where oa.AddressType == "SHIP"
              select new { o, oa, p };

Update:

Alternatively you can modify your repository function to include a Where clause:

public IQueryable<Orders> OrdersWithAddresses(Func<Orders, bool> predicate)
{
    var orders = _context.Orders.Include("OrdersAddresses").Include("OrdersAddresses.PartyAddresses");

    if(predicate != null)
        return orders.Where(predicate);

    return orders;
}

And call the function like this:

var orders = OrdersWithAddresses(o => o.AddressType = "SHIP");

edited Jul 30 '14 at 20:35

answered Jul 28 '14 at 18:15

DavidG

113,891
12
217
223

Thanks , but I need the results as a populated Graph of Orders objects – WantToBeAnonomous Jul 28 '14 at 18:54
Can you explain what that means? – DavidG Jul 28 '14 at 19:26
Your solution would give me a list of anonymous objects that combine all 3 as separate properties. Instead why I need is a populated Orders object that I can drill down using navigation properties. Ex. results.First().OrderAddresses.First().PartyAddresses.City – WantToBeAnonomous Jul 28 '14 at 19:32
all you need to do is update the last line then, something like `select new Order { ... };` – DavidG Jul 28 '14 at 20:17
I don't think that would work because I don't need a NEW order - i need the order that was returned by EF ,including the change tracking etc... – WantToBeAnonomous Jul 30 '14 at 14:55
Yeah that would work :-) , but what i am trying to figure out is, What would go into that Where predicate – WantToBeAnonomous Jul 30 '14 at 17:08

C# EF - Left join on a Included entity with a filter

1 Answers1