LinqConnect Documentation
In This Topic
    Compiled Queries
    In This Topic

    When executing a query, LinqConnect performs the following two main tasks:

    Generally, both translator and materializer are pretty fast. However, if a query is executed several times, there is no point in running it through these processors over and over. To shorten the time taken for translating and materializing, LinqConnect provides compiled queries. A compiled query is an object that keeps a prepared SQL statement and a delegate to a materializing function. The first one is to be executed at the server to get rows related to the query, the second one transforms a result set into a sequence of entity objects. In other words, compiled query is the ready-to-use result of all hard work the LinqConnect runtime does to link the object and relational sides of the application.

    Query workflow

    An important note on this is that the query result is not cached. I.e., if the result set has changed after the first query execution, you will nevertheless get the 'fresh' entity collection when executing the same query again. Indeed, though the SQL command is the same, the server will return the proper (i.e., up-to-date) result set; and the materializing function, when being applied to those result set, will return up-to-date entities.

    There are two ways of using compiled queries: explicit and implicit. The first one is compiling your most common queries manually via the special CompiledQuery class, and then using the compilation results whenever you need. The second way is to create a cache, where compiled versions of all executed queries are placed, and to get those compiled versions whenever the user query coincides with one already executed before (LinqConnect does this automatically by default, so you don't need to do anything specific to use this way). Below we describe both ways in more details.

    Using Compiled Query Cache

    The query caching functionality is provided by the CompiledQueryCache class. This class holds a number of static caches, one per registered DataContext type. 'Registered' means that CompiledQueryCache was informed that this particular DataContext descendant should be involved into query caching (otherwise, caching is not enabled for this DataContext type).

    To register the YourDataContext type for query caching, you need only to invoke the corresponding method of the CompiledQueryCache static interface, e.g.,


    C#csharpCopy Code
    CompiledQueryCache crmDemoCache =
    CompiledQueryCache.RegisterDataContext(typeof(CrmDemoDataContext));
    Visual BasicCopy Code
    Dim crmDemoCache As CompiledQueryCache = _
    CompiledQueryCache.RegisterDataContext(GetType(CrmDemoDataContext))

    If you use either of default code generation templates, there is no necessity in registering your context, as the template generates the code that will construct a static field of your DataContext class, and this field points to the cache corresponding to YourDataContext. For example, the mentioned code for the data context generated after the CRM Demo sample database is


    C#csharpCopy Code
    public partial class CrmDemoDataContext : Devart.Data.Linq.DataContext
{
    public static CompiledQueryCache compiledQueryCache = 
        CompiledQueryCache.RegisterDataContext(typeof(CrmDemoDataContext));
    Visual BasicCopy Code
    Public Partial Class CrmDemoDataContext
    Inherits Devart.Data.Linq.DataContext
 
    Public Shared compiledQueryCache As CompiledQueryCache = _
        CompiledQueryCache.RegisterDataContext(GetType(CrmDemoDataContext))

    After registering YourDataContext, all queries executed on all YourDataContext instances will be added (more precisely, their compiled versions will be added) to the corresponding cache. Thus, each time you will re-execute a query, its compiled version will be instantly got from the cache, avoiding translation and preparation of the materialization delegate.

    The cache has a limit of stored queries (it is set by the MaxSize property), so that multiple queries from multiple DataContext instances don't consume too much memory after being compiled. When the number of stored queries get to this limit, the query that was executed the longest time ago is removed from the cache; this way, the most popular queries may remain in the cache forever.

    To allow manual control on compiled query caches, the CompiledQueryCache class provides static methods to get the cache for a particular DataContext type (GetCache), register/unregister this type for caching and check if it is registered (RegisterDataContext, UnRegisterDataContext, IsDataContextRegistered). Instance methods of CompiledQueryCache allow accessing and clearing the content of a cache for a particular DataContext type (see Contains, TryGet, Remove, Clear). For the details on these methods, refer to the CompiledQueryCache topic.

    What Compiled Query Cache Actually Caches

    In many situations it would be insufficient to save queries 'as is'. For example, it would almost negate the positive effect of caching for queries like "get all orders and these orders' shipping companies for the current product". Indeed, such a query may be used many times, but it has a variable parameter, the 'current product' ID, so for each new ID another compiled query object would be added to the cache.

    To overcome this problem, the compiled query cache saves only query's 'frame', i.e., the statements used in the query except the parameter values. For example, the following queries differ only on the minimal amount an order may be discounted on:


    C#csharpCopy Code
    var discountedOrders = from o in context.Orders
                       where o.Discount > 10
                       select o;
 
var ordersWithLargeDiscount = from o in context.Orders
                              where o.Discount > 20
                              select o;
 
discountedOrders.ToList();
ordersWithLargeDiscount.ToList();
    Visual BasicCopy Code
    Dim discountedOrders = From o In context.Orders _
                       Where o.Discount > 10 _
                       Select o
 
Dim ordersWithLargeDiscount = From o In context.Orders _
                              Where o.Discount > 20 _
                              Select o
 
discountedOrders.ToList()
ordersWithLargeDiscount.ToList()

    When the first query is enumerated, it will be compiled with an empty parameter corresponding to the literal the order's discount is compared with. Thus, it will suit well for the second query, except that other parameter value should be used. I.e., in this sample 'ordersWithLargeDiscount' is got from the cache instead of being compiled.

    Saving queries without parameter values is awesome, but the LinqConnect runtime provides even more flexibility. Namely, materialization delegates are actually saved apart of compiled queries. To better understand what this means, let us consider two queries that get Order entities by different criteria:


    C#csharpCopy Code
    var allOrders = from o in context.Orders
                select o;
 
var largeOrders = from o in context.Orders
                  join d in context.Orderdetails on o.OrderID equals d.OrderID
                  where d.Quantity > 50
                  select o;
 
allOrders.ToList();
largeOrders.ToList();
    Visual BasicCopy Code
    Dim allOrders = From o In context.Orders
                Select o
 
Dim largeOrders =
    From o In context.Orders Join d In context.Orderdetails On o.OrderID Equals d.OrderID
    Where d.Quantity > 50
    Select o
 
allOrders.ToList()
largeOrders.ToList()

    The first query just gets all orders available in the database, the second one gets only those including multiple units of the same product. Since both queries operate on result sets of the same type, they can use a single materialization function to create entities from those result sets. And this is exactly what the LinqConnect runtime does: materialization functions are saved in a special cache separated from the compiled query cache.

    When the first query is executed, a materialization delegate and a compiled query are prepared for it. When enumerating the 'largeOrders' query, the first compiled query cannot be used, as it has no restrictions on the orders being returned. However, the materialization function may be the same, so it is taken from the cache instead of being prepared again.

    The same concerns anonymous types: when two anonymous types used as query results have the same sets of fields, the corresponding queries share the materialization function. For example:


    C#csharpCopy Code
    var productNames = from p in context.Products
                   select new { ID = p.ProductID, Name = p.ProductName };
 
var companyNames = from c in context.Companies
                   select new { ID = c.CompanyID, Name = c.CompanyName };
 
productNames.ToList();
companyNames.ToList();
    Visual BasicCopy Code
    Dim productNames = From p In context.Products Select New With { _
   Key .ID = p.ProductID, _
   Key .Name = p.ProductName _
}
 
Dim companyNames = From c In context.Companies Select New With { _
   Key .ID = c.CompanyID, _
   Key .Name = c.CompanyName _
}
 
productNames.ToList()
companyNames.ToList()

    Though these queries work with different entity types, they both return collections of anonymous types having only ID and Name public properties. This allows to use the same materialization function for the compiled queries corresponding to 'productNames' and 'companyNames', and this is exactly what happens.

    Manually compiling queries

    In certain situations, it may be suitable to compile queries manually (for example, to prepare some important but heavy requests on the application initialization, so that the user don't need to wait when these requests are executed for the first time). To compile a query explicitly, you pass it to the static Compile method of the CompiledQuery class; as a result, you get a delegate to which you can then pass parameters. For example, the following code gets all companies from a particular city:


    C#csharpCopy Code
    var getCompaniesByCity =
    CompiledQuery.Compile<CrmDemoDataContext, string, IQueryable<Company>>(
    (crmContext, city) =>
        crmContext.Companies.Where(c => c.City == city)
    );
 
var munichCompanies =
    getCompaniesByCity(new CrmDemoDataContext(), "Munich")
    .ToList();
    Visual BasicCopy Code
    Dim getCompaniesByCity = _
    CompiledQuery.Compile(Of CrmDemoDataContext, String, IQueryable(Of Company)) _
        (Function(crmContext, city) _
             crmContext.Companies.Where(Function(c) c.City = city))
 
Dim munichCompanies = getCompaniesByCity(New CrmDemoDataContext(), "Munich").ToList()

    The Compile method is generic and has two mandatory generic type arguments: the first is the type of the DataContext you are going to execute the query via, and the latter one is the result type of the query. For example, we use CrmDemoDataContext generated for the CRM Demo sample database and suppose that the query returns a sequence of Company entities.

    The other generic arguments passed between these two are optional and specify the types of the actual query parameters that would be passed to the result delegate. In our sample, the only optional generic type argument is string since we will pass city names to the compiled query.

    The argument of the Compile method is the lambda expression, getting the query based on the context instance and optional parameters' values. Thus, the result of this method is a delegate, which does just what was said above: gets the query based on the context instance and several parameters. Note that the result is indeed a query, i.e., it is deferred and should be enumerated to run a SQL command on the server and get the materialized entities.

    The Compile method overloads take up to three generic arguments (besides the DataContext and result types). To pass more than that, you can use your own structure/class or pass an object array. Here is the sample for the latter approach:


    C#csharpCopy Code
    var getCompanyByAddress = CompiledQuery.Compile<
    CrmDemoDataContext, object[], IQueryable<Company>>(
        (context, address) =>
            from emp in context.Companies
            where emp.Address == (string)address[0] &&
                emp.City == (string)address[1] &&
                emp.Country == (string)address[2]
            select emp);
 
var rgsConsulting =
    getCompanyByAddress(
        new CrmDemoDataContext(),
        new object[] { "Warngauer Str.,15", "Munich", "Germany" })
    .Single();
    Visual BasicCopy Code
    Dim getCompanyByAddress = _
    CompiledQuery.Compile( _
        Of CrmDemoDataContext, Object(), IQueryable(Of Company) _
        ) _
    (Function(context, address) _
        From emp In context.Companies _
        Where emp.Address = DirectCast(address(0), String) _
            AndAlso emp.City = DirectCast(address(1), String) _
            AndAlso emp.Country = DirectCast(address(2), String) _
        Select emp)
 
Dim rgsConsulting = getCompanyByAddress( _
    New CrmDemoDataContext(), _
    New Object() {"Warngauer Str.,15", "Munich", "Germany"}) _
    .Single()

    Note that the array members should be explicitly cast to their actual type in the query.

    As for the other way, you can declare your own structure:


    C#csharpCopy Code
    struct FullAddress
{
 
    public string Address { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}
    Visual BasicCopy Code
    Structure FullAddress
 
    Public Property Address() As String
        Get
            Return m_Address
        End Get
        Set(value As String)
            m_Address = Value
        End Set
    End Property
    Private m_Address As String
    Public Property City() As String
        Get
            Return m_City
        End Get
        Set(value As String)
            m_City = Value
        End Set
    End Property
    Private m_City As String
    Public Property Country() As String
        Get
            Return m_Country
        End Get
        Set(value As String)
            m_Country = Value
        End Set
    End Property
    Private m_Country As String
End Structure

    and pass it as the compiled query parameter:


    C#csharpCopy Code
    var getCompanyByAddress = CompiledQuery.Compile<
    CrmDemoDataContext, FullAddress, IQueryable<Company>>(
    (context, address) =>
        from emp in context.Companies
        where emp.Address == address.Address &&
            emp.City == address.City &&
            emp.Country == address.Country
        select emp);
 
var rgsConsulting =
    getCompanyByAddress(
        new CrmDemoDataContext(),
        new FullAddress()
        {
            Address = "Warngauer Str.,15",
            City = "Munich",
            Country = "Germany"
        })
    .Single();
    Visual BasicCopy Code
    Dim getCompanyByAddress = CompiledQuery.Compile( _
    Of CrmDemoDataContext, FullAddress, IQueryable(Of Company))( _
    Function(context, address) _
        From emp In context.Companies _
        Where emp.Address = address.Address _
            AndAlso emp.City = address.City _
            AndAlso emp.Country = address.Country _
        Select emp)
 
 
Dim rgsConsulting = _
    getCompanyByAddress(New CrmDemoDataContext(), New FullAddress() With { _
        .Address = "Warngauer Str.,15", _
        .City = "Munich", _
        .Country = "Germany" _
    }).[Single]()

     

     

    © 2010 - 2024 Devart. All Rights Reserved.

    [email protected]