CODEWITHSUNDEEP
c#sqlsql-serverjoinlinq-to-sql
Kris
Kris

Reputation: 13

Incorrect results from multiple joins in Linq2SQL

I've been tasked with converting some crusty old SQL to Linq2SQL and I know this shouldnt be the first choice however it needs to be done.

Problem

I'm stuck attempting to force specific joins to replicate the desired output and the following is a simplified version of the SQL with one parameter variant shown followed by a simplified version of my attempt in Linq2SQL. Several alternative fields may be queried in Table1 hence the use q and q2 in the converted code though the actual parameters are not relevant to the issue.

Aim

The query needs to retrieve relevant rows from Table1, the latest related row from Table2 and additional data from Table2's parent table Table3, preferably in a single pass.

There may be 0-n matches in Table1, Table1 has a 1:n relationship with Table2 with 0-n rows, there is a 1:n relationship between Table3 and Table2.

Regardless of how I structure the expressions, the linq generates an INNER JOIN onto Table2 excluding rows in Table1, how can I structure the linq query to achieve the desired result?

SELECT      [...]
FROM        Table1 t1
LEFT JOIN   (
                SELECT      MAX(id) AS id, parent_id 
                FROM        Table2  
                GROUP BY    parent_id 
            ) x2 ON t1.id = x2.parent_id 
LEFT JOIN   Table2  t2  ON x2.id = t2.id 
LEFT JOIN   Table3  t3  ON t2.table3_id = t3.id 
WHERE       t1.id = row_id

var q = dc.Table1.AsQueryable();

    q = from r in q where r.id == row_id select r;

var q2 = from r in (q)
    join x2 in (from r in dc.Table2.DefaultIfEmpty() group r by r.parent_id into maxt2 let max_id = maxt2.Max(f => f.id) select new { maxt2.Key, max_id }) on r.id equals wx.Key
    join t2 in dc.Table2.DefaultIfEmpty() on x2.max_id equals t2.id
    join t3 in dc.Table3.DefaultIfEmpty() on t2.table3_id equals t3.id
    select
    {
        [...]
    };

Upvotes: 1

Views: 45

Answers (1)

NetMage
NetMage

Reputation: 26917

Here is my translation using my recipe rules:

var Q1 = from t2 in dc.Table2
         group t2 by t2.parent_id into t2g
         select new { parent_id = t2g.Key, id = t2g.Max(t2 => t2.id) };

var Q2 = from t1 in Table1
         where t1.id == row_id
         join q1 in Q1 on t1.id equals q1.parent_id into q1j
         from q1 in q1j.DefaultIfEmpty()
         join t2 in dc.Table2 on q1.id equals t2.id into t2j
         from t2 in t2j.DefaultIfEmpty()
         join t3 in dc.Table3 on t2.table3_id equals t3.id into t3j
         from t3 in t3j.DefaultIfEmpty()
         select new { t1, t2, t3 };

Upvotes: 1

Related Questions