Standard Query Operator Translation (LINQ to SQL)

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.

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<TSource> method is defined for ordered multisets where the order of the receiver and the order of the argument are the same. Concat<TSource> works as UNION ALL over the multisets followed by the common order.

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

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<TSource> and Skip<TSource> methods are well defined only against ordered sets. The semantics for unordered sets or multisets are undefined.

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:

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

var custQuery = 
    (from cust in db.Customers
    where cust.City == "London" 
    orderby 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<TSource> and Skip<TSource> are chained together. Otherwise, the results are undefined.

Both Take<TSource> and Skip<TSource> 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.

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(Function(c) c)
db.Customers.GroupBy(Function(c) New With {c.CustomerID, _
    c.ContactName})

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

Equatable / Comparable Arguments

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

Contains

Skip<TSource>

Union

Intersect

Except

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.

Following are examples of flat arguments:

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})

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 });    

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

' 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)

// 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);

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:

Inheritance Support

Inheritance Mapping Restrictions

For more information, see How to: Map Inheritance Hierarchies (LINQ to SQL).

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 (LINQ to SQL).

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

Take<TSource> and Skip<TSource> 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 (LINQ to SQL).

Object Materialization

Materialization creates CLR objects from rows that are returned by one or more SQL queries.

• The following calls are executed locally as a part of materialization:

  • Constructors

  • ToString methods in projections

  • Type casts in projections

• Methods that follow the AsEnumerable<TSource> method are executed locally. This method does not cause immediate execution.

• You can use a struct as the return type of a query result or as a member of the result type. Entities are required to be classes. Anonymous types are materialized as class instances, but named structs (non-entities) can be used in projection.

• A member of the return type of a query result can be of type IQueryable<T>. It is materialized as a local collection.

• The following methods cause the immediate materialization of the sequence that the methods are applied to:

  • ToList<TSource>

  • ToDictionary

  • ToArray<TSource>

See Also

Tasks

How to: Return or Skip Elements in a Sequence (LINQ to SQL)

How to: Concatenate Two Sequences (LINQ to SQL)

How to: Return the Set Difference Between Two Sequences (LINQ to SQL)

How to: Return the Set Intersection of Two Sequences (LINQ to SQL)

How to: Return the Set Union of Two Sequences (LINQ to SQL)

Other Resources

Reference (LINQ to SQL)