当前位置：首页 -> AI编程基础 -> 数据库编程

BLToolkitLinq-provider(十二)

2015-11-21 01:53:40 · 作者: · 浏览: 69
标签: BLToolkitLinq-provider
, 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
      
       (
        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.Customer
select 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
      
       > 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