在LINQ to SQL中使用Translate方法以及修改查询用SQL
2008-02-19 03:02 Jeffrey Zhao 阅读(30074) 评论(64) 编辑 收藏 举报目前LINQ to SQL的资料不多——老赵的意思是，目前能找到的资料都难以摆脱“官方用法”的“阴影”。LINQ to SQL最权威的资料自然是MSDN，但是MSDN中的文档说明和实例总是显得“大开大阖”，依旧有清晰的“官方”烙印——这简直是一定的。不过从按照过往的经验，在某些时候如果不按照微软划定的道道来走，可能就会发现别样的风景。老赵在最近的项目中使用了LINQ to SQL作为数据层的基础，在LINQ to SQL开发方面积累了一定经验，也总结出了一些官方文档上并未提及的有用做法，特此和大家分享。
言归正传，我们先看一个简单的例子。
Item实体对应Item表，每个Item拥有一些评论，也就是ItemComment。Item实体中有一个Comments属性，是ItemComment实体的集合。这个例子将会使用这个再简单不过的模型。
为用户显示他的Item列表是非常常见的需求，如果使用LINQ to SQL来获取Item的话，我们可能会这么做：
public List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.UserID == ownerId
orderby item.CreateTime descending
select item;
return query.ToList();
}
这么做自然可以实现我们想要的功能，这的确没错。但是这种做法有个很常见的问题，那就是可能会获得太多不需要的数据。一个Item数据量最大的是Introduction字段，而显示列表的时候我们是不需要显示它的。如果我们在获取Item列表时把Introduction一起获得的话，那么应用服务器和数据库服务器之间的数据通信量将会成百甚至上千地增长了。因此我们在面向此类需求的话，都会忽略每个Item对象的Introduction字段。那么我们该怎么做呢？对LINQ有简单了解的朋友们可能会想到这么做：
public List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.UserID == ownerId
orderby item.CreateTime descending
select new Item
{
ItemID = item.ItemID,
Title = item.Title,
UserID = item.UserID,
CreateTime = item.CreateTime
};
return query.ToList();
}
这个做法很直观，利用了C# 3.0中的Object Initializer特性。编译通过了，理应没有错，可是在运行时却抛出了NotSupportedException：“Explicit construction of entity type 'Demo.Item' in query is not allowed.”，意思就是不能在LINQ to SQL中显式构造Demo.Item对象。
事实上在RTM之前的版本中，以上的语句是能运行通过的——我是指通过，不是正确。LINQ to SQL在RTM之前的版本有个Bug，如果在查询中显式构造一个实体的话，在某些情况下会得到一系列完全相同的对象。很可惜这个Bug我只在资料中看到过，而在RTM版本的LINQ to SQL中这个Bug已经被修补了，确切地说是绕过了。直接抛出异常不失为一种“解决问题”的办法，虽然这实际上是去除了一个功能——没有功能自然不会有Bug，就像没有头就不会头痛了一个道理。
但是我们还得做，难道我们只能自己SQL语句了吗？
使用Translate方法
幸亏DataContext提供了Translate方法，Translate方法的作用就是从一个DbDataReader对象中生成一系列的实例。其中最重要的就是一个带范型的重载：
public static List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
dataContext.Connection.Open();
SqlCommand command = new SqlCommand(
"SELECT [ItemID], [Title], [UserID], [CreateTime]" +
" FROM [Item] WHERE [UserID] = " + ownerId +
" ORDER BY [CreateTime]",
(SqlConnection)dataContext.Connection);
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
return dataContext.Translate<Item>(reader).ToList();
}
}
在这段代码里，我们拼接出了一段SQL语句，实现了我们需要的逻辑。在ExecuteReader之后即使用dataContext.Translate方法将DbDataReader里的数据转换成Item对象。使用Translate方法除了方便之外，生成的对象也会自动Attach到DataContext中，也就是说，我们可以继续对获得的对象进行操作，例如访问Item对象的Comments属性时会自动去数据库获取数据，改变对象属性之后调用SubmitChange也能将修改提交至数据库。Translate方法从DbDataReader中生成对象的规则和内置的DataContext.ExecuteQuery方法一样，大家可以查看MSDN中的说明（中文、英文）。
此外，这里有两个细节值得一提：
- 为什么调用ExecuteReader方法时要传入CommandBehavior.CloseConnection：LINQ to SQL中的DataContext对象有个特点，如果在使用时它的Connection对象被“显式”地打开了，即使调用了DataContext对象的Dispose方法也不会自动关闭。因此我们在开发程序的时候一定要注意这一点。例如，在调用ExecuteReader是传入CommandBehavior.CloseConnection，这样就保证了在关闭DbDataReader时同时关闭Connection——当然，我们也可以不这么做。
- 在调用Translate方法后为什么要直接调用ToList方法：因为GetItemsForListing方法的返回值是List<Item>，这是原因之一。另一个原因是Translate方法并不会直接生成所有的对象，而是在外部代码访问Translate方法返回的IEnmuerable<T>时才会生成其中每个对象。这也是一种Lasy Load，但是也导致了所有的对象必须在Reader对象关闭之前生成，所以我一般都会在Translate方法后直接调用ToList方法，保证所有的对象已经生成了。虽然事实上我们也可以不使用using关键字而直接返回Translate方法生成的IEnumerable<Item>，不过这么做的话当前链接就得不到释放（释放，而不是关闭），也就是把处理数据连接的问题交给了方法的使用者——很可能就是业务逻辑层。为了确保分层结构的职责分明，我一般倾向于在这里确保所有对象的已经生成了。
上面的例子使用拼接SQL字符串的方式来访问数据库，那我们又该如何使用LINQ to SQL呢？幸亏LINQ to SQL中的DataContext提供了GetCommand方法。我们直接来看一个完整的扩展：
public static class DataContextExtensions
{
public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable query)
{
DbCommand command = dataContext.GetCommand(query);
dataContext.OpenConnection();
using (DbDataReader reader = command.ExecuteReader())
{
return dataContext.Translate<T>(reader).ToList();
}
}
private static void OpenConnection(this DataContext dataContext)
{
if (dataContext.Connection.State == ConnectionState.Closed)
{
dataContext.Connection.Open();
}
}
}
自从有了C# 3.0中的Extension Method，很多扩展都会显得非常优雅，我非常喜欢这个特性。DataContextExtensions是我对于LINQ to SQL中DataContext对象的扩展，如果以后有新的扩展也会写在这个类中。OpenConnection方法用于打开DataContext中的数据连接，今后的例子中也会经常看到这个方法。而这次扩展的关键在于新的ExecuteQuery方法，它接受一个IQueryable类型的对象作为参数，返回一个范型的List。方法中会使用DataContext的GetCommand方法来获得一个DbCommand。在我之前的文章，以及MSDN中的示例都只是通过这个DbCommand对象来查看LINQ to SQL所生成的查询语句。也就是说以前我们用它进行Trace和Log，而我们这次将要真正地执行这个DbCommand了。剩下的自不必说，调用ExecuteReader方法获得一个DbDataReader对象，再通过Translate方法生成一个对象列表。
新的ExecuteQuery方法很容易使用：
public static List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.UserID == ownerId
orderby item.CreateTime descending
select new
{
ItemID = item.ItemID,
Title = item.Title,
CreateTime = item.CreateTime,
UserID = item.UserID
};
using (dataContext.Connection)
{
return dataContext.ExecuteQuery<Item>(query);
}
}
在通过LINQ to SQL获得一个query之后，我们不再直接获得查询数据了，而是将其交给我们的ExecuteQuery扩展来执行。现在这种做法既保证了使用LINQ to SQL进行查询，又构造出Item对象的部分字段，算是一种较为理想的解决方案。不过使用这个方法来获得仅有部分字段的对象时需要注意一点：在构造匿名对象时使用的属性名，可能和目标实体对象（例如之前的Item）的属性名并非一一对应的关系。
这种情况会在实体对象的属性名与数据表字段名不同的时候发生。在使用LINQ to SQL时默认生成的实体对象，其属性名与数据库的字段名完全对应，这自然是最理想的情况。但是有些时候我们的实体对象属性名和数据库字段名不同，这就需要在ColumnAttribute标记中设置Name参数了（当然，如果使用XmlMappingSource的话也可以设置），如下：
[Table(Name = "dbo.Item")]
public partial class Item : INotifyPropertyChanging, INotifyPropertyChanged
{
[Column(Storage = "_OwnerID", DbType = "Int NOT NULL", Name = "UserID")]
public int OwnerID
{
get {...}
set {...}
}
}
OwnerID属性上标记的ColumnAttribute的Name属性设为UserID，这表示它将与Item表中的UserID字段对应。那么如果我们要在这种情况下改写之前的GetItemsForListing方法，我们该怎么做呢？可能有朋友会很自然的想到：
public static List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.OwnerID == ownerId
orderby item.CreateTime descending
select new
{
ItemID = item.ItemID,
Title = item.Title,
CreateTime = item.CreateTime,
OwnerID = item.OwnerID
};
using (dataContext.Connection)
{
return dataContext.ExecuteQuery<Item>(query);
}
}
按照“常理”判断，似乎只要将所有的UserID改为OwnerID即可——其实不然。查看方法返回的结果就能知道，所有对象的OwnerID的值都是默认值“0”，这是怎么回事呢？使用SQL Profiler观察以上代码所执行SQL语句之后我们便可明白一切：
SELECT [t0].[ItemID], [t0].[Title], [t0].[CreateTime], [t0].[UserID] AS [OwnerID]
FROM [dbo].[Item] AS [t0]
WHERE [t0].[UserID] = @p0
ORDER BY [t0].[CreateTime] DESC
由于我们所使用的query实际上是用于生成一系列匿名对象的，而这些匿名对象所包含的是“OwnerID”而不是“UserID”，因此LINQ to SQL实际在生成SQL语句的时候会将UserID字段名转换成OwnerID。由于Item的OwnerID上标记的ColumnAttribute把Name设置成了UserID，所以Translate方法读取DbDataReader对象时事实上会去寻找UserID字段而不是OwnerID字段——这很显然就造成了目前的问题。因此，如果您使用了ColumnAttribute中的Name属性改变了数据库字段名与实体对象属性名的映射关系，那么在创建匿名对象的时候还是要使用数据库的字段名，而不是实体对象名，如下：
public static List<Item> GetItemsForListing(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.OwnerID == ownerId
orderby item.CreateTime descending
select new
{
ItemID = item.ItemID,
Title = item.Title,
CreateTime = item.CreateTime,
UserID = item.OwnerID
};
using (dataContext.Connection)
{
return dataContext.ExecuteQuery<Item>(query);
}
}
这样就能解决问题了——不过显得不很漂亮，因此在使用LINQ to SQL时，我建议保持实体对象属性名与数据库字段名之间的映射关系。
改变LINQ to SQL所执行的SQL语句
按照一般的做法我们很难改变LINQ to SQL查询所执行的SQL语句，但是既然我们能够将一个query转化为DbCommand对象，我们自然可以在执行之前改变它的CommandText。我这里通过一个比较常用的功能来进行演示。
数据库事务会带来锁，锁会降低数据库并发性，在某些“不巧”的情况下还会造成死锁。对于一些查询语句，我们完全可以显式为SELECT语句添加WITH (NOLOCK)选项来避免发出共享锁。因此我们现在扩展刚才的ExecuteQuery方法，使它接受一个withNoLock参数，表明是否需要为SELECT添加WITH (NOLOCK)选项。请看示例：
public static class DataContextExtensions
{
public static List<T> ExecuteQuery<T>(
this DataContext dataContext, IQueryable query, bool withNoLock)
{
DbCommand command = dataContext.GetCommand(query, withNoLock);
dataContext.OpenConnection();
using (DbDataReader reader = command.ExecuteReader())
{
return dataContext.Translate<T>(reader).ToList();
}
}
private static Regex s_withNoLockRegex =
new Regex(@"(] AS \[t\d+\])", RegexOptions.Compiled);
private static string AddWithNoLock(string cmdText)
{
IEnumerable<Match> matches =
s_withNoLockRegex.Matches(cmdText).Cast<Match>()
.OrderByDescending(m => m.Index);
foreach (Match m in matches)
{
int splitIndex = m.Index + m.Value.Length;
cmdText =
cmdText.Substring(0, splitIndex) + " WITH (NOLOCK)" +
cmdText.Substring(splitIndex);
}
return cmdText;
}
private static SqlCommand GetCommand(
this DataContext dataContext, IQueryable query, bool withNoLock)
{
SqlCommand command = (SqlCommand)dataContext.GetCommand(query);
if (withNoLock)
{
command.CommandText = AddWithNoLock(command.CommandText);
}
return command;
}
}
上面这段逻辑的关键在于使用正则表达式查找需要添加WITH (NOLOCK)选项的位置。在这里我查找SQL语句中类似“] AS [t0]”的字符串，并且在其之后添加WITH (NOLOCK)选项。其他的代码大家应该完全能够看懂，我在这里就不多作解释了。我们直接来看一下使用示例：
public static List<Item> GetItemsForListingWithNoLock(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
var query = from item in dataContext.Items
where item.UserID == ownerId
orderby item.CreateTime descending
select new
{
ItemID = item.ItemID,
Title = item.Title,
CreateTime = item.CreateTime,
UserID = item.UserID
};
using (dataContext.Connection)
{
return dataContext.ExecuteQuery<Item>(query, true);
}
}
使用SQL Profiler查看上述代码所执行的SQL语句，就会发现：
SELECT [t0].[ItemID], [t0].[Title], [t0].[CreateTime], [t0].[UserID]
FROM [dbo].[Item] AS [t0] WITH (NOLOCK)
WHERE [t0].[UserID] = @p0
ORDER BY [t0].[CreateTime] DESC
很漂亮。事实上只要我们需要，就可以在DbCommand对象生成的SQL语句上作任何修改（例如添加事务操作，容错代码等等），只要其执行出来的结果保持不变即可（事实上变又如何，如果您真有自己巧妙设计的话，呵呵）。
以上扩展所受限制
以上的扩展并非无可挑剔。由于Translate方法的特点，此类做法都无法充分发挥LINQ to SQL查询的所有能力——那就是所谓的“LoadWith”能力。
在LINQ to SQL中，默认会使用延迟加载，然后在必要的时候才会再去数据库进行查询。这个做法有时候会降低系统性能，例如：
List<Item> itemList = GetItems(1);
foreach (Item item in itemList)
{
foreach (ItemComment comment in item.Comments)
{
Console.WriteLine(comment.Content);
}
}
这种做法的性能很低，因为默认情况下每个Item对象的ItemComment集合不会被同时查询出来，而是会等到内层的foreach循环执行时再次查询数据库。为了避免不合适的Lazy Load降低性能，LINQ to SQL提供了DataLoadOptions机制进行控制：
public static List<Item> GetItems(int ownerId)
{
ItemDataContext dataContext = new ItemDataContext();
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Item>(item => item.Comments);
dataContext.LoadOptions = loadOptions;
var query = from item in dataContext.Items
where item.UserID == ownerId
orderby item.CreateTime descending
select item;
return query.ToList();
}
当我们为DataContext对象设置了LoadOptions并且指明了“Load With”关系，LINQ to SQL就会根据要求查询数据库——在上面的例子中，它将生成如下的SQL语句：
SELECT [t0].[ItemID], [t0].[Title], [t0].[Introduction], [t0].[UserID], [t0].[CreateTime], [t1].[ItemCommentID], [t1].[ItemID] AS [ItemID2], [t1].[Content], [t1].[UserID], [t1].[CreateTime] AS [CreateTime2], (
SELECT COUNT(*)
FROM [dbo].[ItemComment] AS [t2]
WHERE [t2].[ItemID] = [t0].[ItemID]
) AS [value]
FROM [dbo].[Item] AS [t0]
LEFT OUTER JOIN [dbo].[ItemComment] AS [t1] ON [t1].[ItemID] = [t0].[ItemID]
WHERE [t0].[UserID] = @p0
ORDER BY [t0].[CreateTime] DESC, [t0].[ItemID], [t1].[ItemCommentID]
相信大家已经了解Translate方法为何无法充分发挥LINQ to SQL的能力了。那么我们又该如何解决这个问题呢？如果您希望同时使用本文类似的扩展和Load With能力，可能就需要通过查询两次数据库并加以组合的方式来生成对象了——虽然查询了两次，但总比查询100次的性能要高。
