Data.Linq.Functions

Home / Data / Linq

Standard .NET Framework functions

BLToolkit Linq provider supports about four hundred standard .NET Framework functions including string, datetime, math, convert, language specific, etc functions. If some function does not have exact analogue in SQL, it's implemented as a specific for particular database server algorithm. See a few examples below.

Here the Length property has exact 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

The Compare function is transformed to corresponding SQL expression:

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 implements the Math.Round function. By default this function rounds a number to even number if it's halfway between two numbers.

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

So far so good. But now let's make a little change.

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

This is not what we really expected. So, it would be better to change this query as the following:

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