Really complex LINQ (to SQL) query example

Question

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).

Answer 1

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.