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.
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:
Using LINQ to Objects operators with a LightSpeed query
// Runtime error: can't translate TakeWhile to SQL var millionaires = unitOfWork.Employees
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.
Comparing attributes of associated entities in a LINQ query
// Runtime error: association must appear on left where order.Price > order.Customer.CreditLimit
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.
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
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.
Some databases lack support for certain functions or operations that are supported elsewhere. See Working with Database Providers for database-specific information.