非常复杂的LINQ（到SQL）查询示例 [英] Really complex LINQ (to SQL) query example

问题描述

我们正在考虑为ORMBattle.NET增加更多的LINQ的测试，但没有更多的想法。所有的LINQ测试有检查常见的LINQ功能：

• 在任何测试必须通过对LINQ到了IEnumerable
• 在任何测试，必须有至少一个ORM，就可以通过（实际上它并不重要，如果它被列为@ ORMBattle与否）。

目前的LINQ测试序列的目标是自动的计算的LINQ实施覆盖率得分

prerequisites：

• $ C $的C测试的LINQ to SQL是这里。这必须得到一个什么样的已经涵盖了一些想象力。
• 测试的其他工具（实际上它们是由的这个T4模板）是的这里。

如果您有什么可以被添加有任何想法，请分享。我一定会接受的任意的例子，满足上述要求，并可能LINQ查询 - 一些好的想法与改进测试套件，即的可以执行的（所以例如，如果你会建议我们手动研究翻译的质量，这是行不通的，因为我们无法自动执行此）。

解决方案

1. 防爆pression.Invoke 的SUBEX pressions;在3.5SP1工程LINQ到SQL和LINQ到对象，但不是EF（对于的IEnumerable＆LT; T＆GT; ，呼叫 .AsQueryable（ ）在前）：

    防爆pression＆LT; Func键＆LT;客户，布尔＆GT;＆GT; preD1 =卡斯特=＆GT; cust.Country ==英国;
       防爆pression＆LT; Func键＆LT;客户，布尔＆GT;＆GT; preD2 =卡斯特=＆GT; cust.Country ==法国;
       变种参数=实施例pression.Parameter（typeof运算（客户），×）;
       VAR最终=前pression.Lambda＆LT; Func键＆LT;客户，布尔＆GT;＆GT;（
           防爆pression.OrElse（
               防爆pression.Invoke（preD1，参数），
               防爆pression.Invoke（preD2，参数）
           ），参数）;
       使用（VAR CTX =新DataClasses1DataContext（））
       {
           ctx.Log = Console.Out;
           INT ukPlusFrance = ctx.Customers.Count（最终）;
       }
    

   例如LINQ到SQL输出（火花EF爆炸）：

     SELECT COUNT（*）AS [值]
   FROM [DBO]。[客户] AS [T0]
   WHERE（[T0] [国家] = @ P0）或（[T0] [国家] = @ P1）
    -  @ P0：输入为nvarchar（尺寸= 2; preC = 0;规模= 0）[UK]
    -  @ P1：输入为nvarchar（尺寸= 6; preC = 0;规模= 0）[法国]
    

2. 身份管理器短路而不往返 - 即

     VAR OBJ = ctx.Single（X =＆GT; x.Id == ID）;
   VAR OBJ = ctx.Where（X =＆GT; x.Id == ID）。单（）;
    

   等应的没有的需要到数据库中，如果一个对象与该身份已经被物化并存储在身份管理;也适用于首先，的SingleOrDefault ， FirstOrDefault 。见LINQ到SQL（也<一href="https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=340036">here和<一href="https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=362313">here;您可以通过将验证 .LOG ）;例如：

    使用（VAR CTX =新DataClasses1DataContext（））
   {
       ctx.Log = Console.Out;
       VAR第一= ctx.Customers.First（）;
       字符串ID = first.CustomerID;
       Console.WriteLine（任何更多的游？）;
       变种firstDup = ctx.Customers.First（X =＆GT; x.CustomerID == ID）;
       Console.WriteLine（的ReferenceEquals（第一，firstDup））; // 真正
       Console.WriteLine（证明还连着）;
       诠释计数= ctx.Customers.Count（）;
   }
    

   日志输出仅显示只有两趟;一个得到所述对象的第一时间，和一个用于计数;这也显示了相同的对象引用是由materializer返回：

     SELECT TOP（1）[T0]。[客户]，[T0]。[公司名称]，[T0] [联系人姓名]，[T 0]。[
   联系人头衔]，[T0]。[地址]，[T0] [市]，[T0]。[区域]，[T0]。[邮政code]，[吨
   0] [国家]，[T0]。[电话]，[T0]。[传真]
   FROM [DBO]。[客户] AS [T0]
    - 背景：sqlProvider的（SQL2008）型号：AttributedMetaModel体形：3.5.30729.492
   6
   
   
   任何更多的旅行？
   真LT; ====这是对象引用的平等，而不是有什么更多的旅行
   证明还连着
   SELECT COUNT（*）AS [值]
   FROM [DBO]。[客户] AS [T0]
    - 背景：sqlProvider的（SQL2008）型号：AttributedMetaModel体形：3.5.30729.492
   6
    

3. UDF支持;一个简单的例子，也适用于LINQ到对象：

    部分类MyDataContext {
        [功能（NAME =NEWID，IsComposable =真）
        公共的Guid随机（）{返回Guid.NewGuid（）;}
   }
    

   ，然后才能通过 X =＆GT; ctx.Random（）;例如：

    使用（VAR CTX =新DataClasses1DataContext（））
   {
       ctx.Log = Console.Out;
       VAR anyAtRandom =（从卡斯特在ctx.Customers
                          排序依据ctx.Random（）
                          选择卡斯特）。首先（）;
   }
    

   与输出：

     SELECT TOP（1）[T0]。[客户]，[T0]。[公司名称]，[T0] [联系人姓名]，[T0] [联系人头衔]，[ T0]。[地址]，[T0] [市]，[T0]。[区域]，[T0]。[邮政code]，[T0] [国家]，[T0]。[电话] [T0]。[传真]
   FROM [DBO]。[客户] AS [T0]
   ORDER BY NEWID（）
    

4. 如果我当时的感觉的真正的邪恶的递归拉姆达;可能不值得支持这个的任意的方式......同样，4.0 EX pression（DLR）的节点类型。

We're thinking about adding more LINQ tests for ORMBattle.NET, but have no more ideas. All LINQ tests there are checking common LINQ functionality:

• Any test must pass on LINQ to IEnumerable
• For any test, there must be at least one ORM, on it passes (actually it doesn't matter if it is listed @ ORMBattle or not).

Currently the goal of LINQ test sequence is to automatically compute LINQ implementation coverage score.

Prerequisites:

• Code of tests for LINQ to SQL is here. This must give some imagination of what's already covered.
• Tests for other tools (actually they're generated by this T4 template) are here.

If you have any ideas on what can be added there, please share them. I'll definitely accept any example of LINQ query that satisfies above requirements, and possibly - some good idea related to improvement of test suite, that can be implemented (so e.g. if you'd suggest us to manually study the quality of translation, this won't work, because we can't automate this).

解决方案

1. Expression.Invoke for subexpressions; works on LINQ-to-SQL and LINQ-to-Objects, but not EF in 3.5SP1 (for IEnumerable<T>, call .AsQueryable() first):

       Expression<Func<Customer, bool>> pred1 = cust=>cust.Country=="UK";
       Expression<Func<Customer, bool>> pred2 = cust=>cust.Country=="France";
       var param = Expression.Parameter(typeof(Customer), "x");
       var final = Expression.Lambda<Func<Customer, bool>>(
           Expression.OrElse(
               Expression.Invoke(pred1, param),
               Expression.Invoke(pred2, param)
           ), param);
       using (var ctx = new DataClasses1DataContext())
       {
           ctx.Log = Console.Out;
           int ukPlusFrance = ctx.Customers.Count(final);
       }
   

   example LINQ-to-SQL output (EF explodes in sparks):

   SELECT COUNT(*) AS [value]
   FROM [dbo].[Customers] AS [t0]
   WHERE ([t0].[Country] = @p0) OR ([t0].[Country] = @p1)
   -- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [UK]
   -- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [France]
   

2. identity-manager short-circuit without roundtrip - i.e.

   var obj = ctx.Single(x=>x.Id == id);
   var obj = ctx.Where(x=>x.Id == id).Single();
   

   etc should not need to go to the database if an object with that identity has already been materialized and stored in the identity-manager; applies also to First, SingleOrDefault, FirstOrDefault. See LINQ-to-SQL (also here and here; you can verify by attaching to .Log); example:

   using (var ctx = new DataClasses1DataContext())
   {
       ctx.Log = Console.Out;
       var first = ctx.Customers.First();
       string id = first.CustomerID;
       Console.WriteLine("Any more trips?");
       var firstDup = ctx.Customers.First(x=>x.CustomerID==id);
       Console.WriteLine(ReferenceEquals(first, firstDup)); // true
       Console.WriteLine("Prove still attached");
       int count = ctx.Customers.Count();
   }
   

   log output shows only only two trips; one to get the object the first time, and one for the count; it also shows the same object reference is returned by the materializer:

   SELECT TOP (1) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[
   ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t
   0].[Country], [t0].[Phone], [t0].[Fax]
   FROM [dbo].[Customers] AS [t0]
   -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.492
   6
   
   
   Any more trips?
   True <==== this is object reference equality, not "are there any more trips"
   Prove still attached
   SELECT COUNT(*) AS [value]
   FROM [dbo].[Customers] AS [t0]
   -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.492
   6
   

3. UDF support; for a simple example that also works for LINQ-to-Objects:

   partial class MyDataContext {
        [Function(Name="NEWID", IsComposable=true)] 
        public Guid Random()  { return Guid.NewGuid();}
   }
   

   and then order by x => ctx.Random(); example:

   using (var ctx = new DataClasses1DataContext())
   {
       ctx.Log = Console.Out;
       var anyAtRandom = (from cust in ctx.Customers
                          orderby ctx.Random()
                          select cust).First();
   }
   

   with output:

   SELECT TOP (1) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[        ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
   FROM [dbo].[Customers] AS [t0]
   ORDER BY NEWID()
   

4. If I was feeling truly evil, recursive lambda; probably not worth supporting this in any way... likewise, 4.0 expression (DLR) node-types.

这篇关于非常复杂的LINQ（到SQL）查询示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！