LLBL Linq variable number of joins

Question

I'm using the Linq syntax for LLBL. I have a need to join to a particular table a variable number of times. I have a query that does work, but I'm trying to make it faster. I have the raw SQL, but I'm not sure how to code it in Linq or if I even can. Here is a simplified example:

SELECT Parent.SomeColumn
FROM
    ParentTable Parent
    INNER JOIN ChildTable Child1 ON Child1.ParentID = Parent.ID
    INNER JOIN ChildTable Child2 ON Child2.ParentID = Parent.ID
WHERE
    Child1.TypeColumn = 'Type1'
    AND Child2.TypeColumn = 'Type2'

That's easy enough to do, but I want something that would handle Child3, Child4, etc. In other words, I won't know the number of ChildTable joins at compile time.

My original code queried ParentTable and then did EXISTS subqueries on ChildTable for each time I needed the join. It works, but what I'm seeing is the query above is going to take about 1/4 of the time to execute. I have some real-time UI updates that would see a significant improvement if I can make this work.

I know this would be super easy if I was building the SQL query in code, but I'd really like to rely on LLBL to generate that for me.

Answer 1

I don't know about LLBL specifically, but in EF or LINQ to SQL I would be very surprised if the following didn't produce something very similar to the raw SQL query you posted:

int numJoins = ...
IQueryable<ParentTable> parents = ...

for (var i = 0; i < numJoins; i++) {
    var typeI = "Type" + (i + 1);
    var typeIChildren = children.Where(ch => ch.TypeColumn == typeI);
    parents = parents.Join(typeIchildren, parent => parent.Id, child => child.ParentId, (parent, child) => parent);
}

return parents.Select(p => p.SomeColumn);