BLToolkit Linq-provider

Introduction 
Getting Started

Connecting provider database 
Creating a data model

Ideology and programming model 
Standard Linq queries

The first acquaintance 
Query two tables

Implementation of the standard features

Association
Inheritance
Standard features .NET Framework

Additional features BLToolkit

Select 
DML operations 
SQL Function

Expanding BLToolkit

SQL function 
substitution methods 
Reuse expressions

Performance

Performance issues in Linq 
CompiledQuery

Conclusion 

Demonstration Project

Introduction

Today Linq does not need a special introduction and recommendations. This technology every day gets more and more spread and still wins more recognition of the developers. BLToolkit just did not go away and offers a Linq-provider to access the databases. The purpose of this document is to provide the right information to support Linq BLToolkit.

Getting Started

Connection database provider

BLToolkit supports the following list of databases:

• DB2
• Informix
• Firebird
• Oracle
• Microsoft Access
• MS SQL 2005
• MS SQL 2008
• MySql
• PostgreSQL
• SQLCE
• SQLite
• Sybase ASE

By default, the library supports only three providers: MS SQL 2008, MS SQL 2005 and Microsoft Access. This is due to the fact that these providers are included in the standard delivery of .NET, and for the rest of the performance you need to install additional components from the database vendors.

To add support for the provider are not included in the standard list, follow these steps:

1. Add references to the library from the producers of databases in your project.
2. Add the appropriate file with the implementation of the project provider. The source code for all providers located in the directory Data \ DataProvider project BLToolkit or in the same directory as the archive http://bltoolkit.net/download/bltoolkit_bin.zip, if you are using a compiled version BLToolkit.
3. Register BLToolkit provider in your project in the following ways:

using System;using NUnit.Framework;using BLToolkit.Data;using BLToolkit.Data.DataProvider;namespace HowTo.Data.DataProvider{    [TestFixture]    public class AddDataProvider    {        const string connectionString =             "Data Source=DBHost;Port=5000;Database=BLToolkitData;Uid=sa";        [Test]        public void Test()        {            // 3rd party data provider registration.            //            DbManager.AddDataProvider(new SybaseDataProvider());            // It can be configured by App.config.            // We use this way for the demo purpose only.            //            DbManager.AddConnectionString(                "Sybase",          // Provider name                "Sybase",          // Configuration                connectionString); // Connection string            using (var db = new DbManager("Sybase"))            {            }        }    }}

Or, using the configuration file:

<?xml version="1.0"?><configuration>    <configSections>        <section name="bltoolkit" type="BLToolkit.Configuration.BLToolkitSection, BLToolkit.3"/>    </ configSections>    <bltoolkit>        <dataProviders>            <add type="BLToolkit.Data.DataProvider.SybaseDataProvider, MyProjectName" />        </ dataProviders>    </ bltoolkit>    <connectionstring>        <add name="Sybase" connectionString="Data Source=DBHost;Port=5000;Database=BLToolkitData;Uid=sa" providerName="Sybase.Data.AseClient"/>    </ connectionstring></configuration>

Details of the configuration BLToolkit see. Http://bltoolkit.net/doc/Data/index.htm.

Creating a data model

To construct a Linq query, we need a data model as a set of classes that describe the structure of the database tables and relations between them. Building such a model can be done in the following ways:

• Using a special utility generating classes for BLToolkit by Andrei Smirnov.
• Using template MyGeneration by Andy77.
• With the data model designer Linq to SQL.
• By writing data model classes manually.

We will use examples to the standard guinea pig from Microsoft - database Northwind. Script create this database and data model for it can be found in the sample project in this article. Below is given a seed for one of the classes of the model:

[TableName("Categories")]public class Category{    [PrimaryKey, Identity] public int    CategoryID;    [NotNull]              public string CategoryName;                           public string Description;                           public Binary Picture;    [Association(ThisKey="CategoryID", OtherKey="CategoryID")]    public List<Product> Products;}

In addition to classes, we need a data model class, combining all the table model.

class NorthwindDB : DbManager{    public NorthwindDB() : base("Northwind")    {    }    public Table<Northwind.Category> Category { get { return GetTable<Northwind.Category>(); } }    // ...}

The existence of such a class is not a requirement, you can use our model by calling GetTable in place, but the presence of a class NorthwindDB generally make our code cleaner and easier to read.

In the future, we will slightly alter the description of our model in order to clarify some aspects of the work BLToolkit, but now it is enough to get started.

Ideology and programming model

BLToolkit ORM is not in the classic sense of the definition. Classic ORM solve two problems are essentially provided in one package: Data Mapping and Entity Services. The first task - it is just something that should be dealt with initially ORM system - shifting data from the database into objects and back. Second, Entity Services - a set of management services object model such as: ensuring referential integrity, object caching, change tracking, validation objects, etc. Recently, such ORM was called Heavy ORM (heavy ORM), and the tool provides only Data Mapping - Lightweight ORM (lightweight ORM).BLToolkit the second category. Therefore, you will not see BLToolkit:

• automatic recovery operations nested lists of objects and lazy loading;
• automatic caching of objects;
• data context (like Linq to SQL DataContext), which tracks the changes and referential integrity has been read from the database objects;
• SubmitChanges operation for the data context.

Instead, you can access:

• operations DML (Insert / Update / Delete) in a form in which they are present in SQL;
• the approximate maximum to SQL programming model, including including SELECT, the data source (for example, to generate a call SELECT CURRENT_TIMESTAMP);
• the ability to describe the data model using only interfaces;
• mechanisms for reusing expressions and subqueries.

In other words, BLToolkit will not hide from you try to work with the database of the application object model, but on the contrary, provide the most complete and natural means to work with your database.

Standard Linq queries

The first acquaintance

• Our first test will be unremarkable. Here we just take the Employees table and perform her request.

static void FirstTest(){    using (var db = new NorthwindDB())    {        var query = db.Employee;        foreach (var employee in query)        {            Console.WriteLine("{0} {1}", employee.EmployeeID, employee.FirstName);        }    }}

What could be simpler? However, let's look at what we have under the microscope. We set a breakpoint inside the loop and look at the contents of the variable query and db in the debugger.

First of all we are interested in the property SqlText variable query. Open Text visualizer and see the contents of SqlText.

This query text generated BLToolkit. The first line commented out the current line configuration data provider name and name of the SQL provider.

But, unfortunately, the property SqlText available only if we work with variable IQueriable <T> as in the previous case. It is not always possible, in the following example query returns us to a value of type int.

static void CountTest(){    using (var db = new NorthwindDB())    {        int count = db.Employee.Count();        Console.WriteLine(count);    }}

In this case, we can vospolzovatesya property db.LastQuery, which contains the text of the last of a database query.

And the last way to get all the same information, but without looking at the variables of objects - is to enable tracing DbManager.

static void Main(){    DbManager.TraceSwitch = new TraceSwitch("DbManager", "DbManager trace switch", "Info");    FirstTest();    CountTest();}

Naturally, this mode is only available when using the debug version BLToolkit.

The following example, we need more for the order to smoothly move on to more complex queries.

static void SingleTableTest(){    using (var db = new NorthwindDB())    {        var query =            from e in db.Employee            where e.EmployeeID > 5            orderby e.LastName, e.FirstName            select e;        foreach (var employee in query)        {            Console.WriteLine("{0} {1}, {2}", employee.EmployeeID, employee.LastName, employee.FirstName);        }    }}

NOTE

Next article provides methods will not completely, but only in part related to the demonstration.

Generated SQL:

SELECT    [And]. [EmployeeID]    [e].[LastName],    [e].[FirstName],    [e].[Title],    [And]. [TitleOfCourtesy]    [e].[BirthDate],    [And]. [HireDate]    [e].[Address],    [e].[City],    [e].[Region],    [And]. [PostalCode]    [e].[Country],    [E]. [HomePhone],    [e].[Extension],    [e].[Notes],    [And]. [ReportsTo]    [And]. [PhotoPath]FROM    [Employees] [e]WHERE    [e].[EmployeeID] > 5ORDER BY    [e].[LastName],    [e].[FirstName]

Query two tables

The following are standard requests to the two (or more) tables. They will be needed in the future to discuss other possibilities BLToolkit.

Select Many

"Old style 'Join.

from c in db.Categoryfrom p in db.Productwhere p.CategoryID == c.CategoryIDselect new{    c.CategoryName,    p.ProductNameʱ??

SQL:

SELECT    [c].[CategoryName],    [T1]. [ProductName]FROM    [Categories] [c], [Products] [t1]WHERE    [t1].[CategoryID] = [c].[CategoryID]

Inner Join

"New" Join.

from p in db.Productjoin c in db.Category on p.CategoryID equals c.CategoryIDselect new{    c.CategoryName,    p.ProductName};

SQL:

SELECT    [t1].[CategoryName],    [P]. [ProductName]FROM    [Products] [p]        INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

Left Join

And finally, here is a plain Left Join.

from p in db.Productjoin c in db.Category on p.CategoryID equals c.CategoryID into gfrom c in g.DefaultIfEmpty()select new{    c.CategoryName,    p.ProductName}

SQL:

SELECT    [t1].[CategoryName],    [P]. [ProductName]FROM    [Products] [p]        LEFT JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

Implementation of the standard features

Association

Associations are called relationships between entities. Association are set by the attributes and greatly simplify queries due to the fact that every time eliminates the need to specify the relationship between binding tables.

Below is a table Product and its association.

[TableName("Products")]public abstract class Product{    [PrimaryKey, Identity] public int      ProductID;    [NotNull]               public  string    ProductName;                            public  int ? SupplierID;                            public  int ? CategoryID;                            public  string    QuantityPerUnit;                            public  decimal ? UnitPrice,                            public  short ? UnitsInStock;                            public  short ? UnitsOnOrder;                            public  short ? ReorderLevel;                            public  bool      Discontinued;    [Association(ThisKey="ProductID",  OtherKey="ProductID")]    public List<OrderDetail> OrderDetails;    [Association(ThisKey="CategoryID", OtherKey="CategoryID", CanBeNull=false)]    public Category Category;    [Association(ThisKey="SupplierID", OtherKey="SupplierID", CanBeNull=false)]    public Supplier Supplier;}

What you need to know about associations?

• Member of the class to which the attribute is applied Association may have a list type, or be a reference to the object. Payroll type is one to many relationship. Ie in this case one Product and manyOrder Details. Just a reference to an object is one-to-one. Either a lot to one that for us it does not matter, because on SQL generation is not affected.
• Feedback is given by a pair of very attribute properties ThisKey / OtherKey. Property ThisKey comma separated fields of the entity in which the association is declared. Property OtherKey - relevant fields related entity. If the field names and the nature of the original database tables differ in these properties indicate the names of the fields being.
• Property CanBeNull points BLToolkit what connection generate: Inner Join or Left Join. Be careful with this property. Left Join is not the case can very significantly improve query execution time.Besides BLToolkit can perform additional optimizations for one-to-one, and remove extra links from the request if it Inner Join. In general, the use of this attribute rule is: For one-to-many relationships CanBeNull may be equal to false only if the master recording may not appear in the database with no child bath (e.g., Order -> OrderDetails); for one-to-one relationships oftenCanBeNull to be false, but it is necessary to specify explicitly.
• The members of the class to which the association applied not filled in automatically when reading an object from the database entirely.

Now let's see what we've got:

from p in db.Productselect new{    p.Category.CategoryName,    p.ProductName}

An explicit connection with Table Category disappeared, but generated SQL remains the same:

SELECT    [t1].[CategoryName],    [P]. [ProductName]FROM    [Products] [p]        INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

Let's try to change the association Category property value CanBeNull to true, and look at the result:

SELECT    [t1].[CategoryName],    [P]. [ProductName]FROM    [Products] [p]        LEFT JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

You guessed it, now we turned Left Join.

Use the list-association may be, for example, as follows:

from p in db.Productselect new{    p.OrderDetails.Count,    p.ProductName}

SQL:

SELECT    (        SELECT            Count(*)        FROM            [Order Details] [t1]        WHERE            [p].[ProductID] = [t1].[ProductID]    ) as [c1],    [P]. [ProductName]FROM    [Products] [p]

As mentioned above related entities are not filled automatically when you create an object, but you can do it manually:

from o in db.Orderselect new Northwind.Order{    OrderID = o.OrderID,    Customer = o.Customer};

SQL:

SELECT    [o]. [OrderID]    [t1].[CustomerID],    [T1]. [CompanyName]    [t1].[ContactName],    [t1].[ContactTitle],    [t1].[Address],    [t1].[City],    [t1].[Region],    [t1].[PostalCode],    [t1].[Country],    [t1].[Phone],    [t1].[Fax]FROM    [Orders] [o]        INNER JOIN [Customers] [t1] ON [o].[CustomerID] = [t1].[CustomerID]

Associations are a very powerful tool, makes it easy to write the most complex queries. Let's try to write a query with multi-level associations:

from o in db.OrderDetailselect new{    o.Product.ProductName,    o.Order.OrderID,    o.Order.Employee.ReportsToEmployee.Region}

SQL:

SELECT    [T1]. [ProductName]    [o]. [OrderID]    [t2].[Region]FROM    [Order Details] [o]        INNER JOIN [Products] [t1] ON [o].[ProductID] = [t1].[ProductID]        INNER JOIN [Orders] [t4]            LEFT JOIN [Employees] [t3]                LEFT JOIN [Employees] [t2] ON [t3].[ReportsTo] = [t2].[EmployeeID]            ON [t4].[EmployeeID] = [t3].[EmployeeID]        ON [o].[OrderID] = [t4].[OrderID]

Note that we have used for the different tables of different types of Join. This is the result of application parameter CanBeNull.

Another interesting point is the use of the field to Select OrderID in the table OrderDetails, although in the Linq query uses a field in the table Orders. It's just that most optimization, which performs BLToolkit to retract unnecessary links. Let's change our example:

from o in db.OrderDetailselect new{    o.Product.ProductName,    o.Order.OrderID,    //o.Order.Employee.ReportsToEmployee.Region};

SQL:

SELECT    [T1]. [ProductName]    [o]. [OrderID]FROM    [Order Details] [o]        INNER JOIN [Products] [t1] ON [o].[ProductID] = [t1].[ProductID]

Since the table Order to access the Employee we no longer need and use OrderID from OrderDetails does not change the logic of the request, the connection with the Order, too, disappeared.

In addition, the same type of associative objects of different entities can be compared:

from o in db.Orderfrom t in db.EmployeeTerritorywhere o.Employee == t.Employeeselect new{    o.OrderID,    o.EmployeeID,    t.TerritoryID}

SQL:

SELECT    [o]. [OrderID]    [o]. [EmployeeID]    [t1].[TerritoryID]FROM    [Orders] [o], [EmployeeTerritories] [t1]WHERE    [o].[EmployeeID] = [t1].[EmployeeID]

And use for grouping:

from p in db.Productgroup p by p.Category into gwhere g.Count() == 12select g.Key.CategoryName;

SQL:

SELECT    [t1].[CategoryName]FROM    [Products] [p]        INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]GROUP BY    [t1].[CategoryID],    [t1].[CategoryName]HAVING    Count(*) = 12

For comparison and grouping of objects BLToolkit uses primary key information object. If the primary key is not specified, BLToolkit generates code that compares all of the object with each other. This is, firstly, not effective, and, secondly, is not always possible, because not all types in SQL can be compared.

As an experiment, let's try to remove from the Employee entity primary key and run the previous query again. The resulting SQL:

SELECT    [o]. [OrderID]    [o]. [EmployeeID]    [t3].[TerritoryID]FROM    [Orders] [o]        LEFT JOIN [Employees] [t1] ON [o].[EmployeeID] = [t1].[EmployeeID],    [EmployeeTerritories] [t3]        LEFT JOIN [Employees] [t2] ON [t3].[EmployeeID] = [t2].[EmployeeID]WHERE    [o].[EmployeeID] = [t3].[EmployeeID] AND    [t1].[LastName] = [t2].[LastName] AND    [t1].[FirstName] = [t2].[FirstName] AND    [t1].[Title] = [t2].[Title] AND    [t1].[TitleOfCourtesy] = [t2].[TitleOfCourtesy] AND    ([t1].[BirthDate] IS NULL AND [t2].[BirthDate] IS NULL OR [t1].[BirthDate] IS NOT NULL AND [t2].[BirthDate] IS NOT NULL AND [t1].[BirthDate] = [t2].[BirthDate]) AND    ([t1].[HireDate] IS NULL AND [t2].[HireDate] IS NULL OR [t1].[HireDate] IS NOT NULL AND [t2].[HireDate] IS NOT NULL AND [t1].[HireDate] = [t2].[HireDate]) AND    [t1].[Address] = [t2].[Address] AND    [t1].[City] = [t2].[City] AND    [t1].[Region] = [t2].[Region] AND    [t1].[PostalCode] = [t2].[PostalCode] AND    [t1].[Country] = [t2].[Country] AND    [t1].[HomePhone] = [t2].[HomePhone] AND    [t1].[Extension] = [t2].[Extension] AND    [t1].[Notes] = [t2].[Notes] AND    ([t1].[ReportsTo] IS NULL AND [t2].[ReportsTo] IS NULL OR [t1].[ReportsTo] IS NOT NULL AND [t2].[ReportsTo] IS NOT NULL AND [t1].[ReportsTo] = [t2].[ReportsTo]) AND    [t1].[PhotoPath] = [t2].[PhotoPath]

Not only is this request without tears to look it is impossible, it is still not working as among the fields of the table Employees have a field of type ntext, which can not be compared.

COUNCIL

Be careful in the description of the data model. BLToolkit uses meta-information about your entities to optimize queries, and its absence can lead to a less than optimal query construction.

Inheritance

Support for inheritance BLToolkit made in the image and likeness of inheritance in Linq To SQL. To organize inheritance require special markup entities as in the following code:

[TableName("Products")][InheritanceMapping(Code="True",  Type=typeof(DiscontinuedProduct))][InheritanceMapping(Code="False", Type=typeof(ActiveProduct))]public abstract class Product{    [PrimaryKey, Identity]                      public int      ProductID;    [NotNull]                                    public  string    ProductName;                                                 public  int ? SupplierID;                                                 public  int ? CategoryID;                                                 public  string    QuantityPerUnit;                                                 public  decimal ? UnitPrice,                                                 public  short ? UnitsInStock;                                                 public  short ? UnitsOnOrder;                                                 public  short ? ReorderLevel;    [MapField(IsInheritanceDiscriminator=true)] public bool     Discontinued;}public class ActiveProduct : Product{}public class DiscontinuedProduct : Product{}

Here the field Discontinued acts as a discriminator - a sign, according to which the objects are divided into certain types. Attribute InheritanceMapping allows you to compare the value of this field and the type that corresponds to this field. In this example, the types of ActiveProduct and DiscontinuedProduct are not specific to the type of field, but in principle it is not prohibited.

More information can be found at the following link - Linq To the SQL Inheritance.

Below are a few examples of the use of inheritance.

from p in db.DiscontinuedProductselect p;

SQL:

SELECT    [p].[ProductID],    [P]. [ProductName]    [p].[SupplierID],    [p].[CategoryID],    [p].[QuantityPerUnit],    [p].[UnitPrice],    [p].[UnitsInStock],    [p].[UnitsOnOrder],    [p].[ReorderLevel],    [p].[Discontinued]FROM    [Products] [p]WHERE    [p].[Discontinued] = 'True'

In exactly the same SQL will be generated for the following test:

from c in db.Productwhere c is Northwind.DiscontinuedProductselect c;

Standard features .NET Framework

BLToolkit supports conversion into SQL standard features about four .NET Framework. This includes functions for working with strings, dates, mathematical functions, conversion functions and converting scalar data types. If a feature has no direct counterpart in SQL, it is replaced by a specific to a particular database server algorithm. Here are some examples.

Here everything is simple. Property Length has a direct implementation in SQL:

from c in db.Customerwhere c.ContactName.Length > 5select c.ContactName;

SQL:

SELECT    [p].[ContactName]FROM    [Customers] [p]WHERE    Len([p].[ContactName]) > 5

Here Compare function is converted into the corresponding construction SQL:

from c in db.Customerwhere c.ContactName.CompareTo("John") > 0select c.ContactName;

SQL:

SELECT    [p].[ContactName]FROM    [Customers] [p]WHERE    [p].[ContactName] > 'John'

The following query to SQL implements a function equivalent to function Math.Round in .NET. The fact that this function is the default number of rounds to the nearest chёtnomu, so we can observe this unusual implementation.

from o in db.Orderwhere Math.Round(o.Freight) >= 10select o.Freight;

SQL:

SELECT    [o].[Freight]FROM    [Orders] [o]WHERE    CASE        WHEN [o].[Freight] - Floor([o].[Freight]) = 0.5 AND Floor([o].[Freight]) % 2 = 0            THEN Floor([o].[Freight])        ELSE Round([o].[Freight], 0)    END >= 10

It looks like this is all quite harmless, but here lies one unpleasant detail. Let's complicate our inquiry:

from o in db.Orderwhere Math.Round(o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)) >= 10select o.Freight;

SQL:

SELECT    [o].[Freight]FROM    [Orders] [o]        INNER JOIN [Order Details] [t1] ON [o].[OrderID] = [t1].[OrderID]WHERE    CASE        WHEN (            SELECT                Sum(Convert(Decimal(5,0), [t2].[Quantity]) * [t2].[UnitPrice])            FROM                [Order Details] [t2]            WHERE                [o].[OrderID] = [t2].[OrderID]        ) - Floor((            SELECT                Sum(Convert(Decimal(5,0), [t3].[Quantity]) * [t3].[UnitPrice])            FROM                [Order Details] [t3]            WHERE                [o].[OrderID] = [t3].[OrderID]        )) = 0.5 AND Floor((            SELECT                Sum(Convert(Decimal(5,0), [t4].[Quantity]) * [t4].[UnitPrice])            FROM                [Order Details] [t4]            WHERE                [o].[OrderID] = [t4].[OrderID]        )) % 2 = 0            THEN Floor((            SELECT                Sum(Convert(Decimal(5,0), [t5].[Quantity]) * [t5].[UnitPrice])            FROM                [Order Details] [t5]            WHERE                [o].[OrderID] = [t5].[OrderID]        ))        ELSE Round((            SELECT                Sum(Convert(Decimal(5,0), [t6].[Quantity]) * [t6].[UnitPrice])            FROM                [Order Details] [t6]            WHERE                [o].[OrderID] = [t6].[OrderID]        ), 0)    END >= 10

The result is not as expected. Therefore, such a request is best rewritten as follows:

from o in db.Orderlet sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)where Math.Round(sum) >= 10select o.Freight;

SQL:

SELECT    [o1].[Freight] as [Freight1]FROM    (        SELECT            (                SELECT                    Sum(Convert(Decimal(5,0), [t1].[Quantity]) * [t1].[UnitPrice])                FROM                    [Order Details] [t1]                WHERE                    [o].[OrderID] = [t1].[OrderID]            ) as [sum1],            [o].[Freight]        FROM            [Orders] [o]                INNER JOIN [Order Details] [t2] ON [o].[OrderID] = [t2].[OrderID]    ) [O1]WHERE    CASE        WHEN [o1].[sum1] - Floor([o1].[sum1]) = 0.5 AND Floor([o1].[sum1]) % 2 = 0            THEN Floor([o1].[sum1])        ELSE Round([o1].[sum1], 0)    END >= 10

So much better. In fact, the previous query is equivalent to the following:

from o in    from o in db.Order    select new    {        sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice),        O    }where  Math.Round(o.sum) >= 10select o.o.Freight;

Design let wrapped into one more Select, for which BLToolkit creates a subquery. But sub-queries are not all Select. Thus, for example, the following code will not be created subquery:

from o in db.Orderlet sum = o.Freightwhere  Math.Round(sum) >= 10select o.Freight;

BLToolkit finds that in such a subquery, there is no expression, but only appeal to the fields of the table and eliminate unnecessary nesting.

Additional features BLToolkit

In addition to supporting standard features Linq, BLToolkit implementing a number of additional extensions.

Select

Sometimes you need to get from the database server some data not included in any of the tables. For example, it may be the next request for the current date of the server:

SELECT     CURRENT_TIMESTAMP as [c1]

BLToolkit Such functionality is implemented as follows:

static void SimpleSelectTest(){    using (var db = new NorthwindDB())    {        var value = db.Select(() => Sql.CurrentTimestamp);        Console.WriteLine(value);    }}

DML operations

All existing Linq providers implement data manipulation operations (Insert, Update, Delete) by the Entity Services, which causes a lot of criticism among developers for many reasons. BLToolkit offers a complete set of operations DML, including operations to manipulate the set of records with one request.

Insert

The next method is to add a new entry in the database table.

db.Employee.Insert(() => new Northwind.Employee{    FirstName = "John",    LastName  = "Shepard",    Title     = "Spectre",    HireDate  = Sql.CurrentTimestamp}

SQL:

INSERT INTO [Employees] (    [FirstName],    [LastName],    [Title],    [HireDate])VALUES(    'John',    'Shepard',    'Spectre',    CURRENT_TIMESTAMP)

Unfortunately, this method can not work with objects that do not have a default constructor, or has read-only properties. Therefore, for these objects BLToolkit offers an alternative Insert:

db    .Into(db.Employee)        .Value(e => e.FirstName, "John")        .Value(e => e.LastName,  "Shepard")        .Value(e => e.Title,     "Spectre")        .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)    .Insert();

SQL statement for this query is similar to the previous.

Notice the way to set the value of the last field - is used instead of just the value of lambda. This is necessary in order to assign a field value server variable.

The next group of functions allows you to work with multiple entries:

db.Region    .Where(r => r.RegionID > 2)    .Insert(db.Region, r => new Northwind.Region()    {        RegionID          = r.RegionID + 100,        RegionDescription = "Copy Of " + r.RegionDescription    }

And the alternative:

db.Region    .Where(r => r.RegionID > 2)    .Into(db.Region)        .Value(_ => _.RegionID,          r => r.RegionID + 100)        .Value(_ => _.RegionDescription, r => "Copy Of " + r.RegionDescription)    .Insert();

SQL:

INSERT INTO [Region] (    [RegionID],    [RegionDescription])SELECT    [r].[RegionID] + 100,    'Copy Of ' + [r].[RegionDescription]FROM    [Region] [r]WHERE    [r].[RegionID] > 2

InsertWithIdentity

This method is used to insert a record into a table that has an Identity field, and the return value of this field. Identity field should be labeled with IdentityAttribute.

var value = db.Employee.InsertWithIdentity(() => new Northwind.Employee{    FirstName = "John",    LastName  = "Shepard",    Title     = "Spectre",    HireDate  = Sql.CurrentTimestamp}

var value =    db        .Into(db.Employee)            .Value(e => e.FirstName, "John")            .Value(e => e.LastName,  "Shepard")            .Value(e => e.Title,     () => "Spectre")            .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)        .InsertWithIdentity();

SQL:

INSERT INTO [Employees] (    [FirstName],    [LastName],    [Title],    [HireDate])VALUES(    'John',    'Shepard',    'Spectre',    CURRENT_TIMESTAMP)SELECT SCOPE_IDENTITY()

Update

The following three methods allow you to update one or more entries in the table.

Upgrading from a predicate:

db.Employee    .Update(        e => e.Title == "Spectre",        e => new Northwind.Employee        {            Title = "Commander"        });

Upgrading from subquery:

db.Employee    .Where(e => e.Title == "Spectre")    .Update(e => new Northwind.Employee    {        Title = "Commander"    });

Update read-only properties:

db.Employee    .Where(e => e.Title == "Spectre")    .Set(e => e.Title, "Commander")    .Update();

For all of these queries generated the following SQL:

UPDATE    [is]SET    [Title] = 'Commander'FROM    [Employees] [e]WHERE    [e].[Title] = 'Spectre'

Another update operation, which involved one of the fields of the table:

db.Employee  .Where(e => e.Title == "Spectre")  .Set(e => e.HireDate, e => e.HireDate.Value.AddDays(10))  .Update();

SQL:

UPDATE  [is]SET  [HireDate] = DateAdd(Day, 10, [e].[HireDate])FROM  [Employees] [e]WHERE  [e].[Title] = 'Spectre'

Delete

And in the end the delete operation.

Deleting a predicate:

db.Employee.Delete(e => e.Title == "Spectre");

Remove with a subquery:

db.Employee    .Where(e => e.Title == "Spectre")    .Delete();

SQL:

DELETE [e]FROM    [Employees] [e]WHERE    [e].[Title] = 'Spectre'

SQL Function

Along with the standard functions of .NET Framework, BLToolkit allows you to use SQL functions that are available databases. The following two examples are similar to the examples that we have considered above the standard features for .NET Framework, but instead they use features SQL.

Using the string length:

from c in db.Customerwhere Sql.Length(c.ContactName) > 5select c.ContactName;

SQL:

SELECT    [c].[ContactName]FROM    [Customers] [c]WHERE    Len([c].[ContactName]) > 5

Function rounding:

from o in db.Orderwhere  Sql.Round(o.Freight) >= 10select o.Freight;

SQL:

SELECT    [o].[Freight]FROM    [Orders] [o]WHERE    Round([o].[Freight], 0) >= 10

Note, this time caused by rounding function directly as it is present in SQL.

Expanding BLToolkit

SQL Function

BLToolkit allows us not only to use SQL functions, but also easily add their own. Realization of us considered the function of calculating the length of the line is as follows:

[SqlFunction][SqlFunction("Access",    "Len")][SqlFunction("Firebird",  "Char_Length")][SqlFunction("MsSql2005", "Len")][SqlFunction("MsSql2008", "Len")][SqlFunction("SqlCe",     "Len")][SqlFunction("Sybase",    "Len")]public static int? Length(string str){    return str == null ? null : (int?)str.Length;}

Thus, SQL function - this is the usual method marked attribute SqlFunction. Attribute SqlFunction includes the following properties:

• Name - name of the SQL function. If the name is not specified, it is used as a method name.
• SqlProvider - name of the SQL provider. If no attribute among the attributes to the desired provider is taken without specifying the attribute provider.
• ArgIndices - an array that allows you to restrict or specify the order of the arguments concerning the method of SQL functions. For example, this is useful when the same function for different database not only has different names, but the order of the arguments.
• ServerSideOnly - flag prohibiting perform the function on the client. Always leads to the generation of SQL.

The last parameter requires special explanation. The fact that BLToolkit attempts to minimize the load on the database server, and if possible, to shift the execution of certain functions on the client. But this behavior is not always useful. If SQL function flagged ServerSideOnly, the function anyway translated into SQL.

As BLToolkit contains a function which can be used to force converted to SQL expressions those which did not originally anticipated. Consider the example of everything with the same function of calculating the length of the line:

from c in db.Customerselect Sql.Length(c.ContactName);

SQL:

SELECT    [c].[ContactName]FROM    [Customers] [c]

In this case, the string is passed to the client, which is already calculated its length.

In the following example, we use the Sql.AsSql, to prevent this behavior:

from c in db.Customerselect Sql.AsSql(Sql.Length(c.ContactName));

SQL:

SELECT    Len([c].[ContactName]) as [c1]FROM    [Customers] [c]

You probably guessed it should look like a function:

[SqlExpression("{0}", 0, ServerSideOnly=true)]public static T AsSql<T>(T obj){    return obj;}

Notable here is that in this case we do not use SqlFunction attribute, and attribute similar SqlExpression. It is very similar SqlFunction, but it takes a format string as a parameter by which builds the SQL statement. The arguments are a format string method parameters, and if we are dealing with a generalized method or class, and the generalized parameters. Thus function Sql.AsSql will be passed two parameters: objand T. But since the parameter T us in this case is not interested, the second parameter attribute SqlExpression in our example just says BLToolkit, which used to be only one parameter at index 0.

It should also mention one more attribute - SqlProperty. In fact, it's just a function with no parameters. This property Sql.CurrentTimestamp we've met before. That is its implementation:

[SqlProperty("CURRENT_TIMESTAMP",   ServerSideOnly = true)][SqlProperty("Informix", "CURRENT", ServerSideOnly = true)][SqlProperty("Access",   "Now",     ServerSideOnly = true)][SqlFunction("SqlCe",    "GetDate", ServerSideOnly = true)][SqlFunction("Sybase",   "GetDate", ServerSideOnly = true)]public static DateTime CurrentTimestamp{    get { throw new LinqException("The 'CurrentTimestamp' is server side only property."); }}

Attributes SqlFunction, SqlExpression and SqlProperty can be applied simultaneously to the same method to different providers. Here, for example, looks like a method TrimLeft:

[SqlExpression("DB2", "Strip({0}, L, {1})")][SqlFunction  (       "LTrim")]public static string TrimLeft(string str, char? ch){    return str == null || ch == null ? null : str.TrimStart(ch.Value);}

Using these attributes, you can easily expand your set of SQL functions and attribute SqlExpression allows you to create almost any SQL statements that you may need.

Substitution method

SQL functions are very important and useful primitives, but unfortunately, the creation of less complex algorithms, SQL Servers are not provided, they are extremely difficult. Take for example our already visible feature Math.Round. Its implementation in C # with SQL primitives is as follows:

static decimal? RoundToEven(decimal? value){    return        value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?            Sql.Floor(value) :            Sql.Round(value);}

The implementation of these algorithms using SQL functions is not really rewarding, also encode an algorithm as a text string, we exclude the possibility of optimization, which performs BLToolkit for expressions.

To implement such functions BLToolkit offers another mechanism.

First of all, we need a method that we will call our Linq queries. The above RoundToEven is fine. Next, we need to register this function in BLToolkit and attach to it the necessary expression. This is done as follows:

static void RoundToEvenTest(){    Expressions.MapMember<decimal?,decimal?>(        value => RoundToEven(value),        value =>            value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?                Sql.Floor(value) :                Sql.Round(value));    using (var db = new NorthwindDB())    {        var query =            from o in db.Order            let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)            where RoundToEven(sum) >= 10            select o.Freight;        foreach (var item in query)        {            Console.WriteLine(item);        }    }}

The first argument of the method MapMember can either type MemberInfo, or lambda from which this MemberInfo can be easily obtained. The second option - an expression that will be substituted for the first method, when BLToolkit meet such a method in Linq expression.

Supports all standard functions of the .NET Framework BLToolkit realized in this way.

In addition, the method MapMember can receive name of the SQL provider that allows you to implement different algorithms for different database servers.

Reuse expressions

The above method is good for the implementation of global, universal functions. But sometimes you want to re-use Linq expressions for specific business logic. To this end, there is a similar mechanism BLToolkit allowing locally declare methods and expressions for substitution.

Suppose we have the following code, and we want the expression Count reused for different values ​​ShipRegion.

from c in db.Customerselect new{    sum1 = c.Orders.Count(o => o.ShipRegion == "SP"),    sum2 = c.Orders.Count(o => o.ShipRegion == "NM")}

This can be done as follows:

[MethodExpression("OrderCountExpression")]static int OrderCount(Northwind.Customer customer, string region){    throw new NotImplementedException();}static Expression<Func<Northwind.Customer,string,int>> OrderCountExpression(){    return (customer, region) => customer.Orders.Count(o => o.ShipRegion == region);}static void MethodExpressionTest(){    using (var db = new NorthwindDB())    {        var query =            from c in db.Customer            select new            {                sum1 = OrderCount(c, "SP"),                sum2 = OrderCount(c, "NM")            }        foreach (var item in query)        {            Console.WriteLine(item);        }    }}

There OrderCount is a method that we will use in our Linq expression and replace it with another wildcard expression. How is it to replace the expression is defined attribute MethodExpression. This attribute specifies the method that we will return the resulting expression. In our example, this method OrderCountExpression.

Below is the result SQL:

SELECT    (        SELECT            Count(*)        FROM            [Orders] [o]        WHERE            [c].[CustomerID] = [o].[CustomerID] AND [o].[ShipRegion] = 'SP'    ) as [c1],    (        SELECT            Count(*)        FROM            [Orders] [o1]        WHERE            [c].[CustomerID] = [o1].[CustomerID] AND [o1].[ShipRegion] = 'NM'    ) as [c2]FROM    [Customers] [c]

Performance

Performance issues in Linq

The ability to create Linq providers, broadcasting Linq to SQL queries and other formats is undoubtedly the greatest achievement of the ruling in the technology industry. But what is the charge for such achievements? The article Building a LINQ Provider briefly describes the process of creating a service provider like Linq To SQL. In addition, this article describes the path that passes Linq expression of the structure created by the compiler, before converting it into a SQL or another format.

In contrast to the normal code, the compiler does not produce on the basis of Linq expressions executable code, but instead includes a code for forming a special structure that describes the original Linq expression, the so-called Expression Tree. Further, this structure somehow passed Linq provider, which in turn parses it and converts to the desired format. All this is done at runtime. Ie calling a method with Linq query, we call the code that each time first creates Expression Tree, then passes it Linq provider, which in turn parses it using complex and resource-intensive algorithm.

You could cache the results of parsing, but apart from the fact that every time we are dealing with a newly created structure, and it can even be different. Differences can arise, firstly, because of the possibility to create such a structure by parts, such as in the following example:

var query =    from e in db.Employee    select e;if (a > 0){    query =        from e in query        where e.EmployeeID > id        select e;}

Second, the values ​​of transmitted parameters, such as the parameter id in the example above, are incorporated directly into Expression Tree, making this structure depends on the values ​​of the parameters.

As a result, we have to parse the Linq queries each time. It is clear that this approach is low speed of Linq queries we guarantee.

Turning to the test site ORMBattle.net, for example, to this chart, you can easily verify the validity of this assumption. Below is a table with the numbers from the chart that we will be interested.

BLT

EF

IS

DO

L2S

SQL

Unit

LINQ Query

8749

450

933

1559

835

n/a

queries/s

Compiled LINQ Query

13222

6501

2395

8046

9114

n/a

queries/s

Native Query

17187

9728

8465

10335

n/a

18645

queries/s

Here, horizontal:

• Query LINQ - Linq queries is implemented by different providers.
• Linq Query Compiled - compiled Linq queries. On them will be discussed below.
• Query Native - database queries without using Linq.

Vertical lists various Linq providers penultimate column - pure SQL, the latter - a unit of measure (RPS). The highest attainable standard of bold - is the SQL / Native Query.

Compared with the figures of the reference line LINQ Query you can see that some providers slowing client code using Linq queries may be dozens! times.

As BLToolkit struggles with the situation, provided that the parsing algorithm Expression Tree in BLToolkit no less complex and resource-intensive? We remember that the cache Expression Tree is impossible, but simple logic says that if you really want, you can.

Stratagem is as follows. For each request takes a cache BLToolkit already processed trees and compares them with the original. When comparing the trees are not captured fragments, which are regarded as parameters. If the same initial request is in the cache, the original Expression Tree transferred him to extract the parameters, then the query is executed. To reduce the number of comparisons Point requests last move forward, but the main thing for each type of the value returned by the query is constructed separate cache. Thus the number of comparisons trees reduced to a minimum, and anonymous types, it is almost unique. The result you can see in the table - the usual BLToolkit Linq queries run faster even compiled Linq query of most providers.

CompiledQuery

To solve the above problem, many providers, including BLToolkit, offer so-called Compiled Query - Linq query compilation lambda, which can then be used without re-parsing. It looks as follows:

static Func<NorthwindDB,int,IEnumerable<Northwind.Employee>> _query =    CompiledQuery.Compile<NorthwindDB,int,IEnumerable<Northwind.Employee>>((db, n) =>        from e in db.Employee        where e.EmployeeID > n        orderby e.LastName, e.FirstName        select e    );static void CompiledQueryTest(){    using (var db = new NorthwindDB())    {        var query = _query(db, 5);        foreach (var item in query)        {            Console.WriteLine(item);        }    }}

All request is passed to the method Compile class CompileQuery, returns a compiled lambda, which can then be used like an ordinary function.

With this approach, the execution of queries on the client grows at times, but the board is clear - more cumbersome and non-obvious code.

Conclusion

At the end I want to thank everyone who took part in the discussion and testing Linq provider BLToolkit. Special thanks to the entire project , once again who became a testing ground for new ideas, and personally Andrei Koryavchenko for positive criticism, identified mistakes and a lot of interesting ideas.