SQL, it is replaced by a specific to a particular database server algorithm. Here are some examples.
Here everything is simple. Property Length has a direct implementation in SQL:
from c in db.Customer
where c.ContactName.Length > 5
select c.ContactName; |
SQL:
SELECT
[p].[ContactName]
FROM
[Customers] [p]
WHERE
Len([p].[ContactName]) > 5 |
Here Compare function is converted into the corresponding construction SQL:
from c in db.Customer
where c.ContactName.CompareTo("John") > 0
select c.ContactName; |
SQL:
SELECT
[p].[ContactName]
FROM
[Customers] [p]
WHERE
[p].[ContactName] > 'John' |
The following query to SQL implements a function equivalent to function Math.Round in .NET. The fact that this function is the default number of rounds to the nearest chёtnomu, so we can observe this unusual implementation.
from o in db.Order
where Math.Round(o.Freight) >= 10
select o.Freight; |
SQL:
SELECT
[o].[Freight]
FROM
[Orders] [o]
WHERE
CASE
WHEN [o].[Freight] - Floor([o].[Freight]) = 0.5 AND Floor([o].[Freight]) % 2 = 0
THEN Floor([o].[Freight])
ELSE Round([o].[Freight], 0)
END >= 10 |
It looks like this is all quite harmless, but here lies one unpleasant detail. Let's complicate our inquiry:
from o in db.Order
where Math.Round(o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)) >= 10
select o.Freight; |
SQL:
SELECT
[o].[Freight]
FROM
[Orders] [o]
INNER JOIN [Order Details] [t1] ON [o].[OrderID] = [t1].[OrderID]
WHERE
CASE
WHEN (
SELECT
Sum(Convert(Decimal(5,0), [t2].[Quantity]) * [t2].[UnitPrice])
FROM
[Order Details] [t2]
WHERE
[o].[OrderID] = [t2].[OrderID]
) - Floor((
SELECT
Sum(Convert(Decimal(5,0), [t3].[Quantity]) * [t3].[UnitPrice])
FROM
[Order Details] [t3]
WHERE
[o].[OrderID] = [t3].[OrderID]
)) = 0.5 AND Floor((
SELECT
Sum(Convert(Decimal(5,0), [t4].[Quantity]) * [t4].[UnitPrice])
FROM
[Order Details] [t4]
WHERE
[o].[OrderID] = [t4].[OrderID]
)) % 2 = 0
THEN Floor((
SELECT
Sum(Convert(Decimal(5,0), [t5].[Quantity]) * [t5].[UnitPrice])
FROM
[Order Details] [t5]
WHERE
[o].[OrderID] = [t5].[OrderID]
))
ELSE Round((
SELECT
Sum(Convert(Decimal(5,0), [t6].[Quantity]) * [t6].[UnitPrice])
FROM
[Order Details] [t6]
WHERE
[o].[OrderID] = [t6].[OrderID]
), 0)
END >= 10 |
The result is not as expected. Therefore, such a request is best rewritten as follows:
from o in db.Order
let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)
where Math.Round(sum) >= 10
select o.Freight; |
SQL:
SELECT
[o1].[Freight] as [Freight1]
FROM
(
SELECT
(
SELECT
Sum(Convert(Decimal(5,0), [t1].[Quantity]) * [t1].[UnitPrice])
FROM
[Order Details] [t1]
WHERE
[o].[OrderID] = [t1].[OrderID]
) as [sum1],
[o].[Freight]
FROM
[Orders] [o]
INNER JOIN [Order Details] [t2] ON [o].[OrderID] = [t2].[OrderID]
) [O1]
WHERE
CASE
WHEN [o1].[sum1] - Floor([o1].[sum1]) = 0.5 AND Floor([o1].[sum1]) % 2 = 0
THEN Floor([o1].[sum1])
ELSE Round([o1].[sum1], 0)
END >= 10 |
So much better. In fact, the previous query is equivalent to the following:
from o in
from o in db.Order
select new
{
sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice),
O
}
where Math.Round(o.sum) >= 10
select o.o.Freight; |
Design let wrapped into one