The Concat<(Of <(TSource>)>) method is defined for ordered multisets where the order of the receiver and the order of the argument are the same. Concat<(Of <(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<(Of <(TSource>)>) does not preserve the order of its arguments. To ensure appropriate ordering, you must explicitly order the results of Concat<(Of <(TSource>)>).

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

Note:
Take<(Of <(TSource>)>) and Skip<(Of <(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).

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<(Of <(TSource>)>) and Skip<(Of <(TSource>)>) are chained together. Otherwise, the results are undefined.

Both Take<(Of <(TSource>)>) and Skip<(Of <(TSource>)>) are well-defined for non-negative, constant integral arguments based on the Standard Query Operator specification.

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

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.

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.

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

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

Contains

Skip<(Of <(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);

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:

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

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.

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

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.

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.

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.

Take<(Of <(TSource>)>) and Skip<(Of <(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).