How can I pass a Predicate<T> to the Where() method in Linq to SQL?

I have the following model:

 +--------+
 | Folder |
 +--------+
     | 1
     |
     | *
+----------+         +---------+
| WorkItem |---------| Project |
+----------+ *     1 +---------+

I need to retrieve a list of Folders with the current count of WorkItems.

If I have specified a Project, then I only want the count of WorkItems in each Folder that are associated with the specified Project.

If no Project is specified, it should return the total WorkItem count.

I have the following Linq to SQL code:

public interface IWorkItemCriteria {
    int? ProjectId { get; }
}

public static IQueryable<Folder> GetFoldersWithItemCounts(IWorkItemCriteria criteria) {
    var results = from folder in dataContext.Folders
        select new {
              Folder = folder,
              Count = folder.WorkItems.Count()
    };
    return(results);
}

The problem is - I want to filter the work items that are being counted.

This works:

    var results = from folder in dataContext.Folders
        select new {
              Folder = folde开发者_StackOverflow中文版r,
              Count = folder.WorkItems.Where(item => item.ProjectId == criteria.ProjectId).Count()
    };

but I can't get it to use any kind of dynamic predicate / expression. The syntax I'm trying to use is:

    var results = from folder in dataContext.Folders
        select new {
              Folder = folder,
              Count = folder.WorkItems.Where(filter).Count()
    };

I've tried

Predicate<WorkItem> filter = (item => item.ProjectId == criteria.ProjectId);

and

Expression<Func<WorkItem, bool>> filter = (item => item.ProjectId == criteria.ProjectId)

neither of which will compile - gives The type arguments for method 'System.Linq.Enumerable.Where<TSource> (System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

I've tried

Func<WorkItem, bool> filter = (item => item.ProjectId == criteria.ProjectId);

which builds, but then fails with 'Unsupported overload used for query operator 'Where'.'

I'm going to need to add additional properties to the IWorkItemCriteria interface, so the ability to dynamically construct a predicate that can be cleanly translated by Linq to SQL is pretty important.

Any ideas?

I think your problem is that you'd like to reference one expression (filter) from within a second expression (the one being passed to results.Where.Select()), and that the LINQ to SQL IQueryable provider doesn't know how to pull in the value of that.

What you're going to have to do is compose the Select expression from scratch in order to inline the expression that's your dynamic filter. This might be extra hard with anonymous types, i'm not sure. I just found the following article, it seems to explain this pretty well: http://www.codeproject.com/KB/linq/rewrite_linq_expressions2.aspx

Something I grabbed off of MSDN: the idea is that you just point it to an existing function:

using System;

public class GenericFunc

{
   public static void Main()
   {
      Func<string, string> convertMethod = UppercaseString;
   }

   private static string UppercaseString(string inputString)
   {
      return inputString.ToUpper();
   }
}

I'm sure there are also other ways. I remember doing something like

new Func<WorkItem, bool>((item => item.projectId == criteria.ProjectId))

But I'm not sure. anymore, I'm quite sure you need the "new" though, hope this helps :)