Hi,
In this post i will talk about SubSelects with LinqToSql.
We often need to perform SubSelects as we do in standard T-SQL. But howto do it with Linq2Sql ?
I'll give you some exemples to do that using Anonymous Types.
SubSelect in a Select Clause
using (DbCtxDataContext db = new DbCtxDataContext())
{
var q = from p in db.Customers
select new
{
p.CustomerId,
p.Email,
OrderCount = (from o in db.Orders
where o.CustomerId == p.CustomerId
select o).Count()
};
}Generated SQL Code
SELECT [t0].[CustomerId], [t0].[Email],
(
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t1]
WHERE [t1].[CustomerId] = [t0].[CustomerId]
) AS [OrderCount]
FROM [dbo].[Customers] AS [t0]
Another SubSelect with "let" Keyword usage
NB : The "let" keyword allow us to "Alias" a query like the "As" in T-SQL
using (DbCtxDataContext db = new DbCtxDataContext())
{
var q = from p in db.Customers
let OrderCount = (from o in db.Orders
where o.CustomerId == p.CustomerId
select o).Count()
select new
{
p.CustomerId,
p.Email,
OrderCount
};
}Generated SQL Code
SELECT [t0].[CustomerId], [t0].[Email],
(
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t1]
WHERE [t1].[CustomerId] = [t0].[CustomerId]
) AS [OrderCount]
FROM [dbo].[Customers] AS [t0]
Hope this help's!
Views(1700)
