LINQ2SQL: Passing predicate to the .Count(Func<T>,bool) in a subquery

I have a question about predicates in linq2sql. What am i trying to do is to separate conditions into a static class and then use them in my queries. i have created a static class with some Functions:

public static class CountingFilters
    {
        public static  Func<CountingItem, bool> TotalItemsPredicate
            = ci =>
                      !ci.Deleted && ci.OdinProduct != null &&
                      ((ci.OdinProduct.NotOriginal == null && !ci.OdinProduct.Deleted)
                       || ((ci.OdinProduct.NotOriginal == ci.OdinProduct.GUID) && ci.OdinProduct.Deleted))
                      && (!ci.OdinProduct.Temp);

        public static  Func<CountingItem, bool> AlreadyCountedPredicate
            = ci =>
                TotalItemsPredicate(ci) && ci.CountedAmount.HasValue;
     }

If i use it like this:

var count = CountingFacade.GetCountingItems().Count(CountingFilters.TotalItemsPredicate);

Everything works correctly, and i get the result. But when i try to create projections for binding to gridview, like this:

var result = from f in countinglist
   let alreadyCounted = f.CountingItems.Count(CountingFilters.AlreadyCountedPredicate)
   let total = f.CountingItems.Count(CountingFilters.TotalItemsPredicate)
   select n开发者_运维知识库ew CountingProjection
   {
          AlreadyCountedProducts = alreadyCounted,
          Description = f.Description,
          NumberOfProducts = total,
          PlannedDate = f.PlannedDate.Value,
          Site = f.Site,
          Status = f.Status,
          Type = f.Type,
          GUID = f.GUID,

   };

It throws exception "Unsupported overload used for query operator 'Count'." For some reason my predicates are not being translated to SQL, as far as i understand... Can anyone tell me, what am i doing wrong? Thanks in advance.

You would normally need to return Expression< Func<>> for them to be translated into SQL.

Take a look at predicate builder

and this is worth a look

Another way is to use Extension methods. It will save you a lot of time if you dont wont to dig into Expression and etc. Your code will look following:

public static class CountingFilters
{
     public static int TotalItems(this IEnumerable<CountingItem> source)
     {
         return source.Count(ci => !ci.Deleted && ci.OdinProduct != null &&
                          ((ci.OdinProduct.NotOriginal == null && !ci.OdinProduct.Deleted)
                           || ((ci.OdinProduct.NotOriginal == ci.OdinProduct.GUID) && ci.OdinProduct.Deleted))
                          && (!ci.OdinProduct.Temp));
     }

     public static int AlreadyCounted(this IEnumerable<CountingItem> source)
     {
         return source.Count(ci => !ci.Deleted && ci.OdinProduct != null &&
                          ((ci.OdinProduct.NotOriginal == null && !ci.OdinProduct.Deleted)
                           || ((ci.OdinProduct.NotOriginal == ci.OdinProduct.GUID) && ci.OdinProduct.Deleted))
                          && (!ci.OdinProduct.Temp) && ci.CountedAmount.HasValue);
     }
}

    var result = from f in countinglist
       let alreadyCounted = f.CountingItems.AlreadyCounted()
       let total = f.CountingItems.TotalItems()
       select new CountingProjection
       {
              AlreadyCountedProducts = alreadyCounted,
              Description = f.Description,
              NumberOfProducts = total,
              PlannedDate = f.PlannedDate.Value,
              Site = f.Site,
              Status = f.Status,
              Type = f.Type,
              GUID = f.GUID,

       };