Composable LINQ to SQL
In LINQ to SQL, queries can be composed. We can use this ability to create dynamic queries. For example:
Northwind db = new Northwind(connString);
var query =
from c in db.Customers
where c.City == “London”
select c;
Console.WriteLine(db.GetCommand(query).CommandText);
if (true) // Some condition
{
query =
from c in query
where c.ContactTitle == “Sales Representative”
select c;
}
Console.WriteLine(db.GetCommand(query).CommandText);
/*
SELECT
[t0].[CustomerID], [t0].[CompanyName],
[t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0].[Country],
[t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[ContactTitle] = @p0) AND ([t0].[City] = @p1)
*/
if (true)
{
query =
(
from c in query
from o in c.Orders
where o.ShipCity == “Colchester”
select c
).Distinct();
}
Console.WriteLine(db.GetCommand(query).CommandText);
/*
SELECT DISTINCT
[t0].[CustomerID], [t0].[CompanyName],
[t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0].[Country],
[t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1]
WHERE ([t1].[ShipCity] = @p0)
AND ([t0].[ContactTitle] = @p1)
AND ([t0].[City] = @p2)
AND ([t1].[CustomerID] = [t0].[CustomerID])
*/
foreach (var cust in query)
{
Console.WriteLine(cust);
}
Cool eh!
This can be done because query is an expression tree and the SQL is only generated from the expression tree and the resulting SQL executed when we start to enumerate the query.
Tags: Dynamic, Linq, LINQ to SQL, SQL