Let's see the following simple example:
Code: Select all
from t1 in DB.Parent
from t2 in DB.Child.Where(t2 => t2.Id == t1.Id).DefaultIfEmpty()
group t1 by new {t1.Id, t2.Date} into grouped
select new { grouped.Key.Id, grouped.Key.Date, Last_date = grouped.Max(c => c.Date_inserted) };
Code: Select all
SELECT t1.id, t1.date, (
SELECT MAX(t4.date_inserted) AS C1
FROM db.parent t4
LEFT OUTER JOIN db.child t5 ON (t5.id = t4.id)
WHERE (t1.id = t4.id) AND (t1.date = t5.date)
) AS C1
FROM (
SELECT t2.id, t3.date
FROM db.table1 t2
LEFT OUTER JOIN db.table2 t3 ON (t3.id = t2.id)
GROUP BY t2.id, t3.date
) t1
Code: Select all
SELECT t2.id, t3.date, MAX(t2.date_inserted)
FROM db.table1 t2
LEFT OUTER JOIN db.table2 t3 ON (t3.id = t2.id)
GROUP BY t2.id, t3.date
If the set of records in child table is empty, this condition is always FALSE and therefore LEFT JOIN has the same effect as INNER JOIN.
So, for example, if the following result set is returned by the last query:
Code: Select all
id date MAX( t2 . date_inserted )
7 2009-08-31 19:13:28 2009-08-09 16:18:29
14 NULL 2009-08-02 17:10:05
The first query, translated from linq, would return just the first row.
Thank you for attendance. I hope, it isn't too confused.