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。
