LINQ Support Limitations

The LINQ API was designed by Microsoft to represent a wide range of set-oriented operations, not all of which are meaningful or practical in the context of a relational database or the SQL query language.  Some LINQ queries that are syntactically valid are therefore not supported in LightSpeed, and there are some limitations on the terms that can be used in a LINQ query to LightSpeed.  This section summarises these limitations.

Unsupported LINQ Operators

The following operations are not supported by the LightSpeed LINQ Query Provider, and no support is planned.

·         SequenceEqual: Compares two sequences for equality

·         SkipWhile: Skips elements while a condition remains true

·         TakeWhile: Takes elements while a condition remains true

If you need to use one of these operators, you must materialise the query results using AsEnumerable or ToList.  You can then process it using LINQ to Objects, which does support the operators mentioned above.  For example:

// Runtime error: can't translate TakeWhile to SQL var millionaires = unitOfWork.Employees
                             .OrderByDescending(e => e.Salary)
                             .TakeWhile(e => e.Salary >= 1000000);
// Success: translates first two lines to SQL, then runs TakeWhile on the results var millionaires = unitOfWork.Employees
                             .OrderByDescending(e => e.Salary)
                             .AsEnumerable() // subsequent operators run client-side
                             .TakeWhile(e => e.Salary >= 1000000);

Comparisons to an Associated Entity

In a LINQ query, you can compare values from one entity with values from an associated entity.  However, you must write the query so that the associated entity appears on the left of the comparison operator.

// Runtime error: association must appear on left where order.Price > order.Customer.CreditLimit
// Corrected version
where order.Customer.CreditLimit <= order.Price

CLR Methods in a LINQ Query

The translation of CLR methods to SQL is dependent on the database provider.  As a guideline:

·         String members which correspond to the SQL LIKE operator such as Contains and StartsWith are generally supported on all databases. (Note, however, that the comparand must be a string known at query time; it cannot be another property of the range variable. For example, you can write where m.Name.Contains(filterBox.Text), but not where m.Name.Contains(m.Email). This is a restriction of the SQL LIKE operator.)

·         Queries involving string concatenation may not be correctly handled on non-SQL Server databases.

·         The extended patterns of the Visual Basic Like operator are supported only on SQL Server.

·         Math.Min and Math.Max are supported on all databases.

·         Other Math members such as Abs, Ceiling and Log are generally supported on all databases except SQLite.

·         DateTime members relating to date or time parts (Year, Month, Day, Hour, Minute, Second) are generally supported on all databases. The DateTime.Date property is supported only on SQL Server 2008, Oracle and PostgreSQL. The DateTime.Time property is not supported on any database.

Joining, Grouping and Combining

Some complex joining and grouping scenarios are not supported. Support for some scenarios will be added in future builds; please contact Mindscape if there is a particular pattern you would like us to support.

Unsupported scenarios include:

·         Binary expressions used as grouping keys

·         Group joins over more than two tables

·         Queries that self-join in their criteria

·         Combining a grouping with joins

·         Some grouping with ordering or paging queries

Set operations

There is only partial support for the Concat, Intersect, Union and Except operators.  These can be used to combine entity sets of the same type, but support for other scenarios (such as projections from multiple tables) is very limited.  Many databases do not support the Intersect operator at all.

Database Specific Limitations

Some databases lack support for certain functions or operations that are supported elsewhere.  See Working with Database Providers for database-specific information.