General usage

This section contains the usage descriptions of some of the Linq constructs and extension methods available. Not all constructs are discussed here: IQueryable extension methods / Linq constructs which are implemented as described in the MSDN documentation aren't discussed here, as that would be redundant.

If a method isn't discussed on this page or elsewhere in this manual, you can assume the behavior of the method is as described in the MSDN documentation of that method.

This section will also describe the extension methods we added to enrich the Linq support to expose elements of the LLBLGen Pro API which weren't covered by the Linq syntax.

Important!

Linq queries against a database, using the LinqMetaData queryable object can still contain in-memory constructs, like creating Lists, arrays, calling a method etc. Linq to LLBLGen Pro tries as much as possible to execute this code locally in-memory, if appropriate: either when the data is fetched and placed into objects, or when the query is constructed.

It can be that this isn't always possible, for example if you specify a method call on an in-memory object which relies on data known only during the query execution in the database. Typically this occurs when Linq to Object queries are merged with Linq to LLBLGen Pro queries. It's recommended that you don't merge these queries, unless you're sure it will work (and not throw an exception at runtime): keep database queries separated from in-memory (Linq to Objects) queries as much as possible: not only is it more clear what's executed where, but it's also less error prone.

Aggregates

Linq supports five aggregate functions: Count(predicate), Sum, Max, Min and Average. There's a 6th, LongCount, but it can be seen as the same aggregate as Count, only that it will return a long/Int64 instead of an int/Int32. LLBLGen Pro supports more aggregate functions than Linq currently offers. The following IQueryable extension methods have been added to support the extra aggregates LLBLGen Pro supports: ([] marks optional parameters, offered by different overloads).

  • CountColumn(field [, bool applyDistinct]). This method can count on a field and if true is passed for applyDistinct, the count will be a Count distinct. Currently Count() is a CountRow (which is a Count(*))
  • StandardDeviation(field [, bool applyDistinct]). This method is similar to StDev in normal databases. If true is specified for applyDistinct, it will be a distinct StDev. Distinct is only supported on a small set of databases. Please consult your databases' manual if it supports StdDev with distinct.
  • Variance(field [, bool applyDistinct]). This method is similar to Var(field) in normal databases. If true is specified for applyDistinct it will be a distinct Var. Distinct is only supported on a small set of databases. Please consult your databases' manual if it supports Var with distinct.

You can call these methods on a query or a set inside a query similar to when you call Max, Sum, Count or the other standard Linq aggregate functions.

VB.NET Specific

With Linq there are differences between C# and VB.NET. Below are specific differences explained and what VB.NET users should use in some situations.

Don't use the Aggregate keyword

VB.NET users shouldn't use the Aggregate keyword, as the VB.NET compiler doesn't generate any Expression tree from the Aggregate usage in some situations due to a bug in the VB.NET compiler. Instead it will produce a query which fetches all data of the target table/query into memory and apply the aggregate function on that data. This is very inefficient. As it's unclear when the VB.NET compiler refuses to produce a proper Expression tree and when it works, it's recommended to use the extension methods to be safe. So instead of:

Dim max = Aggregate o In metaData.Order Into m=Max(o.OrderDate)

do:

Dim max = metaData.Order.Max(Function(o) o.OrderDate)

Use the .Value property of Nullable(Of T)

VB.NET users should use the .Value property of Nullable(Of T) typed fields instead of just the field in filter expressions, like expressions in the Where clause. The VB.NET compiler introduces coalesce statements on these boolean expressions if it detects a Nullable(Of T) typed element in the expression which are useless and which bloat the query unnecessary.

Linq to LLBLGen Pro strips off these needless coalesce expressions if it runs into one of them (except in the projection (select)), however it can be it can't make the proper decision and the query fails.

Example of a proper query (o.EmployeeId is a Nullable(Of Integer) typed field):

Dim q = From o In metaData.Order _
     Where o.EmployeeId.Value=2 _
     Select o

Example of a query which contains the Coalesce operator around EmployeeId=2:

Dim q = From o In metaData.Order _
     Where o.EmployeeId=2 _
     Select o

The unnecessary Coalesce expression is detected in this case, however to avoid unnecessary errors and unnecessary complex SQL, use the '.Value' property on the Nullable(Of T) typed fields.

To filter on Nothing (null), it's necessary to use the field instead of the '.Value' property, though this is ok, as the VB.NET compiler doesn't wrap these expressions with a coalesce statement:

Dim q = From o In metaData.Order _
     Where o.EmployeeId = Nothing _
     Select o

Use AndAlso / OrElse in Where clauses instead of And / Or

The VB.NET compiler converts 'And' and 'Or' to bitwise operations, and when used in Linq queries this results in bitwise operations in the expressions tree as well. As it's unknown to the Linq provider what language the expression tree originates from, it's not doable to determine whether the developer meant a bitwise operation or a logical operation.

This means, if you mean an 'And' in a predicate expression, specify 'AndAlso' instead. The same goes for 'Or', please specify 'OrElse' instead. See the example below

Dim q = From o In metaData.Order _
     Where o.EmployeeId=2 AndAlso o.CustomerId="CHOPS" _
     Select o

Group by

Grouping of data is an important aspect of Linq and also of Linq to LLBLGen Pro. Together with aggregate functions on the grouped data, complex queries can be written. Linq to LLBLGen Pro supports group by constructs in all possible situations, including grouping on constants, grouping on compound keys (using a new anonymous type, see example below) and specifying group targets in the projection.

Below, you'll find two examples of the usage of group by in a Linq to LLBLGen Pro query: one groups on multiple fields, the other one shows how to produce a hierarchical set where per group key (the field(s) the set was grouped on) the matching data elements (e.g. entities) are stored.

Group by multiple keys

// per country-city the # of customers is determined.
var q = from c in metaData.Customer
        group c by new { c.Country, c.City } into g
        select new { g.Key, Amount = g.Count()};

' per country-city the # of customers is determined.
Dim q = From c In metaData.Customer _
        Group By c.Country, c.City _
        Into Count() _
        Select Country, City, Count

Select group data in hierarchy

// returns set of objects which contain per country the customers in a hierarchy
var q = from c in metaData.Customer
        group c by c.Country into g
        select g;

' returns set of objects which contain per country the customers in a hierarchy
Dim q = From c In metaData.Customer _
        Group c By c.Country Into G = Group

The second example shows a fetch of a hierarchical set: per key (country) the set of customers in that country is returned. Linq to LLBLGen Pro has an efficient hierarchical fetch engine which produces two queries for this fetch.

Other Linq providers like Linq to Sql produce per key a query, which can result in much lower performance. Later on in this section, the hierarchical fetches are more discussed in detail.

Important!

VB.NET users shouldn't append the Into grouping clause with a Select statement if they want to refer to the whole Group, like in the example above where the group is stored in variable 'G'. Doing so will wrap the nested query into a derived table, which makes it impossible to fetch it, as a nested query can only be present in the projection.

Order by

You can sort on any field in the projection or in the scope of the query, as you'd expect in a Linq query. As Linq also allows you to specify functions inside the query, you can also sort on the outcome of a function. See the following example where all Customer entities are sorted on the second character in the CustomerId:

var q = from c in metaData.Customer
        orderby c.CustomerId[1]
        select c;

Dim q = From c In metaData.Customer _
        Order By c.CustomerId(1) _
        Select c

Union / Concat

You can use Union and Concat to mimic the SQL statements UNION and UNION ALL. As with the SQL counterparts, the projections of both queries have to result in the same resultset. Here's an example which uses Union to merge two resultsets and also uses a nested Union call to merge a third resultset into the second query first. This way you can control which set is unioned first.

var q1 = from c in metaData.Customer
         where c.CompanyName.StartsWith("B")
         select new { c.CustomerId, c.CompanyName };
var q3 = from c in metaData.Customer
         where c.CompanyName.EndsWith("N")
         select new { c.CustomerId, c.CompanyName };
var q2 = (from c in metaData.Customer
          where c.CompanyName.EndsWith("S")
          select new {c.CustomerId, c.CompanyName}).Union(q3);
var q4 = q1.Union(q2).OrderBy(c=>c.CustomerId);

Dim q1 = From c in metaData.Customer _
         Where c.CompanyName.StartsWith("B") _
         Select New With { .CustomerId = c.CustomerId, .CompanyName = c.CompanyName }
Dim q3 = From c in metaData.Customer _
         Where c.CompanyName.EndsWith("N") _
         Select New With { .CustomerId = c.CustomerId, .CompanyName = c.CompanyName }
Dim q2 = (From c in metaData.Customer
          Where c.CompanyName.EndsWith("S")
          Select New With { .CustomerId = c.CustomerId, .CompanyName = c.CompanyName }) _
         .Union(q3)
Dim q4 = q1.Union(q2).OrderBy(Function(c) c.CustomerId)

Limitations

A query which is unioned into another query using Union or Concat can't contain a nested query in the projection nor can it prefetch path definitions nor paging directives.

Using a Table Valued Function call

To fetch entities mapped onto the resultset of a Table Valued Function, the LinqMetaData class offers each mapped Table Valued Function which is the target of an entity as a method. In the following example, the Table Valued Function CustomersFromCountry which returns all Customer entities for the country specified is used.

var q = from c in metaData.CustomersFromCountry("Germany")
        where c.CompanyName.StartsWith("S")
        select c;

Dim q = From c in metaData.CustomersFromCountry("Germany") _
        Where c.CompanyName.StartsWith("S") _
        Select c

The query above results in SQL like: (@p0 is 'Germany' and @p1 is 'S%'.)

SELECT LPA_R1.CustomerID As CustomerId, ...
FROM dbo.fn_CustomersFromCountry(@p0) AS LPA_R1
WHERE LPA_R1.CompanyName LIKE @p1

However the parameters have to be filled in with constants: you can't specify an element of the query as parameter value: the value of a parameter has to be specified as an in-memory value, not as a value which is obtained from the database. An example query which fails:

// Will fail!
var q = from c in metaData.Customer
        where metaData.GetOrdersForCustomer(c.CustomerId).Contains(order)
        select c;

Fetching a typed view

When a Typed View has its OutputSetting set to PocoWithLinqQuery, the designer will generate a Poco class with a property in the LinqMetaData class so the typed view can be fetched using a normal linq query.

Typed view mapped onto a table / view

A property is generated in the LinqMetaData class for each typed view, which returns a DataSource(2) Object, named after the typed view. The DataSource(2) object is an IQueryable<TypedViewNameRow>. Usage is the same as with entities in Linq: Below the Invoices typed view is fetched and filtered on OrderId

var q = from x in metaData.Invoices
        where x.OrderId == 10254
        select x;

Typed view mapped onto a Table-valued function resultset.

A method is generated in the LinqMetaData class which has the name of the Table-Valued Function which returns the typed view row and for each parameter of the Table-Valued-Function Call the method has a corresponding argument.

This is equal to the method generated for Table Valued Functions which return an entity, so the api is more predictable. The method returns the same DataSource(2) object as the property a normal TypedView would get, except it has a wrapped Table-Valued Function call set as well, similar to the Table-Valued Function which returns an entity.

This looks like the following:

var q = from o in metaData.GetOrdersForCustomerTV("ALFKI")
        where o.EmployeeId == 4
        select o;

Here, the typed view 'GetOrdersForCustomerTV' which is mapped onto the Table-Valued function 'GetOrdersForCustomer' is fetched, by using the method generated in the LinqMetaData class. An additional filter is appended, which filters the resultset of the Table-Valued-function on the EmployeeId field.

Fetching a Typed List

When the OutputType setting for a typed list is set to PocoWithLinqQuery, a Linq query is generated into the LinqMetaData class in the form of a method: GetTypedListNameTypedList(). This method returns an IQueryable<T> where T is the type of the POCO class generated for the typed list: TypedListNameRow.

To fetch the query, one has to enumerate the returned IQuerable<T>, as is always the case with Linq. The IQueryable<T> can be appended with joins, where calls and e.g. order by calls to further shape the query.

Example

var q = from x in metaData.GetOrderDetailsTLTypedList()
        where x.ProductName.StartsWith("A")
        select x;

Here a typed list with, among other entities, the Product entity, is fetched and filtered based on a field in the Typed List, 'ProductName'.

Wrapping a Plain SQL query in an IQueryable

When you want to specify a part of the Linq query as a Plain SQL fragment, you can use the FromSql method on the LinqMetaData class. Using this method, you can wrap a Plain SQL fragment, including parameters and values for the parameters, as an IQueryable<T>, which can then be used as a part of a Linq query.

Example:

using(var metaData = new LinqMetaData(adapter))
{
    var s = "SELECT * FROM Customer WHERE Country = @Country";
    IQueryable<Customer> q1 = metaData.FromSql<Customer>(s, new {Country="USA"});
    var q = from c in q1
            join o in metaData.Order on c.CustomerId equals o.CustomerId
            where o.EmployeeId == 2
            select new {c.CustomerId, o.OrderId};
    return q.ToList();
}

Here the plain sql query in s is wrapped in an IQueryable<Customer>. It uses one parameter, which uses the same parameter passing mechanism as the Plain SQL API. The Customer class is a POCO and not an entity class.

The requirements for the type specified with FromSql are: it has to have an empty constructor (so a constructor that doesn't take any arguments), and all properties used in the query have to be public. Any property of the specified type that doesn't take part in the projection has to be marked with the attribute LLBLGenProProjectionIgnore.

Type filtering and casting

LLBLGen Pro supports entity inheritance and this gives the requirement of being able to filter on types in a query and also to be able to cast an entity to a given entity type. To be able to filter on a type, LLBLGen Pro requires a type filter.

With Linq to LLBLGen Pro, it's easy to insert a type filter: by using the Queryable extension methods Cast or TypeOf, or by using the C#/VB.NET keywords as/As and is/Is.

It's recommended to use Cast<T> or TypeOf<T> over as. The following example shows why:

// Query 1
var q = from e in metaData.Employee
        let x = e as BoardMemberEntity
        where x.CompanyCarID==3
        select e;

// Query 2
var q = from e in metaData.Employee.Cast<BoardMemberEntity>()
        where e.CompanyCarID==3
        select e;

// Query 3
var q = from e in metaData.Employee.TypeOf<BoardMemberEntity>()
        where e.CompanyCarID==3
        select e;

' Query 1
Dim q = From e In metaData.Employee _
        Let x = e As BoardMemberEntity _
        Where x.CompanyCarID=3 _
        Select e

' Query 2
Dim q = From e In metaData.Employee.Cast(Of BoardMemberEntity)() _
        Where e.CompanyCarID=3 _
        Select e

' Query 3
Dim q = From e In metaData.Employee.TypeOf(Of BoardMemberEntity)()
        Where e.CompanyCarID=3 _
        Select e

Query 1 shows the usage of let to store a temporary query result in a variable. While this results in the usage of a typefilter in the final query being executed, it does have a design flaw: what if 'e' isn't a BoardMemberEntity? In normal code, it should throw an exception on the next line as 'x' is null / Nothing in that case.

In case of Linq to LLBLGen Pro, no exception is thrown, as the let expression is converted into a SQL query fragment, and there's no way to throw the exception as the SQL query has no notion of the inheritance mapping.

Query 2 and Query 3 are all doing the same thing as Query 1, but they're conceptually different: they first cast the employee to the right type (BoardMemberEntity) before working with that entity. One could argue that TypeOf is actually better over Cast, as Cast also should throw an exception if 'e' isn't casteable to BoardMemberEntity.

Usage of 'as/As' in filters and projections

The keyword as (As in VB.NET) can be used in filters but it's not supported in projections. For using as in filters, use the following rules of thumb:

  • (variable as type) operand expression results in type operand expression which always results in a typefilter.
  • (variable as type).Field operand expression results in a predicate where Field is seen as a field of the entity of type type.

Example queries of usage of 'as/As' in a filter (supported) and in projections (not supported):

// as in a filter, supported. Fetches all BoardMemberEntity instances
var q = from e in metaData.Employee
        where (e as BoardMemberEntity) != null
        select e;


// as in a projection. Not supported.
var q = from e in metaData.Employee
        select new { SomeField = e as BoardMemberEntity};

' As in a filter, supported. Fetches all BoardMemberEntity instances
Dim q = From e In metaData.Employee _
        Where Not (e As BoardMemberEntity) Is Nothing _
        Select e


' As in a projection. Not supported.
Dim q = From e In metaData.Employee _
        Select SomeField = e As BoardMemberEntity

Temporal table predicates (SQL Server 2016+ / DB2 v10+)

Two extension methods on IQueryable<T> are available to specify temporal table predicates:

  • ForSystemDate(Predicate[, param1[, param2]]). Predicate is a string, which is the predicate to emit, e.g. "AS OF". param1 and param2 are optional parameters of type DateTime, to be used with the predicate. You can specify just one temporal table predicate per target. Specifying more than one will make the last one be used.
  • ForBusinessDate(Predicate[, param1[, param2]]). Equal to ForSystemDate, but now it uses a BusinessDate directive, instead of a SystemDate directive. ForBusinessDate is supported only on DB2 v10+.

You can specify the ForSystemDate / ForBusinessDate extension methods on all elements exposed by LinqMetaData, which includes Poco Typed Views, except Table Valued Function calls. Of course specifying these methods on elements which have no versioning table assigned to them in the RDBMS will result in an error inside the database.

Examples

The following example queries the Employee entity set using the System Time with a BETWEEN predicate, specifying two dates. The database will automatically merge matching results from the history table as well as the active table into one resultset. 

var q = from e in metaData.Employee
                            .ForSystemTime("BETWEEN {0} AND {1}", 
                                            fromDate, toDate)
        where e.EmployeeId == 1
        select e;

Dim q = From e In metaData.Employee _
                            .ForSystemTime("BETWEEN {0} AND {1}", 
                                            fromDate, toDate)
        Where e.EmployeeId = 1 _
        Select e

The following example shows a query using the Business Time with an AS OF predicate. Business Time predicates are supported on DB2 only as SQL Server 2016 doesn't support Business Time versioning. 

var q = from p in metaData.PolicyBus.ForBusinessTime("AS OF {0}", 
                                new DateTime(2010, 4, 1))
        where p.Id==1414
        select p;

Dim q = From p in metaData.PolicyBus.ForBusinessTime("AS OF {0}", _
                                new DateTime(2010, 4, 1)) _
        Where p.Id = 1414 _ 
        Select p

Target / Index hints (SQL Server / MySQL)

To specify a hint on an entity / poco typed view fetched using Linq, you can use the extension method WithHint(hint). hint is a string, e.g. "NOLOCK" and is the actual hint to apply. The string specified has to contain the arguments for the hint, it's placed inside the query as-is. You can specify as many WithHint statements as needed, they're appended. For SQL Server, we support target hints using WithHint. For MySQL we support index hints.

Target hint example (SQL Server specific)

The following example shows the target hint support for SQL Server. Here a query using a join specifies for one join operand two hints which are applied to the target the element, in this case the "Customer" entity, is mapped on. 

var q = from c in metaData.Customer
                                .WithHint("NOLOCK")
                                .WithHint("FORCESEEK")
        join o in metaData.Order on c.CustomerId equals o.CustomerId 
        where o.EmployeeId > 4
        select c;

Dim q = From c In metaData.Customer _
                                .WithHint("NOLOCK") _
                                .WithHint("FORCESEEK") _
        Join o In metaData.Order On c.CustomerId Equals o.CustomerId _
        Where o.EmployeeId > 4 _
        Select c

Index hint example (MySQL specific)

The following example shows the specification of an index hint on the entity "Address" to specify the database to use that particular index in the query.

var q = from a in metaData.Address
                            .WithHint("USE INDEX (idx_fk_city_id)")
        where a.City.Contains("Stadt")
        select a;

Dim q = From a In metaData.Address _
                            .WithHint("USE INDEX (idx_fk_city_id)") _
        Where a.City.Contains("Stadt") _
        Select a

Join hint with secondary hint example (Google Cloud Specific)

You can specify join hints and secondary index hints with one element. The runtime will assign the join hint with the join operator and the other hints with the table name, as shown in the following example:

var metaData = new LinqMetaData(adapter);
var q = (from n in metaData.TypesTab
         join i in metaData.Notnulltab
                        .WithHint("FORCE_JOIN_ORDER=TRUE")
                        .WithHint("FORCE_INDEX=NOTNULLTABIDX") 
            on n.Id equals i.Id
         where n.Id >= 1000
         orderby n.Id ascending
         select n);

Query / Optimizer hints (SQL Server, MySQL, Oracle)

If, for some specific queries, you need to specify a query hint or optimizer hint in a Linq query, you can. Query hints and Optimizer hint support is available for SQL Server, MySQL and Oracle. For Oracle you can use the query hint system also to specify target hints, as they're specified at the same location as query hints in the SQL query.

Specifying hints with a query

To specify a hint with a query, the same method as for target hints is used, WithHint, however a directive argument has to be specified to describe the nature of the hint: WithHint(hintstring, HintType). By default the HintType is set to TargetHint. To specify a query hint, HintType.QueryHint should be specified with the WithHint call.

Limitations

The runtime doesn't check whether the hint is valid on the select statement it's specified with. This means that if a unioned query contains a query hint, it will be emitted with that unioned query's SQL. However on SQL Server this isn't allowed.

Important!

Query hints are not meant to be 'apply them and everything will be better'. In general optimizers do a great job optimizing the SQL query and you don't need to specify any hint to help it with its job. However in the case where the optimizer makes a suboptimal choice, you could opt for a query hint to make the query perform better. Do realize that a hint to help a query could also cause a query to perform less optimal later on as the dataset changes and the optimizer would have made a better choice than the hint allows it to do.

Additionally, a hint is a string, and hint text is included into the query as-is and therefore no user provided data has to be included to avoid SQL injection.

Example

var q = (from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId 
        where o.EmployeeId > 4
        select c)
        .WithHint("MERGE JOIN", HintType.QueryHint)
        .WithHint("FORCE ORDER", HintType.QueryHint);

SELECT DISTINCT [LPA_L1].[Address],
                [LPA_L1].[City],
                [LPA_L1].[CompanyName],
                [LPA_L1].[ContactName],
                [LPA_L1].[ContactTitle],
                [LPA_L1].[Country],
                [LPA_L1].[CustomerID] AS [CustomerId],
                [LPA_L1].[Fax],
                [LPA_L1].[Phone],
                [LPA_L1].[PostalCode],
                [LPA_L1].[Region]
FROM   ([Northwind].[dbo].[Customers] [LPA_L1]
        INNER JOIN [Northwind].[dbo].[Orders] [LPA_L2]
            ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])
WHERE  ((((([LPA_L2].[EmployeeID] > @p1 )))))
OPTION (FORCE ORDER, MERGE JOIN)
Important!

It's important to specify the .WithHint() directives at the outer query, as they're meant for the outer query only, not for e.g. a query joined in the From clause.

Queryable: Contains

Contains is the Queryable extension method which allows a developer to produce IN queries or more precisely: queries which filter on another set, be it a set of in-memory objects or a set formed by another Linq query. Linq to LLBLGen Pro has deep support for Contains in all possible situations where Contains can be used. Contains is also a method of the String class, which is converted to a LIKE filter.

The Contains examples below are focusing on Queryable.Contains. String extension methods which result in LIKE filters, the StartsWith/EndsWith and Contains methods, are discussed below in their own paragraph.

Below you'll find some examples of Contains queries. Linq to LLBLGen Pro also supports Contains calls on in-memory objects with multiple fields. These objects should have fields in common with the primary key of the entity filtered.

// Query 1, simple entity check in entity list
var q = from c in metaData.Customer
        where c.Orders.Where(o=>o.EmployeeId==3).Contains(order)
        select c;

// Query 2, operand is entity which is result of query
var q = from c in metaData.Customer
        where c.Orders.Contains(
                 (from o in metaData.Order 
                  where o.EmployeeId == 2 select o).First())
        select c;

// Query 3, operand and source are both queries.
var q = from c in metaData.Customer
        where c.Orders.Where(o => o.EmployeeId == 2).Contains(
                   (from o in metaData.Order 
                    where o.EmployeeId == 2 select o).First())
        select c;

// Query 4, constant compare with value from query. Yes this is different.
var q = from c in metaData.Customer
        where c.Orders.Where(o => o.EmployeeId > 3).Select(o => o.ShipVia).Contains(2)
        select c;

// Query 5, check if a constant tuple is in the result of a query
var q = from c in metaData.Customer
        where c.Orders.Select(oc => new { EID = oc.EmployeeId, CID = oc.CustomerId }).Contains(
                         new { EID = (int?)1, CID = "CHOPS" })
        select c;

// Query 6, as 5 but now compare with a tuple created with a query
var q = from c in metaData.Customer
        where c.Orders.Select(oc => new { EID = oc.EmployeeId, CID = oc.CustomerId }).Contains(
                      (from o in metaData.Order where o.CustomerId == "CHOPS" 
                       select new { EID = o.EmployeeId, CID = o.CustomerId }).First())
        select c;

// Query 7, checking if the value of a field in an entity is in a list of constants
List<string> countries = new List<string>() { "USA", "UK" };
var q = from c in metaData.Customer
        where countries.Contains(c.Country)
        select c;

// Query 8, as 7 but now with an IEnumerable
LinkedList<string> countries = new LinkedList<string>(new string[] { "USA", "UK"});
var q = from c in metaData.Customer
        where countries.Contains(c.Country)
        select c;

// Query 9, combination of 2 queries where the first is merged with the second and
// only the second is executed. (this is one of the reasons why you have to write 
// your own Funcletizer code.
var q1 = (from c in metaData.Customer
          select c.Country).Distinct();
var q2 = from c in metaData.Customer
         where q1.Contains(c.Country)
         select c;

// Query 10, as 7 but now with an array obtained from another array.
string[][] countries = new string[1][] { new string[] { "USA", "UK" } };
var q = from c in metaData.Customer
        where countries[0].Contains(c.Country)
        select c;

// Query 11, complex contains query with comparison of in-memory object list
List<Pair<string, string>> countryCities = new List<Pair<string, string>>();
countryCities.Add(new Pair<string, string>("USA", "Portland"));
countryCities.Add(new Pair<string, string>("Brazil", "Sao Paulo"));


// now fetch all customers which have a tuple of country/city in the list of countryCities.
var q = from c in metaData.Customer
        where countryCities.Contains(
                   (from c2 in metaData.Customer
                    where c2.CustomerId == c.CustomerId
                    select new Pair<string, string>() 
                         { Value1 = c2.Country, Value2 = c2.City }).First())
        select c;

' Query 1, simple entity check In entity list
Dim q = From c In metaData.Customer _
        Where c.Orders.Where(Function(o) o.EmployeeId.Value=3).Contains(order) _
        Select c

' Query 2, operand is entity which is result of query
Dim q = From c In metaData.Customer _
        Where c.Orders.Contains( _
                 (From o In metaData.Order _
                  Where o.EmployeeId.Value = 2 Select o).First()) _
        Select c

' Query 3, operand and source are both queries.
Dim q = From c In metaData.Customer _
        Where c.Orders.Where(Function(o) o.EmployeeId.Value = 2).Contains( _
                   (From o In metaData.Order _
                    Where o.EmployeeId = 2 Select o).First()) _
        Select c

' Query 4, constant compare with value From query. Yes this is different.
Dim q = From c In metaData.Customer _
        Where c.Orders.Where(Function(o) o.EmployeeId.Value > 3).Select(Function(o) o.ShipVia).Contains(2) _
        Select c

' Query 5, check if a constant tuple is In the result of a query
Dim q = From c In metaData.Customer _
        Where c.Orders.Select(Function(oc) New With {.EID = oc.EmployeeId, .CID = oc.CustomerId}).Contains( _
                         New With { .EID = CType(1, Nullable(Of integer)), .CID = "CHOPS" }) _
        Select c

' Query 6, as 5 but now compare with a tuple created with a query
Dim q = From c In metaData.Customer
        Where c.Orders.Select(Function(oc) New With { ,EID = oc.EmployeeId, .CID = oc.CustomerId }).Contains( _
                      (From o In metaData.Order Where o.CustomerId = "CHOPS" _
                       Select New With {.EID = o.EmployeeId, .CID = o.CustomerId }).First()) _
        Select c

' Query 7, checking if the value of a field In an entity is In a list of constants
Dim countries As New List(Of String)() 
countries.Add("USA")
countries.Add("UK")
Dim q = From c In metaData.Customer _
        Where countries.Contains(c.Country) _
        Select c

' Query 8, as 7 but now with an IEnumerable
Dim countryValues(2) As String
countryValues(0)="USA"
countryValues(1)="UK"
Dim countries As new LinkedList(Of String)(countryValues)
Dim q = From c In metaData.Customer _
        Where countries.Contains(c.Country) _
        Select c

' Query 9, combination of 2 queries where the first is merged with the second and
' only the second is executed. (this is one of the reasons why you have to write 
' your own Funcletizer code.
Dim q1 = (From c In metaData.Customer _
          Select c.Country).Distinct()
Dim q2 = From c In metaData.Customer _
         Where q1.Contains(c.Country) _
         Select c

' Query 10, as 7 but now with an array obtained from another array.
Dim countries(0)() As String
Dim countryValues(1) As String
countryValues(0)="USA"
countryValues(1)="UK"
countries(0)=countryValues
Dim q = From c In metaData.Customer _
        Where countries(0).Contains(c.Country) _
        Select c

' Query 11, complex Contains query with comparison of In-memory object list
Dim countryCities As New List(Of Pair(Of String, String))()
countryCities.Add(new Pair(Of String, String)("USA", "Portland"))
countryCities.Add(new Pair(Of String, String)("Brazil", "Sao Paulo"))

' now fetch all customers which have a tuple of country/city In the list of countryCities.
Dim q = From c In metaData.Customer _
        Where countryCities.Contains( _
                   (From c2 In metaData.Customer _
                    Where c2.CustomerId = c.CustomerId _
                    Select New Pair(Of String, String)() With _
                         { .Value1 = c2.Country, .Value2 = c2.City }).First()) _
        Select c

String: Contains, StartsWith and EndsWith

Calling Contains, StartsWith or EndsWith on a string-typed field or variable in a Linq query will result in the expression being converted to a LIKE filter. These three methods are different from the rest of the string methods which are mapped to database functions: Contains, StartsWith and EndsWith are converted to a FieldLikePredicate, if required, or if the expression can be evaluated in-memory first, it's evaluated before it's handled further (e.g. calling one of these three methods on a constant string or an in-memory variable).

The string passed to the methods doesn't have to be padded with LIKE wildcards, this is done by the Linq to LLBLGen Pro provider, based on the method called.

Paging through resultsets

To page through a resultset returned by a Linq query, one can use two different mechanisms: use TakePage(pageNumber, pageSize) or use Skip together with Take.

In contrast with the low-level querying API, the value passed to Skip and Take don't have to result in a valid page number and page size: it is possible with Linq to LLBLGen Pro to use .Skip(3) and then use .Take(5). It's also possible to use Skip without Take.

Be aware that although you can specify Skip and Take anywhere in the query, as well as TakePage, you should use the methods only to page the resultset of the query. This means a page action in the middle of a query, e.g. in one side of a join, isn't supported.

Below is an example of how to retrieve the 2nd page of 20 Customer entities.

var q = (from c in metaData.Customer orderby c.CustomerId select c).TakePage(2, 20);

Be aware of the fact that, if the resultset doesn't have a proper ordering specified (through an Order by clause), paging is useless due to the SQL definition that a set inside SQL has no particular ordering defined.

Linq to LLBLGen Pro doesn't add an ordering on all fields or on the PK field(s) if there's no ordering specified; you have to specify the ordering yourself. This is required because a paging query is executed again for each page requested and SQL by definition doesn't return a resultset in a given order, the order is undefined, unless an ordering using Order by is specified, which is unique.

If you order on a field which gives multiple rows with the same value for the field used to order on (e.g. Customer.Country, with multiple customers from the same country), the order of these rows with the same value is undefined so for each request of a page, the order of these rows could be random, depending on the optimization inside the RDBMS used, because the SQL standard dictates that a set (also the returned set) is in an unknown order (except when an ordering is specified).

Client side paging vs. server side paging

Paging in general happens on the server, however the LLBLGen Pro runtime framework switches to client-side paging at the datareader level if the paging query can't be generated without causing errors on the server. This happens if a DISTINCT violating construct is present in the SQL query, e.g. a type that's not to be used with DISTINCT or an order by on a field that's not in the projection. The datareader-level paging is efficient (it discards any rows before the page to read and stops reading data once the page to read is fully read) however can be slower than a server-side paging query.

To determine whether the framework switched to client-side paging / distinct filtering / row limiting, it will append to the SQL query output to the ORMQueryExecution tracer a string which signals this: "Requires client side paging", "Requires client side distinct filtering" and "Requires client side limiting", if the ORMQueryExecution tracer is set to level 4 (verbose). See ORM Support classes tracing for details on this tracer.

ElementAt / ElementAtOrDefault

ElementAt / ElementAtOrDefault is implemented in Linq to LLBLGen Pro as .TakePage(n, 1), where n is the value passed to ElementAt / ElementAtOrDefault. It is required that the resultset you call this method on (i.e. the query) is ordered with a proper order by clause.

WITH TIES support

For the databases PostgreSQL 13+, Oracle 12C+ and SQL Server 2005+ we support the WITH TIES SQL command, which is used when a resultset is limited to a specified set of rows. In Linq this is done using the .Take(n) IQueryable extension method. If there are duplicate rows at the end of the n rows, using just .Take(n) will make the database return the first n rows and which ones of the duplicates you'll get is undefined.

Using the extension method .WithTies() additionally to .Take(n), the generated SQL query will have the WITH TIES SQL command specified which will make the database return all duplicate rows at the end of the n rows. See the example below:

var metaData = new LinqMetaData(adapter);
var q = (from c in metaData.Customer
         orderby c.City ascending
         select c).Take(14).WithTies();
var results = q.ToList();

On Northwind this would return 14 rows without WithTies(), however in the query above it'll return 16 rows, including the 2 rows with the same City value as the last row.

Using a Context

The Linq provider supports using a Context instance (Adapter, SelfServicing). The LinqMetaData class has a property which accepts a Context, ContextToUse.

Setting this property to a Context instance will make all queries created with the LinqMetaData instance use the set Context. Resetting the property to null will make all queries created with that LinqMetaData instance after that reset action not use the Context previously set. This is illustrated in the example below:

// metaData is a LinqMetaData instance
metaData.ContextToUse = new Context();

// this query will use the context set
var q = from c in metaData.Customer
        where ...
        select ...

// resetting 
metaData.ContextToUse = null;

// this query won't use the context previously set, as the metaData's property
// is null.
var q = from o in metaData.Order
        ...

' metaData is a LinqMetaData instance
metaData.ContextToUse = New Context()

' this query will use the context set
Dim q = From c In metaData.Customer _
        Where ... _
        Select ...

' resetting 
metaData.ContextToUse = Nothing

' this query won't use the context previously set, as the metaData's property
' is null.
Dim q = From o In metaData.Order _
        ...

Excluding / Including fields

Linq to LLBLGen Pro supports the exclusion and inclusion of fields (Adapter, SelfServicing). This is done through the extension methods ExcludeFields and IncludeFields.

Using these methods, the developer can specify which fields to exclude in an entity fetch (ExcludeFields) or which fields to use (IncludeFields). The following example illustrates how to exclude two fields from an entity fetch.

// exclude Photo and Notes from employee fetch
var q = (from e in metaData.Employee
         select e).ExcludeFields(e=>e.Photo, e=>e.Notes);

' exclude Photo and Notes from employee fetch
Dim q = (From e In metaData.Employee _
         Select e).ExcludeFields(Function(e) e.Photo, Function(e) e.Notes)

To fetch these excluded fields later on into the entities returned by the query, you should use the regular mechanisms to do so, as explained in the documentation on Excluding and Including fields (linked above).

Query tagging

Linq has the ability to let you mark (tag) the SQL query or queries generated by the runtime with a tag. This tag is a string which is added as a comment before the actual query string. This way you can easily track back the SQL query on a database server with the actual calling code without a profiler / stacktrace. It's a low-impact way to make sure you can track down information at runtime in the future.

Requirements

As the tag is a string and added to the query as a comment, it can't contain */ and /* character sequences, as they're the comment markers in SQL. The string isn't checked for these characters by the runtime.

Usage

To mark / tag a query, use the MarkWithTag(string) extension method for IQueryable<T>. The runtime will then add the passed in string as a comment to the generated SQL, at the front. The MarkWithTag() method is available on any Linq query.

Prefetch paths, nested queries and query tagging

A prefetch path fetched through the query specified, or nested queries fetched through a query, all use the same tag as their source of origin is the same.

Caching, execution plans and tags

The tag is added (at the front) to the sql string in the DbCommand of an IRetrievalQuery right before execution. This avoids the SQL query not being usable for determining resultset caches. Execution plans can differ based on the added comment as the RDBMS likely will use a hash for the string to determine whether it's already known or a new query. This means that you should think ahead what to use for the tag string: an ever changing value might not be a good idea in this case.

It's also likely not a good idea to tag every query, but only the ones which you might expect problems with at runtime.

Creating tags based on call path

Creating tags based on the call origin might be good in some cases. You can use the CallerFilePath, CallerLineNumber and CallerMemberName attributes in .NET code to create a string with the values created by the compiler. See the .NET Reference documentation of these attributes for details.

Tracing and query tags

In the ORMQueryExecution tracer, which emits which queries were executed, the tag is appended to the output as well, below the parameters. This way queries which are logged through the trace framework can also be related to origins based on the used tag.

Important!

MS access doesn't have support for SQL comments, and using the query tag system with MS Access will lead to errors as the comment added to the query can't be parsed by ms access.

Example

The following code which tags the query with the string "Linq/EntityFetches/QueryTagTest_Scalar"

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var count = (from c in metaData.Customer
                 where c.Country == "UK"
                 select c).MarkWithTag("Linq/EntityFetches/QueryTagTest_Scalar")
                          .Count();
    // do something with 'count'
}

produces the following SQL

/* Linq/EntityFetches/QueryTagTest_Scalar */ SELECT TOP(@p2) COUNT(*) AS [LPAV_]
FROM   [Northwind].[dbo].[Customers] [LPLA_1]
WHERE  ((((([LPLA_1].[Country] = @p3)))))

Hierarchical sets

Besides prefetch paths, which offer the ability to fetch a graph of related entities, another mechanism is available to fetch hierarchies of objects: nested queries in the projection. A nested query is a query which produces its own objects and which is present inside a projection of another query. Below is an example of such a query.

var q = from c in metaData.Customer
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        };

Dim q = From c In metaData.Customer _
        Where c.Country="USA" _
        Select New With _
        { _
            .Name = c.ContactName, _
            .Orders = From o In metaData.Order _
                      Where o.CustomerId = c.CustomerId _
                      select o _
        }

The query above fetches a set of instances of an anonymous type with two properties: Name, which contains the ContactName of the customer and Orders, which contains the OrderEntity instances of the customer.

Linq to LLBLGen Pro fetches nested queries with 1 SQL query per nested query found and merges the parent and child queries together using an efficient mechanism, similar to the one used with prefetch paths. The above Linq query therefore results in two SQL queries being executed: one for the customer data and one for the order data.

If the number of parent elements (here: Customers) is below the ParameterisedPrefetchPathThreshold (see Prefetch Paths for more details (Adapter, SelfServicing)), for the child elements a parameterized IN clause is used, which is often more efficient than a subquery.

If an entity fetch is used as a nested query (in the query above the Orders are an entity fetch) the code will use a normal entity fetch. Specifying related entities to these entities in nested sets isn't possible using queries like the one above: use prefetch paths on the nested query to fetch related entities to the entities fetched in the nested query.

Hierarchical sets can only occur in projections. This means that you can't fetch related data like in the query above if the projection (the select clause) isn't the outer select. This means that if you wrap the above query in yet another select, the nested query won't work. This isn't necessary anyhow, so in general you won't run into this.

The nested query has to have a connection with the outer query, otherwise Linq to LLBLGen Pro can't find a proper parent element for a child element or set of child elements. In the query above, a where clause is used to tie the nested query with the outer query.

This can also be done using the specification of a related set, e.g. c.Orders. Specifying the related set will make Linq to LLBLGen Pro use the correlation relation, which is the relation between parent and child (in the above query: Customer - Order) to build the connection between parent (customer) and child (order). An example of the usage of a correlation relation is the query below, which is a modification of the query above.

var q = from c in metaData.Customer
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = c.Orders
        };

Dim q = From c In metaData.Customer _
        Where c.Country="USA" _
        Select New With _
        { _
            .Name = c.ContactName, _
            .Orders = c.Orders _
        }

In the query above, instead of specifying a full query, the specification of the related set (here the set of Orders) is enough to specify what should be fetched as the nested query: all Orders for a fetched customer.

Linq to LLBLGen Pro currently supports only normal parent.field==child.field where clauses for connecting nested query with outer query.

Calling an in-memory method in the projection

Linq to LLBLGen Pro allows you to call a method in-memory to post-process the results fetched from the database before they're actually returned. This is different from the Function Mapping feature which converts a .NET method into a DB construct: calling a method in-memory is a method call which isn't converted into SQL.

This means that the method call can only be inside the last projection (select) in the query: if it's not, it has to be convertable to SQL as it is part of the SQL query.

Below is an example of this mechanism. It uses a utility class which returns true or false if the customer is matching a rule inside the class.

/// Utility class which obtains value from a webservice
public class InMemoryCallExample
{
    public static int GetInterestRate(string customerId)
    {
         return MyService.GetInterestRate(customerId);
    }
}

// this class can now be used in the following query:
var q = from o in metaData.Order
        select new 
        {
            o.OrderId,
            InterestRate = InMemoryCallExample.GetInterestRate(o.CustomerId)
        };

''' Utility class which obtains value from a webservice
Public Class InMemoryCallExample
    Public Shared Function GetInterestRate(customerId As String) As Integer
        Return MyService.GetInterestRate(customerId)
    End Function
End Class

' this class can now be used in the following query:
Dim q = From o In metaData.Order _
        Select New With _
        {
            .OrderId = o.OrderId, _
            .InterestRate = InMemoryCallExample.GetInterestRate(o.CustomerId) _
        }

It should be noted that this blurs the lines between what's been executed in-memory and what's executed inside the database. This is similar with .NET methods and properties being mapped onto database constructs / functions.

Therefore if possible, you should document these mixes of in-memory elements together with query elements in situations where it's not absolutely clear what is executed where, to avoid misinterpretations, where the person reading the code assumes things are executed on the server, while they're actually executed in-memory and vice versa.

In-memory lambda expressions in projections

Besides using .NET method calls in-memory inside a projection, you can also use a Lambda expression. Often a Lambda expression is easier to formulate. In the following example, a Lambda expression is used which performs a SubString() call on the value passed in. The function is used inside the projection of the query by applying it to a string value from the resultset:

// function which applies substring call on input
Func<string, string> stringChopper = s=>s.Substring(0, 3);

// this function can now be used in a query:
var q = from c in metaData.Customer
        select stringChopper(c.CompanyName);


' function which applies substring call on input
Dim stringChopper As Func(Of String, String) = Function(s) s.Substring(0, 3)


' this function can now be used in a query:
Dim q = From c in metaData.Customer _
        Select stringChopper(c.CompanyName)

The same rules apply to this mechanism as the in-memory method call approach: only use in-memory Func instances (lambda expressions) in the outer projection of the query.