As promised, I am going to explain how we could translate the following SQL query into LINQ
SELECT *
FROM TableX LEFT JOIN TableY ON TableX.YID = TableY.YID
LEFT JOIN TableZ ON TableY.ZID = TableZ.ZID
Before we do this, I feel that using SQL syntax in LINQ to explain a SQL query is relative hard. So, I will stick to .net syntax in my blog.
Now, let begin exam the SQL query we have in hand.
I will break the query into two parts, the first part of the query is just a simple LEFT JOIN, which we already covered on how to convert it into LINQ.
Linq in .net syntax:
TXs.GroupJoin(TYs, x => x.YId, y => y.YId, (x, g) => new { x, g })
.SelectMany(y => y.g.DefaultIfEmpty(), (item, y) => new { item.x, y });
Now, how can we append the second part of the query into it?
One will simply append a similar “Left Join” query into the original query, and it will look like this
// First part of the left join
TXs.GroupJoin(TYs, x => x.YId, y => y.YId, (x, yg) => new { x, yg })
.SelectMany(y => y.yg.DefaultIfEmpty(), (xItem, y) => new { xItem.x, y })
// Second part of the left join
.GroupJoin(TZs, xyItem => xyItem.y.ZId, z => z.ZId, (xyItem, zg) => new { xyItem, zg })
.SelectMany(z => z.zg.DefaultIfEmpty(), (xyItem, z) => new { X = xyItem.xyItem.x, Y = xyItem.xyItem.y, Z = z }).ToList();
If they do, they end up having null object reference exception in the statement xyItem.y.ZId because after the first left join, xyItem.y can be null.
So, how can we solve this?
We can resolve it two different way by understand two important concepts.
First point is very easy to understand, SQL engine handle null different with .NET. So, when something seems make sense in SQL world don’t necessary mark sense in .net world.
To solve it using the first point is very trivial, we just break the SQL query into two. So, we can handle the null condition differently in .NET
The Linq query will look like this
.Where(item => item.yg.Count() > 0)
.GroupJoin(TZs, xyItem => xyItem.y.ZId , z => z.ZId, (xyItem, zg) => new { xyItem, zg })
.SelectMany(z => z.zg.DefaultIfEmpty(), (xyItem, z) => new { X = xyItem.xyItem.x, Y = xyItem.xyItem.y, Z = z })
.Union(
.Where(item => item.g.Count() == 0)
.Select(p => new { X = p.x, Y = (TableY)null, Z = (TableZ)null })
)
It doesn’t look very professional, isn’t it? Let exam, the second fact…
The second point is that, SQL engine probably will not execute the SQL query according to the order of when the T-SQL query, what I mean is that SQL engine probably will handle the “From” clause before it handle the “SELECT” clause.
Now, we can exam the SQL query we would like to translate to Linq closer again. We can see that the TableZ is depends on value in TableY, and TableY is depends on value in TableX. So, we shouldn’t wrote our Linq query according to the order of the text appear, we should wrote the Linq query according to their object dependency.
Let translate the original SQL query to
FROM TableX LEFT JOIN
(SELECT *
FROM TableY LEFT JOIN TableZ
on TableY.Zid = TableZ.ZID) AS TABLEYZ ON TableX.YId = TableYZ.YID
The corresponding Linq query will look like this
TXs.GroupJoin(
TYs.GroupJoin(TZs, y => y.ZId, z => z.ZId, (y, g) => new { y, g })
.SelectMany(z => z.g.DefaultIfEmpty(), (item, z) => new { item.y, z })
, x => x.YId, y => y.y.YId, (x, g) => new { x, g })
.SelectMany(yz => yz.g.DefaultIfEmpty(), (xItem, yz) => new { X = xItem.x, Y = yz == null? (TableY)null : yz.y, Z = yz == null? (TableZ) null : yz.z }).ToList();
Yeah, we finally got our Linq query!
To keep the blog in a reasonable size, let continue to discuss what is the common usage and mistake in the next blog. And I will put all the examples of this blog at the end of the blog, so, you could play around it and explore the power of LINQ. ^_^