在LINQ to SQL中运用Translate办法以及修正查询用SQL51CTO博客 - 娱乐之横扫全球

在LINQ to SQL中运用Translate办法以及修正查询用SQL51CTO博客

2019-01-04 21:32:10 | 作者: 语海 | 标签: 办法,运用,查询 | 浏览: 522

版权声明:原创著作,答应转载,转载时请必须以超链接办法标明文章原始出处(以下两个链接可任选其一)、作者信息和本声明。否则将追查法律责任。
  • [url]http://jeffreyzhao.cnblogs.com/archive/2008/02/19/using-translate-method-and-modify-command-text-before-query-in-linq-to-sql.html[/url]
  现在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次的功能要高。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    sysbench对mysql压力测验ITeye

    测验,线程,基准
  • 2

    Sql Server数据库跨库查询ITeye

    数据库,树立,链接
  • 3

    PowerDesigner 15 设置identityITeye

    规划,数据库,编码
  • 4

    [MySQL]ITeye

    一个,或许,这个
  • 5

    hdfs常用命令ITeye

    文件,目录,途径
  • 6

    orace11gR2 启用日志归档ITeye

    备份,数据库,需求
  • 7

    Mysql高可用架构ITeye

    可用,架构,计划
  • 8

    hbase 全体介绍ITeye

    存储,经过,文章
  • 9

    db2move 指令无法导出表ITeye

    导出,数据,字符
  • 10