Mike Taulty's Blog
Bits and Bytes from Microsoft UK
Messing with Dynamic LINQ Queries

Blogs

Mike Taulty's Blog

Elsewhere

Archives

Mike was chatting to me about how you'd take something like this ( against LINQ to SQL and the Northwind database );

 using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        string[] values = { "A", "B", "C" };

        var query =
          from c in ctx.Customers
          select c;

        foreach (string s in values)
        {
          string t = s; // Care to avoid capturing the same value 3 times...
          query = query.Where(c => c.CompanyName.Contains(t));
        }
        query.ToList();  
      }
      Console.ReadLine();

which produces;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

and write a new extension method Where which could take the list of arguments ("A", "B", "C") and a single Lambda and just "figure it out".

I spent ages on this writing reams of code but in the end I think I chopped it down to;

  public static class QueryExtensions
  {
    public static IQueryable<T> Where<T,S>(this IQueryable<T> query, 
      Expression<Func<T,S,bool>> func, params S[] args)
    {      
      foreach (S s in args)
      {
        MethodCallExpression mce = func.Body as MethodCallExpression;
        Expression e = MethodCallExpression.Call(mce.Object, mce.Method, Expression.Constant(s));
        query = query.Where<T>(Expression.Lambda<Func<T, bool>>(e, func.Parameters[0]));
      }
      return (query);
    }
  }

and all I'm really attempting to do there is to take the Expression which will have 2 parameters and reduce it down to an Expression which has 1 parameter by hard-wiring the 2nd parameter with the current value of the loop variable "s" into the method call expression itself as a constant. Note sure if that's quite right but I can then use it as;

      using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        var query =
          from c in ctx.Customers.Where( (c, s) => c.CompanyName.Contains(s), "A", "B", "C")
          select c;

        query.ToList();  
      }
      Console.ReadLine();

which seems to produce;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

which looks alright. I'm not sure that it's universally useful in that Where is basically AndWhere so whilst Contains might work here there perhaps aren't that many other methods that would make sense.

Regardless, it took me quite a while and I still don't really understand building dynamic expressions at all properly :-)

Some older bits about dynamic queries and the such like;

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/11/10/9911.aspx

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/15/9489.aspx

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9490.aspx

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9492.aspx


Posted Thu, Sep 11 2008 9:08 AM by mtaulty
Filed under: , , ,

Comments

Dew Drop - September 12, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Drop - September 12, 2008 | Alvin Ashcraft's Morning Dew
on Fri, Sep 12 2008 6:45 AM
WMOC#20 - Moving from .net Framework 2.0 to 3.5 - Service Endpoint wrote WMOC#20 - Moving from .net Framework 2.0 to 3.5 - Service Endpoint
on Thu, Sep 25 2008 7:00 PM