Remarks and limitations

Every technology comes with a set of limitations. Linq in general is no exception to this rule and also Linq to LLBLGen Pro isn't an exception. This section discusses the limitations you might run into with Linq to LLBLGen Pro and also the constructs which aren't supported, which aren't implemented (because the Linq construct had no meaning in a SQL targeting query for example).

This all might sound rather dramatic, but in general you won't run into many, if any, of the material discussed below. Nevertheless, it's recommended that a Linq to LLBLGen Pro developer understands which limitations are present and also the reasons behind them.

Queryable methods which are partly implemented

The following methods are not implemented for 100%. Often this means that a particular behavior of the method or an overload isn't supported or that it only works in specific cases. 'Partly implemented' doesn't mean the code isn't finished, it means the method doesn't function as described in the MSDN in all cases you'd expect, which are described with the methods below.

FirstOrDefault. This method has the same result as First: the 'Default' part isn't implemented: if there is no result, you won't receive any result but null. If First is called on a set which is empty, null is returned. This is because First and FirstOrDefault result in a TOP/LIMIT clause being added to the query.
SingleOrDefault. This method has the same result as Single: the ' Default' part isn't implemented: if there is no result, you won't receive any result. However, if there are 0 rows, it doesn't throw an exception, contrary to 'Single', which will throw an exception when 0 rows are returned by the query.
ElementAtOrDefault. This method has the same result as ElementAt: the 'Default' part isn't implemented: if there's no result, you won't receive any result.
LongCount. This method has the same result as Count if the database doesn't support COUNT_BIG, otherwise it will return the result of COUNT_BIG as 64bit value.

Remarks on several extension methods and constructs

The following remarks have to be understood when using the methods described below:

Take, TakePage and ElementAt require an ordering in the resultset. If you don't supply an orderby clause, the results are undefined: it might be the wrong row is returned.
'as' in projections is ignored: entities are always materialized in the type they represent.
'as' only works with entity types.
'as' doesn't add a type filter. This construct however does add a type filter:
```
var q = from e in metaData.Employee
        let x = e as BoardMemberEntity
        where x != null
        select e;
```
It's more efficient to use an is or Cast<entitytype> statement instead.
OfType only works with entity types.
Using method calls of methods which don't have a db mapping is only useful in the projection of the query itself, and then only if it's the last statement. Otherwise you run the risk that the projection is actually wrapped inside another query which will fail, as the method can't be run in-memory in that situation.
Use 'let' with care. Every 'let' statement wraps the inner query in a complete SELECT statement using a derived table. Using 'let' extensively therefore will create slower queries. If you want / need to use imperative programming with the linq data, consider C# code outside the query.
'Single' does throw, on failure (i.e. if the query results in more than 1 element) an exception (NotSupportedException), however only if 'Single' was used on the outer query. If it's used in an inner/subquery, no exception is thrown as it's impossible to check the number of elements in that stage, in all cases. If you require the exception to be thrown, write a count query separately and run that first. In general it's not recommended to use exceptions for control flow.
The parameters of a Table Valued Function call used inside a Linq query have to be constants, you can't specify a part of the query as parameter input for a Table Valued Function call.
Cast<T> already filters on entities of type T. You therefore don't have to use is in a where clause:

// redundant type filter
var q = from e in metaData.Employee.Where(e=>e is BoardMemberEntity).Cast<BoardMemberEntity>()
        select e;

// equal query:
var q = from e in metaData.Employee.Cast>BoardMemberEntity<()
        select e;

SelectMany on a navigated property with an aggregate might fail in the projection. Example:

var q = from c in Customer
        select new
        {
            c.CustomerId,
            OrderDetailsCount = c.Orders.SelectMany(o=>o.OrderDetails).Count()
        }

This fails because the inner order details can't be tied to the outer query using a correlation predicate due to the way the Linq expression tree is constructed and the way the SelectMany() is present in the expression tree. Rewrite the above query to the following instead:

var q = from c in Customer
        select new
        {
            c.CustomerId,
            OrderDetailsCount = c.Orders.Sum(o=>o.OrderDetails.Count())
        }

Not supported Queryable extension methods / overloads

The following methods or specific overloads of methods aren't supported. In general the following rules apply:

Any overload which

takes a lambda with an index parameter.
relies on the properties of sequential rows, such as TakeWhile (except ElementAt())
relies on an arbitrary CLR implementation, such as IComparer<T>.

aren't supported.

The methods which aren't supported, specified more in detail.

Aggregate. Aggregate isn't really applyable in DB queries. To execute aggregate functions, use the extension methods for the functions themselves: Max, Min, Sum, Average, Count, StandardDeviation, Variance and CountLong. Don't confuse this extension method with the VB.NET keyword Aggregate, which is compiled by the VB.NET compiler to a call to the aggregate function mentioned, e.g. Count.
DefaultIfEmpty(object). The normal DefaultIfEmpty is supported, but the DefaultIfEmpty(object) overload isn't supported.
Last, LastOrDefault. To be able to reverse an ordering of a set which is a result of a projection, the whole query likely has to be rewritten, especially if the query has ordering on related elements. Therefore these methods aren't supported.
TakeWhile, SkipWhile. These methods aren't supported because it's not always possible to perform this operation on a set to obtain the same results as if the set was a sequence: if the set isn't ordered on the field(s) in the predicate, applying the predicate on the set could result in wrong results: the values matching after the first element not matching are also matching the predicate.
Reverse. Reverse isn't supported, because it has to reverse the resultset based on the reverse of the order specified, however it's not a given that the reverse of the order given also gives the reversed resultset of the original order.
SequenceEqual. It's not really possible to determine whether two sequences are equal with just set operations, as SQL allows. The problem isn't the check if both sequences contain the same elements, the problem is if both sequences have those elements in the same order as well.

Not supported constructs

The following constructs aren't supported.

Invoking an in-memory lambda inside the query (other than projection). If a lambda, e.g. Func<string, string> foo = s => s.Substring(2, 3); is used inside a Where, it can't be translated to a query element, as it's an in-memory function which can't be executed on the database. A lambda/delegate invoked inside the projection does work, though the same rules apply as with the calls to in-memory methods in the projection.

Nested queries in projection which have no correlation filter. A nested query inside the projection of a parent query has to have a correlation filter to tie the nested query's results to the parent's row, e.g. where o.CustomerID = parent.CustomerID. If this correlation filter isn't found and no correlation relation is found either through specification of a related set, e.g. c.Orders, an exception is thrown, as the nested set can't be tied to the right parent without this filter. The reason this isn't supported is that the alternative would be one query per parent row, which is very bad for performance overall. See the examples below for supported and not supported constructs in this context:

C#VB.NET

// Example of not supported query as there's no connection between order and customer.
var q = from c in metaData.Customer
        select new { 
               c.CompanyName, 
               Orders = from o in metaData.Order
                        where o.EmployeeId == 3 
                        select o
        };

// example of supported query
var q = from c in metaData.Customer
        select new { 
               c.CompanyName, 
               Orders = from o in metaData.Order
                        where o.EmployeeId == 3 
                              && c.CustomerId == o.CustomerId // correlation
                        select o
        };

// or more simpler, also supported:
var q = from c in metaData.Customer
        select new { 
               c.CompanyName, 
               Orders = from o in c.Order  // correlation
                        where o.EmployeeId == 3 
                        select o
        };

// also supported, as correlation relation produces the filter required
var q = from c in metaData.Customer
        select new {
               c.CompanyName,
               Orders = c.Orders   // contains correlation relation.
     };

Dim q = From c In metaData.Customer _
        Select New With { _
               .CompanyName = c.CompanyName, _
               .Orders = From o In metaData.Order _
                         Where o.EmployeeId = 3 _
                         Select o _
        }

'  example of supported query
Dim q = From c In metaData.Customer _
        Select New With { _
               .CompanyName = c.CompanyName, 
               .Orders = From o In metaData.Order _
                         Where o.EmployeeId = 3 _
                               AndAlso c.CustomerId = o.CustomerId _
                         Select o _
        }

' or more simpler, also supported:
Dim q = From c In metaData.Customer _
        Select New With { _
               .CompanyName = c.CompanyName, _
               .Orders = From o In c.Order _ 
                         Where o.EmployeeId = 3 _
                         Select o _
        }

' also supported, as correlation relation produces the filter required
Dim q = From c In metaData.Customer _ 
        Select New With { _
               .CompanyName = c.CompanyName, _
               .Orders = c.Orders _ 
     }

'as' only works with entity types. Using as in a Linq query is supported, though not for types other than entity types. If you want to cast elements in the query, use a cast operator.
Nested queries which use polymorphic correlation filters. Nested queries have to have an exact correlation filter. You can't do a polymorphic fetch of nested data based on a filter which is only true for certain types of parent. E.g. you can't fetch companycar related data into a fetch of employee data, as employee doesn't have a relation with companycar, BoardMember does. If you require this kind of hierarchies, use WithPath and a prefetch path construct and fetch entities instead.
Predicates which use solely Linq to Objects / in-memory elements should be avoided. If the predicate in the where clause of the linq query contains solely Linq to Objects elements (e.g. where listOfValues.Count > 3), the predicate is evaluated in-memory and the result, a bool which is true or false is either ignorable (if true) or makes the query useless (false). The predicate is therefore ignored completely. If you want to use this predicate in your logic, remove the predicate from the query and use it in an if statement which wraps the query as described in the item below about IList.Contains(constant)

IList.Contains(constant) in filters. Although it can be evaluated by the Linq provider, it's of no use inside the query: if the constant is found in the in-memory IList, the predicate can be ignored, and if it's not, the query will always fail.

C#VB.NET

// not supported query construct.
List<int> someIDs = new List<int>() { 1, 2, 3};
int param = 3;

//...
var q = from p in metaData.Product
        where someIDs.Contains(param)
        select p;

// use the following instead:
if(someIDs.Contains(param))
{
    var q = from p in metaData.Product
               select p;

    // enumerate q to execute the query here.
}

' not supported query construct.
Dim someIDs As New List(Of  Integer)()
someIDs.Add(1)
someIDs.Add(2)
someIDs.Add(3)
Dim param As Integer = 3

'...
Dim q = From p in metaData.Product _
        Where someIDs.Contains(param) _
        Select p

' use the following instead:
If someIDs.Contains(param) Then
    Dim q = From p in metaData.Product _
         Select p

    ' enumerate q to execute the query here.
End If

Usage of Convert.ChangeType inside the query. If Convert.ChangeType is used in the query, it won't be applied and the query will likely not result in the query anticipated.
string.Contains(constant) in filters. Similar to IList.Contains(constant), it can be evaluated at runtime but it's actually of no use. Move the Contains call to the outside of the query to be used in an if-expression. See for an example the IList.Contains(constant) example above.
string / query.Contains/StartsWith/EndsWith(query/entityfield). Contains/StartsWith and EndsWith extension methods on string elements (entity field, query producing a single string) only support a constant string value as parameter, not a query / entity field. We might add this in a later stage, as it requires extensive string concatenation / function calls to wrap the operand (query/entityfield) in the LIKE pattern wildcard % character.

Nested from clauses with additional limitations/aggregates related to outer from clauses. When a nested from is placed inside a Linq query, it is typically a CROSS JOIN, which means a cross-product of outer and inner from elements. If the inner element has additional specifications, like a .Where() filter, which relies on a value in the outer from element (See example below), the query can't be formed as a CROSS JOIN anymore, as no database supports that syntaxis. SQL Server 2005 or higher supports CROSS APPLY, however LLBLGen Pro doesn't. Therefore these queries aren't supported. A workaround exists: rewriting the where/conditions in the query's where clause or by using a join construct.

It's not always possible to rewrite the query: a Take or aggregate in the right side doesn't allow for rewriting the query that easily. LLBLGen Pro doesn't support CROSS APPLY so the result query always has to be writable with normal OUTER JOIN or CROSS JOIN statements.

Example: Here, the Order element has a Where clause which refers to a Customer element. This will result in illegal SQL as it's not possible to create a proper JOIN statement for this. Only SQL Server supports this with CROSS APPLY, which isn't a supported join operator by LLBLGen Pro.

var q = from c in metaData.Customer
    from o in metaData.Order.Where(o=>o.CustomerID==c.CustomerID)
    select c;

    // work around this by:
var q = from c in metaData.Customer
        from o in metaData.Order
        where o.CustomerID==c.CustomerID
        select c;

// Another example of a not supported query:
var q = from c in metaData.Customer
        from o in metaDAta.Order.Where(o => o.CustomerId == c.CustomerId).Take(10)
        select new { c.ContactName, o.OrderDate };

// example of code which is supported:
var q = from c in metaData.Customer
        from o in metaData.Order.Where(o=>o.CustomerID=="ALFKI")
        select c;

Dim q = From c in metaData.Customer _
    From o In metaData.Order.Where(Function(o) o.CustomerID=c.CustomerID) _
    Select c

' work around this by:
Dim q = From c In metaData.Customer _
        From o In metaData.Order _
        Where o.CustomerID=c.CustomerID _
        Select c

' Another example of a not supported query:
Dim q = From c In metaData.Customer _
        From o In metaData.Order.Where(Function(o) o.CustomerID = c.CustomerID).Take(10) _
        Select c.ContactName, o.OrderDate

' example of code which is supported:
Dim q = From c In metaData.Customer _
        From o In metaData.Order.Where(Function(o) o.CustomerID="ALFKI") _
        Select c

IList.Except(database query). It's not possible to convert this construct to SQL, as Linq works with sequences, so the result of this should be a list of objects of the same type as located in the IList, however it's unknown how to fabricate those, because that's not formulated in the query. IList.Except(in-memory list) is supported, because that's handled before the query is handled by Linq to Objects.
Usage of .NET types which have no real mapping in the database type used. For example, if you use boolean values as constants in the projection of a query on Oracle, this will likely fail.
The VB.NET 'Like' operator isn't supported. The VB.NET 'Like' operator assures special filtering mechanics, but these are often not available on the target databases supported by LLBLGen Pro. The provider will throw an ORMQueryConstruction exception when a Like operator is detected. To work around this, use StartsWith(), EndsWith() or Contains() method calls to produce proper LIKE queries.