Standard Query Operator Translation

Article
09/15/2021

LINQ to SQL translates Standard Query Operators to SQL commands. The query processor of the database determines the execution semantics of SQL translation.

Standard Query Operators are defined against sequences. A sequence is ordered and relies on reference identity for each element of the sequence. For more information, see Standard Query Operators Overview (C#) or Standard Query Operators Overview (Visual Basic).

SQL deals primarily with unordered sets of values. Ordering is typically an explicitly stated, post-processing operation that is applied to the final result of a query rather than to intermediate results. Identity is defined by values. For this reason, SQL queries are understood to deal with multisets (bags) instead of sets.

The following paragraphs describe the differences between the Standard Query Operators and their SQL translation for the SQL Server provider for LINQ to SQL.

Operator Support

Concat

The Concat method is defined for ordered multisets where the order of the receiver and the order of the argument are the same. Concat works as UNION ALL over the multisets followed by the common order.

The final step is ordering in SQL before results are produced. Concat does not preserve the order of its arguments. To ensure appropriate ordering, you must explicitly order the results of Concat.

Intersect, Except, Union

The Intersect and Except methods are well defined only on sets. The semantics for multisets is undefined.

The Union method is defined for multisets as the unordered concatenation of the multisets (effectively the result of the UNION ALL clause in SQL).

Take, Skip

Take and Skip methods are well defined only against ordered sets. The semantics for unordered sets or multisets are undefined.

Note

Take and Skip have certain limitations when they are used in queries against SQL Server 2000. For more information, see the "Skip and Take Exceptions in SQL Server 2000" entry in Troubleshooting.

Because of limitations on ordering in SQL, LINQ to SQL tries to move the ordering of the argument of these methods to the result of the method. For example, consider the following LINQ to SQL query:

var custQuery =
    (from cust in db.Customers
    where cust.City == "London"
    orderby cust.CustomerID
    select cust).Skip(1).Take(1);

Dim custQuery = _
    From cust In db.Customers _
    Where cust.City = "London" _
    Order By cust.CustomerID _
    Select cust Skip 1 Take 1

The generated SQL for this code moves the ordering to the end, as follows:

SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName],
FROM [Customers] AS [t0]
WHERE (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT TOP 1 [t1].[CustomerID]
        FROM [Customers] AS [t1]
        WHERE [t1].[City] = @p0
        ORDER BY [t1].[CustomerID]
        ) AS [t2]
    WHERE [t0].[CustomerID] = [t2].[CustomerID]
    ))) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]

It becomes obvious that all the specified ordering must be consistent when Take and Skip are chained together. Otherwise, the results are undefined.

Both Take and Skip are well-defined for non-negative, constant integral arguments based on the Standard Query Operator specification.

Operators with No Translation

The following methods are not translated by LINQ to SQL. The most common reason is the difference between unordered multisets and sequences.

Operators	Rationale
TakeWhile, SkipWhile	SQL queries operate on multisets, not on sequences. `ORDER BY` must be the last clause applied to the results. For this reason, there is no general-purpose translation for these two methods.
Reverse	Translation of this method is possible for an ordered set but is not currently translated by LINQ to SQL.
Last, LastOrDefault	Translation of these methods is possible for an ordered set but is not currently translated by LINQ to SQL.
ElementAt, ElementAtOrDefault	SQL queries operate on multisets, not on indexable sequences.
DefaultIfEmpty (overload with default arg)	In general, a default value cannot be specified for an arbitrary tuple. Null values for tuples are possible in some cases through outer joins.

Expression Translation

Null semantics

LINQ to SQL does not impose null comparison semantics on SQL. Comparison operators are syntactically translated to their SQL equivalents. For this reason, the semantics reflect SQL semantics that are defined by server or connection settings. For example, two null values are considered unequal under default SQL Server settings, but you can change the settings to change the semantics. LINQ to SQL does not consider server settings when it translates queries.

A comparison with the literal null is translated to the appropriate SQL version (is null or is not null).

The value of null in collation is defined by SQL Server. LINQ to SQL does not change the collation.

Aggregates

The Standard Query Operator aggregate method Sum evaluates to zero for an empty sequence or for a sequence that contains only nulls. In LINQ to SQL, the semantics of SQL are left unchanged, and Sum evaluates to null instead of zero for an empty sequence or for a sequence that contains only nulls.

SQL limitations on intermediate results apply to aggregates in LINQ to SQL. The Sum of 32-bit integer quantities is not computed by using 64-bit results. Overflow might occur for a LINQ to SQL translation of Sum, even if the Standard Query Operator implementation does not cause an overflow for the corresponding in-memory sequence.

Likewise, the LINQ to SQL translation of Average of integer values is computed as an integer, not as a double.

Entity Arguments

LINQ to SQL enables entity types to be used in the GroupBy and OrderBy methods. In the translation of these operators, the use of an argument of a type is considered to be the equivalent to specifying all members of that type. For example, the following code is equivalent:

db.Customers.GroupBy(c => c);
db.Customers.GroupBy(c => new { c.CustomerID, c.ContactName });

db.Customers.GroupBy(Function(c) c)
db.Customers.GroupBy(Function(c) New With {c.CustomerID, _
    c.ContactName})

Equatable / Comparable Arguments

Equality of arguments is required in the implementation of the following methods:

LINQ to SQL supports equality and comparison for flat arguments, but not for arguments that are or contain sequences. A flat argument is a type that can be mapped to a SQL row. A projection of one or more entity types that can be statically determined not to contain a sequence is considered a flat argument.

The following are examples of flat arguments:

db.Customers.Select(c => c);
db.Customers.Select(c => new { c.CustomerID, c.City });
db.Orders.Select(o => new { o.OrderID, o.Customer.City });
db.Orders.Select(o => new { o.OrderID, o.Customer });

db.Customers.Select(Function(c) c)
db.Customers.Select(Function(c) New With {c.CustomerID, c.City})
db.Orders.Select(Function(o) New With {o.OrderID, o.Customer.City})
db.Orders.Select(Function(o) New With {o.OrderID, o.Customer})

The following are examples of non-flat (hierarchical) arguments:

// In the following line, c.Orders is a sequence.
db.Customers.Select(c => new { c.CustomerID, c.Orders });
// In the following line, the result has a sequence.
db.Customers.GroupBy(c => c.City);

' In the following line, c.Orders is a sequence.
db.Customers.Select(Function(c) New With {c.CustomerID, c.Orders})
' In the following line, the result has a sequence.
db.Customers.GroupBy(Function(c) c.City)

Visual Basic Function Translation

The following helper functions that are used by the Visual Basic compiler are translated to corresponding SQL operators and functions:

CompareString
DateTime.Compare
Decimal.Compare
IIf (in Microsoft.VisualBasic.Interaction)

Conversion methods:

ToBoolean
ToSByte
ToByte
ToChar
ToCharArrayRankOne
ToDate
ToDecimal
ToDouble
ToInteger
ToUInteger
ToLong
ToULong
ToShort
ToUShort
ToSingle
ToString

Inheritance Support

Inheritance Mapping Restrictions

For more information, see How to: Map Inheritance Hierarchies.

Inheritance in Queries

C# casts are supported only in projection. Casts that are used elsewhere are not translated and are ignored. Aside from SQL function names, SQL really only performs the equivalent of the common language runtime (CLR) Convert. That is, SQL can change the value of one type to another. There is no equivalent of CLR cast because there is no concept of reinterpreting the same bits as those of another type. That is why a C# cast works only locally. It is not remoted.

The operators, is and as, and the GetType method are not restricted to the Select operator. They can be used in other query operators also.

SQL Server 2008 Support

Starting with the .NET Framework 3.5 SP1, LINQ to SQL supports mapping to new date and time types introduced with SQL Server 2008. But, there are some limitations to the LINQ to SQL query operators that you can use when operating against values mapped to these new types.

Unsupported Query Operators

The following query operators are not supported on values mapped to the new SQL Server date and time types: DATETIME2, DATE, TIME, and DATETIMEOFFSET.

Aggregate
Average
LastOrDefault
OfType
Sum

For more information about mapping to these SQL Server date and time types, see SQL-CLR Type Mapping.

SQL Server 2005 Support

LINQ to SQL does not support the following SQL Server 2005 features:

Stored procedures written for SQL CLR.
User-defined type.
XML query features.

SQL Server 2000 Support

The following SQL Server 2000 limitations (compared to Microsoft SQL Server 2005) affect LINQ to SQL support.

Cross Apply and Outer Apply Operators

These operators are not available in SQL Server 2000. LINQ to SQL tries a series of rewrites to replace them with appropriate joins.

Cross Apply and Outer Apply are generated for relationship navigations. The set of queries for which such rewrites are possible is not well defined. For this reason, the minimal set of queries that is supported for SQL Server 2000 is the set that does not involve relationship navigation.

text / ntext

Data types text / ntext cannot be used in certain query operations against varchar(max) / nvarchar(max), which are supported by Microsoft SQL Server 2005.

No resolution is available for this limitation. Specifically, you cannot use Distinct() on any result that contains members that are mapped to text or ntext columns.

Behavior Triggered by Nested Queries

SQL Server 2000 (through SP4) binder has some idiosyncrasies that are triggered by nested queries. The set of SQL queries that triggers these idiosyncrasies is not well defined. For this reason, you cannot define the set of LINQ to SQL queries that might cause SQL Server exceptions.

Skip and Take Operators