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;

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

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

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

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