Linq to LLBL Gen Pro LLBL Gen的Linq程序设计

继续讲解LLBL Gen的开发教程，这一篇学习Linq to LLBL Gen的应用。

MSDN对Linq的解释如下：LINQ（语言级集成查询）的意图就是提供一种统一且对称的方式，让程序员在广义的数据上获取和操作数据。虽然Linq to SQL已经不再更新，但是Linq to xml,Linq to Object仍然很实用，它可以简化程序代码的编写。在没有Linq的.NET 2.0时代，通常只能用foreach循环遍历来查找满足条件的数据。

LinqMetaData

如果要在LLBL Gen的项目中使用Linq，首先要保证你的项目属性是.NET 3.0/3.5以上，这会调用Linq的模板生成需要的元数据。在生成的DatabaseGeneric中会产生Linq的文件夹，并且增加类型LinqMetaData。它的定义如下所示

public partial class LinqMetaData: ILinqMetaData
{
     #region Class Member Declarations
      private IDataAccessAdapter _adapterToUse;
      private FunctionMappingStore _customFunctionMappings;
      private Context _contextToUse;
      #endregion
        
     /// <summary>CTor. Using this ctor will leave the IDataAccessAdapter object to use empty. 
     /// To be able to execute the query, an IDataAccessAdapter instance
     /// is required, and has to be set on the LLBLGenProProvider2 object in the query to execute. 
     ///</summary>
     public LinqMetaData() : this(null, null)
     {
     }
}

这里省略了一些方法和属性，请参考生成的类型来学习它的代码。要使用Linq,请传入当前项目的DataAccessAdapter

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
}

为什么可以这样的写？因为LinqMetaData提供了Customers的集合属性，定义如下所示

/// <summary>returns the datasource to use in a Linq query when targeting CustomerEntity instances in the database.</summary>
public DataSource2<CustomerEntity> Customer
{
   get { return new DataSource2<CustomerEntity>(_adapterToUse, new ElementCreator(), 

                    _customFunctionMappings, _contextToUse); }
}

LLBL Gen提供了两种模式的代码调用方式：Adapter和SelfServicing，导致同一种类型要定义二次。比如SelfServicing中实体定义为IEntity，而Adapter则定义于IEntity2，在名称后面加一个数字2。以此类推，凡是类型名后面有数字2的，表示它适用于Adapter模式。Customer属性是定义一个数据源对象。

在这里，推荐安装测试工具软件TestDriven.NET,它的Personal版本完全免费。在任何一个.NET项目文件中，新建立一个方法，点击右键Test With->Debugger，很方便的调试一段代码。因为这个软件，我就再也没有使用很著名的代码片段编辑工具Snippet Compiler，TestDriven.NET会让带给你编写.NET测试代码非常便利的体验。

转化Linq 查询结果

使用Linq to LLBL Gen查询数据之后，如果要把数据绑定到界面中，则需要提供成Entity2或EntityCollection样式。

这里有ILLBLGenProQuery接口用于转化Linq查询结果，以减少我们自已转化的代码。

var q = from c in metaData.Customer 
        where c.Country=="Germany"  
        select c;
EntityCollection<CustomerEntity> customers = 
    ((ILLBLGenProQuery)q).Execute<EntityCollection<CustomerEntity>>();

如代码所示，customers就可以直接用于绑定到界面的GridView或DataGridView中。

写到这里，发现Windows Live Writer的Insert Code有一个小bug：对Linq查询中的from/select关键字没有高亮显示。瑕不掩玉，这个Insert Code工具帮助我的博客中的规范化的代码方面产生了重要作用。

Group by 分组

按照国家和城市为组，对客户进行分类

var q = from c in metaData.Customer
        group c by new { c.Country, c.City } into g
        select new { g.Key, g.Count()};

按照客户的国家分类

var q = from c in metaData.Customer
        group c by c.Country into g
        select g;

Order By 排序

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

CustomerEntity的CustomerId属性是一个字符串，按照它的第二个字母排序。

Queryable: Contains 包含的查询

// 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, 与7对比，换成IEnumerable。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;

Linq的查询有些复杂，如果不能理解，可以在工作中需要用到的时候再来仔细体会。

Excluding / Including fields  不包含/包含字段

这个需求很重要。在写SQL语句时，不要写SELECT *，而是用具体的字段名，Excluding / Including 也就是用来指定需要SELECT出来的字段名。下面的查询，在查询结果集中不包括Photo和Notes字段。

var q = (from e in metaData.Employee
         select e).ExcludeFields(e=>e.Photo, e=>e.Notes);

Calling an in-memory method in the projection 在Linq投影中调用方法

/// 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)
        };

在Linq的返回结果中调用方法，select new产生一个匿名对象，可以对它进行再调用方法处理加工。

也可以直接用Lambda表达式来完成这样的操作，例子代码如下所示

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); 

 

Prefetch paths 子查询

方法 1: WithPath and PathEdges

// query  A
var q =  from c in metaData.Customer.WithPath(...) select c;

// query  B
var q =  (from c in metaData.Customer select c).WithPath(...);

// query  C
var q =  (from c in metaData.Customer.WithPath(...) where ... select c) join o in  metaData.Order on ...

 

方法2：WithPath and Lambda expressions

var q = (from c in metaData.Customer
         where c.Country=="UK"
         select c).WithPath(p=>p.Prefetch(c=>c.Orders));

Function mappings 函数映射

从开头的一段代码中我们看到，Linq to LLBL Gen会产生直接的数据库查询动作。查询数据库时，ORM框架也会调用一些数据库系统的函数，比如SUM,AVG或是自定义的函数，这就要求有一种机制来定义函数的调用方法。

数据库函数定义代码如下

ALTER   FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
    DECLARE @toReturn DECIMAL

    SELECT @toReturn = SUM((UnitPrice-(Discount * @useDiscounts)) * Quantity)
    FROM [Order Details] 
    WHERE OrderID = @orderID
    GROUP BY OrderID

    RETURN @toReturn
END

LLBL Gen的代码生成工具会对这个函数进行.NET封装调用，产生如下所示的代码以调用数据库中的fn_CalculateOrderTotal

public class NorthwindFunctions
{
     public static decimal CalculateOrderTotal(int orderId, bool useDiscounts)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return 0.0M;
    }
}

public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CalculateOrderTotal", 2, 
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
    }
}

NorthwindFunctions类型会根据数据库中的函数，生成.NET调用代码。NorthwindFunctionMappings 则把这个函数转化为Linq to LLBL Gen的映射定义方式，最后看到应用代码是这样的

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
var q = from o in metaData.Order
             where o.CustomerId == "CHOPS"
             select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) };

Full-text search 全文索引

以上面为基础，实现Linq中的全文索引就比较容易，对于SQL Server，在自定义的数据库函数中调有数据Contains。

public class NorthwindFunctions
{
    public static bool FullTextSearch(string fieldToSearch, string toFind)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return true;
    }
}

/// Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS()
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
         // FullTextSearch(2) on 1 field
         this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"));
    }
}

应用代码与前面的相同，在使用前设置CustomFunctionMappings

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
var q = from e in metaData.Employee
        where NorthwindFunctions.FullTextSearch(e.Notes, "BA")
        select e;

经过这两个例子，就引伸出关于调用数据库中函数的方法，也就是如何调用我们经常用到的SQL函数SUM/AVG。请参考帮助文档中的Supported default method / property mappings to functions。

一般来说，调用数据库的函数比前面的调用自定义函数更简单，ORM框架会预先设置好这些函数的映射。对于SQL Server 2005及以上的版本，因为引入了CLR Host技术，会略有些不同。如果我们的SQL代码不涉及CLR 函数和类型，调用方法和前面的一样。如果有涉及，要确保添加正确的的Function Mapping。