C# Driver- LINQ - Aggragate - Contains in Int[]

Question

Mongo Driver Version: 2.2.4.26

I have the following query that performs some aggregations. If I remove the Contains predicate from my Where clause, the query executes successfully.

I would like to reduce and perform the aggregation on the server before realizing the data into the client memory. The issue I am encountering is when I use Contains in my Where clause, I get the following exception:

$project or $group does not support Sum({document}{ForecastQuantity}).

I would prefer to stay in linq implementation vs. the native if possible:

public class Forecast
{
    public int MarkdownGroupId { get; set; }
    public List<ForecastData> ForecastArray { get; set; }
...
}

public class ForecastData
{
    public int MarkdownNumber { get; set; }
    public double ForecastQuantity { get; set; }
...
}

public class Flat
{
    public int MarkdownGroupId { get; set; }
    public int MarkdownNumber { get; set; }
    public double SalesQuantity { get; set; }

...
}

public class FlatProjection
{
    public int MarkdownGroupId { get; set; }
    public int MarkdownNumber { get; set; }
    public double SalesQuantity { get; set; }

...
}

int[] groupIds = new int[]{ 1,2,3 };
var forecastProductDay = _mdb.GetCollection<Forecast>(collectionName);

        var forecastProductProjections = forecastProductDay
            .AsQueryable()
            .Where(x => groupIds.Contains(x.MarkdownGroupId))
            .SelectMany(x => x.ForecastArray, (x, fa) => new Flat        
            {
                MarkdownGroupId = x.MarkdownGroupId,
                MarkdownNumber = fa.MarkdownNumber,
                ForecastQuantity = fa.ForecastQuantity,
                ...
            })
            .GroupBy(key => new {key.MarkdownGroupId, key.MarkdownNumber})
            .Select(g => new FlatProjection
            {
                MarkdownGroupId = g.Key.MarkdownGroupId,
                MarkdownNumber = g.Key.MarkdownNumber,
                SalesQuantity = g.Sum(y => y.ForecastQuantity),
                ...
            })
            .OrderBy(x => x.MarkdownGroupId)
            .ThenBy(x => x.MarkdownNumber)
            .ToList();

This is probably an error in the driver. Does it work if you convert the expression to `Where(x => x.MarkdownGroupId==1 || x.MarkdownGroupId==2 || x.MarkdownGroupId==3)`? — Sergey Kalinichenko, Aug 14 '17 at 16:47
Yes. That works Converting to Where(x => x.MarkdownGroupId==1 || x.MarkdownGroupId==2 || x.MarkdownGroupId==3) — Lui_B, Aug 14 '17 at 17:06
How many IDs do you have in the list? Perhaps you can create an OR list dynamically to work around this issue. — Sergey Kalinichenko, Aug 14 '17 at 17:08
I am not certain the proper way to build the predicate to pass into the my Where clause. I attempted to use [link](http://www.albahari.com/nutshell/predicatebuilder.aspx) , but I get exception: {"Unsupported filter: Invoke(x => (x.MarkdownGroupId == 1), {document})."} Also tried using Any(), but with no luck: public static Expression> InList(int[] ints) { return x => ints.Any(i => i == x.MarkdownGroupId); } — Lui_B, Aug 14 '17 at 18:44
I got it to work after I put .Compile() in expression. .Where(predicate.Compile()) — Lui_B, Aug 14 '17 at 18:58

score 2 · Answer 1 · answered Aug 14 '17 at 19:05

Since the driver does not like Contains() or Any(). I had the build the expression dynamically using the PredicateBuilder.

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

List ints = new List() {1,2,3};

        var predicate = PredicateBuilder.False<Forecast>();
        foreach (var i in ints)
        {
            predicate = predicate.Or(x => x.MarkdownGroupId == i);
        }

var forecastProductDay = _mdb.GetCollection(collectionName);

    var forecastProductProjections = forecastProductDay
        .AsQueryable()
        .Where(predicate.Compile())
        .SelectMany(x => x.ForecastArray, (x, fa) => new Flat        
        {
            MarkdownGroupId = x.MarkdownGroupId,
            MarkdownNumber = fa.MarkdownNumber,
            ForecastQuantity = fa.ForecastQuantity,
            ...
        })
        .GroupBy(key => new {key.MarkdownGroupId, key.MarkdownNumber})
        .Select(g => new FlatProjection
        {
            MarkdownGroupId = g.Key.MarkdownGroupId,
            MarkdownNumber = g.Key.MarkdownNumber,
            SalesQuantity = g.Sum(y => y.ForecastQuantity),
            ...
        })
        .OrderBy(x => x.MarkdownGroupId)
        .ThenBy(x => x.MarkdownNumber)
        .ToList();

C# Driver- LINQ - Aggragate - Contains in Int[]

1 Answers1