继续讲解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。