LinQ method syntax multiple tables with .where() and without joins

Question

How do fetch data from multiple tables with method syntax without using joins, but only .where() methods?

I'm making a select against EF5 db context which maps to this legacy table structure where I have a persons detail table and another table which refers both to itself to create a hierarchy and to the person details table this way:

PersonSet 
.Where(p => p.LastName.ToLower()=="surname")
.Join(SubsetSet, p => p.Id, ss => ss.SubsetLink, (p, ss) => new { PersonDetail = p, person = ss })
.Where(m => m.person.Frame=="a" && m.person.Purpose=="1")
.Join(SubsetSet, ss1 => ss1.person.Owner, person => person.SubsetLink, (ss1, ss2) => new { person = ss1, club = ss2 })
.Where(a => a.club.Frame=="b" && a.club.Purpose=="2")
.Join(SubsetSet, ss => ss.club.Owner, ss2 => ss2.SubsetLink, (ss, ss2) => new { club = ss, association = ss2 })
.Where(a => a.association.Frame=="b" && a.association.Purpose=="3")
.Join(SubsetSet, ss => ss.association.Owner, ss3 => ss3.SubsetLink, (ss, ss3) => new { association = ss, district = ss3})
.Where(d => d.district.Frame=="b" && d.district.Purpose=="4" && d.district.SubsetLink=="12345")
.Select(proj => new { proj.association.club.person, proj.association.club, proj.association, proj.district })
.OrderByDescending(a => a.association.club.person.phyperson.FirstName) 
.Take(10).Dump();

The above query works at least in LinqPad but, it seems to me that If I could get rid of those joins the statement might look a bit nicer. Now I know, like in the Albahari example below, that this can be done with query syntax. But I couldn't find an example that would illustrate this situation with method syntax. The way I'm trying to approach this might of course be wrong and that's why I can't find suitable examples.

Here I found something similar, but couldn't make it work in LinQPad: Is multiple .Where() statements in LINQ a performance issue?

Or this one, where the solution is again in query syntax: Cross Join with Where clause

Or this example by Albahari: (http://www.linqpad.net/WhyLINQBeatsSQL.aspx)

from p in db.Purchases 
where p.Customer.Address.State == "WA" || p.Customer == null 
where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000 
select p

Normally each entity has a connecting property to another entity, i.e. in the Albahari example above a `Purchase` has a `Customer` which links to the particular customer who bought the purchase. Does your entity have any of those? — NeddySpaghetti, Jun 11 '14 at 12:22
It is navigation properties Customer and PurchaseItems in the ALbahri example. If your entity have navigation properties you should not use join to link them with your entity. — Kirill Bestemyanov, Jun 11 '14 at 12:24
@NedStoyanov Ah, I only have these two entities and no associations defined in the edmx between them. The original db does not have relations. The linking key fields between these two tables/entities are of different type. So I couldn't make the association. Also the self referencing SubsetSet has five keys which kind of refer to each other (Owner to SubsetLink) as you can see from my example code. So I guess it can't be done without joins? — Lupa, Jun 11 '14 at 13:00

Kris Vandermotten · Accepted Answer · 2014-06-12T13:25:06.983

Consider this query:

var q = from order in orders
        from orderline in order.Lines
        where orderline.Count > 10
        select order.Discount * orderline.Price;

this more or less corresponds to

var q = orders
    .SelectMany(order => order.Lines, (order, orderline) => new { order, orderline})
    .Where(item => item.orderline.Count > 10)
    .Select(item => item.order.Discount * item.orderline.Price);

For more information on SelectMany, see the MSDN documentation.

If you don't have associations defined:

var q = from order in orders
        from orderline in orderLines
        where orderline.OrderId == order.Id
        where orderline.Count > 10
        select order.Discount * orderline.Price;

this more or less corresponds to

var q = orders
    .SelectMany(order => orderLines, (order, orderline) => new { order, orderline})
    .Where(item => item.orderline.OrderId == item.order.Id)
    .Where(item => item.orderline.Count > 10)
    .Select(item => item.order.Discount * item.orderline.Price);

I guess this could be the answer if I had associations defined between the entities. But as I do not, I'll have to use the original approach I already had. Thanks anyway! — Lupa, Jun 12 '14 at 09:04
@Lupa Updated the answer to make it more clear that this technique can be used also when associations are not defined. — Kris Vandermotten, Jun 12 '14 at 13:26

LinQ method syntax multiple tables with .where() and without joins

1 Answers1