在LINQ to SQL中使用Translate方法以及修改查询用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语句上作任何修改（例如添加事务操作，容错代码等等），只要其执行出来的结果保持不变即可（事实上变又如何，如果您真有自己巧妙设计的话，呵呵）。