Generated code - Linq to LLBLGen Pro, getting started

Preface

As of v2.6, LLBLGen Pro ships with full Linq for .NET 3.5+. Full linq support means that any querying on LLBLGen Pro generated entities can be done through Linq constructs/queries, which makes it an alternative to LLBLGen Pro's own querying API with predicates, relations and the like. It's still recommended to read and learn about the LLBLGen Pro querying API elements as discussed in Filtering and Sorting for Adapter and SelfServicing (please consult the Table of Contents of this manual for all pages regarding filtering and sorting, prefetch paths, excluding fields etc.) so the terms used in this section are familiar to you. LLBLGen Pro's Linq support builds on the existing querying API with predicates, relations, prefetch paths and the like. Due to the design of Linq, some features are available to you through Linq which otherwise would require manual coding when the LLBLGen Pro query API elements would be used. If you want to utilize these features directly, you should use LLBLGen Pro's Linq support.

This section assumes you're familiar with what Linq is. When Linq is mentioned in this section, the language extensions to C# and VB.NET are meant, not Linq to Sql or Linq to Objects or other framework. To get yourself familiar with Linq we recommend to read the MSDN documentation for .NET 3.5 / VS.NET 2008 about Linq features in C# or VB.NET, or read a book like Linq in Action (Manning). We like to refer to our Linq support as Linq to LLBLGen Pro, which is in-line with the terminology Microsoft uses for the various Linq support flavors they've released.

We tried our best to implement support for as much Queryable extension methods and as much Linq constructs as possible. This documentation won't repeat every single extension method of Queryable, only the ones which require your attention or which aren't supported.

To get started with Linq to LLBLGen Pro, generate code for .NET 3.5 and for Adapter or SelfServicing. Your VS.NET project should reference the SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll as well as the generated code project(s) and the SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll. You can then develop Linq queries using your LLBLGen Pro generated entities inside your code. If you're using Adapter, you've got to pass a valid DataAccessAdapter instance to the Linq query before it's executed. More on this below.

Linq to LLBLGen Pro also offers a tracer, at two levels. For more information see Troubleshooting and Debugging.

Example queries
To make things easier for you, we've included most of our Linq unit tests with LLBLGen Pro v2.6. These tests are more targeted towards edge cases but still give you a good insight in what's possible and give you a head-start in using Linq to LLBLGen Pro. They can be found in the folder Sourcecode\LinqUnitTests. Overall there are almost 600 queries (300 for selfservicing 300 for adapter), which target three different databases (AdventureWorks, Northwind and an inheritance database). It might be that some queries give a different result on your instance of Northwind for example, this is because our Northwind database is slightly altered.

note Important:

Linq requires .NET 3.5. Linq to LLBLGen Pro produces queries which can rely on a feature called Derived tables, which means a separate query in the FROM clause of the SELECT statement. Not all databases supported by LLBLGen Pro support this feature: Firebird 1.x and SqlServer CE Desktop 3.1 or earlier don't support derived tables. It's not recommended to use Linq to LLBLGen Pro on these databases, use a version of the database which is newer (Firebird 2.x / SqlServer CE Desktop 3.5). Linq to LLBLGen Pro is only supported on the full version of .NET 3.5, not on the compact framework.

SqlServer CE Desktop 3.5 is supported, however a limitation in SqlServer CE Desktop 3.5 related to scalar queries in SELECT statements (SELECT (SELECT ...) As value, Foo FROM Bar.. ) could lead to exceptions at runtime when Linq is used together with SqlServer CE Desktop 3.5, so be careful with scalar queries on SqlServer CE Desktop 3.x.

LinqMetaData

Linq queries can be split up in two groups: 1) the queries which work on an IEnumerable<T> and 2) the queries which work on an IQueryable<T>. Group 1) will make the compiler (C# or VB.NET) produce code which calls delegates and will execute the query entirely in memory. The more popular name for this feature is Linq to Objects. The second group will make the compiler produce code which creates at runtime a tree of Expression instances, representing the entire query, in short an Expression tree. An Expression tree is not executable directly, it has to be interpreted to execute what is specified inside the Expression tree. This is what a Linq provider, like Linq to LLBLGen Pro, does: it accepts an Expression tree, translates it into elements it can understand, interprets these elements and produces an executable form of this query. In Linq to LLBLGen Pro's case, it will produce a set of query API elements which can be passed to for example FetchEntityCollection on the specified DataAccessAdapter instance, or a GetMulti method call on the collection to fill.

Linq queries are self-contained. This means that they're both specification and also the result of the query: the variable which represents the query can be enumerated to read the results of that same query, and if the query produces a scalar, the variable representing the query also is the scalar value. This requires that when writing the query in C# or VB.NET, the element which executes the query, i.e. Linq to LLBLGen Pro is also specified. This is done through LinqMetaData, a class present in the generated code's Linq folder (Adapter: Dbgeneric project).

To tell the compiler that the query should be built into an Expression tree and be consumed by a Linq provider, the core object we refer to as the source of the data has to be an IQueryable<T> object or produce these objects. This is the LinqMetaData class. As a Linq query is self-contained, it also has to know how to execute itself, as it gets executed when it gets enumerated. With SelfServicing this isn't a problem, SelfServicing code already knows how to read and write data, for Adapter this is a bit different: you need to pass the DataAccessAdapter instance to use to the LinqMetaData's constructor. You can also pass one later on, which is discussed below in the ILLBLGenProQuery section.

note Note:

LinqMetaData is in the generated code, in the yourrootnamespace.Linq namespace, where yourrootnamespace is the root namespace you specified when you generated code. For Adapter users it's in the generated database generic project. This requires you to add the following statement to the top of your code file in which you want to write a Linq query to use Linq to LLBLGen Pro:

  • C#
  • VB.NET
using yourrootnamespace.Linq;
Imports yourrootnamespace.Linq


Below shows the same query in both SelfServicing and Adapter. It defines a query to fetch all CustomerEntity instances from the USA.

   SelfServicing
  • C#
  • VB.NET
LinqMetaData metaData = new LinqMetaData();
var q = from c in metaData.Customer
        where c.Country=="USA"
        select c;
Dim metaData As New LinqMetaData()
Dim q = From c In metaData.Customer _
        Where c.Country="USA" _
        Select c

   Adapter
  • C#
  • VB.NET
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
            where c.Country=="USA"
            select c;

    // enumerate over q here so it gets executed
}
Using adapter As New DataAccessAdapter()
    Dim metaData As New LinqMetaData(adapter)
    Dim q = From c In metaData.Customer _
            Where c.Country="USA" _
            Select c

    ' Enumerate over q here so it gets executed
End Using

As you can see, the queries for SelfServicing and adapter are identical. The only difference is the usage of LinqMetaData: with SelfServicing, it doesn't need a DataAccessAdapter instance. Keep in mind that the query 'q' in the examples above isn't executed till it is enumerated or you convert it into a List for example. Scalar queries (like a query for the Count of all customers from the USA), are executed immediately however, as scalar queries can't be enumerated. This is a caveat of the Linq design by Microsoft. See the section about ILLBLGenProQuery below for details about how to execute a query in a different way than enumerating it.

SelfServicing: passing a Transaction instance
It can be that you want to execute a query inside a running transaction. With SelfServicing, it's necessary to add the elements to fetch to the running transaction to avoid deadlocks on for example SqlServer. With Linq, these elements aren't defined by you, all you do is define a query. To be able to execute this query inside a transaction, you can pass a Transaction instance to the LinqMetaData constructor:

Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "SSTest");
LinqMetaData metaData = new LinqMetaData(trans);
var q = from c in metaData.Customers select c;
Dim trans As New Transaction(IsolationLevel.ReadCommitted, "SSTest")
Dim metaData As New LinqMetaData(trans)
Dim q = From c In metaData.Customers Select c
Setting variables on the Linq provider.
As a Linq query is self-contained and can execute itself, it contains a Provider to make that happen. This Provider is accessible from the query (except when the query is a scalar query, as the query is executed immediately) and allows you to access the members of this provider, for example to set a DataAccessAdapter instance after the query has been constructed. This can be handy if you have written some generic code which produces Linq queries on entities which can be fetched from different databases, depending on which DataAccessAdapter is used. The example below shows you how to access the provider and how to set its members. 

// Adapter
// define the query
var q = from c in metaData.Customers select c;

// .. later set the adapter instance
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    ((LLBLGenProProvider2)((IQueryable)q).Provider).Adapter = adapter;
}
' Adapter
' define the query
Dim q = From c In metaData.Customers Select c

' .. later set the adapter instance
Using adapter As New DataAccessAdapter()
    CType(CType(q, IQueryable).Provider, LLBLGenProProvider2).Adapter = adapter
End Using

ILLBLGenProQuery

An interface is defined on the IQueryable<T> elements produced by LinqMetaData, ILLBLGenProQuery. This interface allows you to execute the query by calling the Execute method. The advantage of this is that you can get the query result in its native container, e.g. an entity collection. Another advantage is that to obtain a list of the results, the provider doesn't have to traverse the results in full, and copy over the results in a List: the returned results are already in the container they're initially stored in.

To use ILLBLGenProQuery.Execute and ILLBLGenProQuery.Execute<TResult>, the query created has to be an IQueryable query, so a query which results in one or more objects, not a scalar. The example below shows how to obtain the entity collection of the customers from the USA.

   SelfServicing
  • C#
  • VB.NET
var q = from c in metaData.Customer where c.Country=="Germany" select c;
CustomerCollection customers = ((ILLBLGenProQuery)q).Execute<CustomerCollection>();

Dim q = From c in metaData.Customer Where c.Country="Germany" Select c
Dim customers As CustomerCollection = CType(q, ILLBLGenProQuery).Execute(Of CustomerCollection)()

   Adapter
  • C#
  • VB.NET
var q = from c in metaData.Customer where c.Country=="Germany" select c;
EntityCollection<CustomerEntity> customers = 
	((ILLBLGenProQuery)q).Execute<EntityCollection<CustomerEntity>>();
Dim q = From c In metaData.Customer Where c.Country="Germany" Select c
Dim customers As EntityCollection(Of CustomerEntity) = _
	 CType(q, ILLBLGenProQuery).Execute(Of EntityCollection(Of CustomerEntity))()
LLBLGen Pro v2.6 documentation. ©2002-2008 Solutions Design