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

Question

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 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,

   };

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.

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

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

edited May 23 '17 at 12:04

Community

1
1

answered Dec 22 '10 at 10:55

Richard Friend

15,800
1
42
60

if i cover my predicates with Expression, like this: public static Expression> TotalItemsPredicate, it throws the exact same exception. – mrblack Dec 22 '10 at 13:00

score 1 · Answer 2 · answered Dec 22 '10 at 11:38

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,

       };

+1. this is how i approach my filter extension methods. in some cases, even option for the generic T version as well (where appropriate) — jim tollan, Dec 22 '10 at 12:50
Yeah, this will work, but i have been wrangling around with my issue for almost 8 hrs and now i really want to find out the answer — mrblack, Dec 22 '10 at 13:09
@mrblack: The reason you're getting the exception is that the LINQ provider is unable to reverse-compile your function to translate it to SQL. This is why you need to return an expression tree (if the LINQ provider supports it) or use extension methods. I would prefer the extension method approach, though using `IQueryable` instead of `IEnumerable`. — Stephen Cleary, Dec 22 '10 at 14:54

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

2 Answers2