Linq with Different Where clauses and GroupBy

Question

I'm trying to have a generic method that get's different results from a query, based on a 'Search Object'I pass in.

Basically, based on certain conditions, I want to add Where clause conditions.

But I want to execute the query once. So, no toList() everywhere. I want to make the database do all the work.

My method is now like this:

 public async Task<List<TransactionBasicDto>> GetListAsync(TransactionListFilterRequest request)
        {
            using (var context = new MyContext())
            {
                var userAccount = context.UserAccounts.First(x => x.ExternalId == _jwt.HomeAccountId);

                var data = context.TransactionLine
                    .Include(x => x.Transaction)
                    .Include(x => x.Transaction.CreditAccount)
                    .Include(x => x.Transaction.DebitAccount)
                    .Where(x => x.Transaction.CreditAccount.UserAccount == userAccount && x.Transaction.Deleted == null);

                if (request.FromDate.HasValue)
                    data = data.Where(x => x.Transaction.Date >= request.FromDate);

                if (request.ToDate.HasValue)
                    data = data.Where(x => x.Transaction.Date <= request.ToDate);

                if (request.AccountId.HasValue)
                    data = data.Where(x => x.Transaction.CreditAccount.ExternalId == request.AccountId || x.Transaction.DebitAccount.ExternalId == request.AccountId);

                if (request.LimitResultCount.HasValue)
                    data = data.Take(request.LimitResultCount.Value);

                if (request.HistoricDaysFromNow.HasValue)
                    data = data.Where(x => x.Transaction.Date >= DateTime.UtcNow.AddDays(request.HistoricDaysFromNow.Value * -1));
                try
                {
                    var g = data
                    .GroupBy(x => new
                    {
                        Id = x.Transaction.ExternalId,
                        CreateOrder = x.Transaction.Id,
                        Date = x.Transaction.Date,
                        DebitAccountId = x.Transaction.DebitAccount.ExternalId,
                        DebitAccountName = x.Transaction.DebitAccount.Name,
                        DebitAccountTypeId = x.Transaction.DebitAccount.AccountTypeId,
                        CreditAccountId = x.Transaction.CreditAccount.ExternalId,
                        CreditAccountName = x.Transaction.CreditAccount.Name,
                        CreditAccountTypeId = x.Transaction.CreditAccount.AccountTypeId,
                        Description = x.Transaction.Description,
                    })
                    .Select(x => new
                    {
                        Id = x.Key.Id,
                        CreateOrder = x.Key.CreateOrder,
                        Date = x.Key.Date,
                        TransactionTypeId = WorkOutType(x.Key.DebitAccountTypeId, x.Key.CreditAccountTypeId),
                        DebitAccountId = x.Key.DebitAccountId,
                        DebitAccountName = x.Key.DebitAccountName,
                        CreditAccountId = x.Key.CreditAccountId,
                        CreditAccountName = x.Key.CreditAccountName,
                        Description = x.Key.Description,
                        Amount = x.Sum(y => y.Amount)
                    })
                    .OrderByDescending(x => x.Date)
                    .ThenByDescending(x => x.CreateOrder)
                    .ToList()
                    .Select(x => new TransactionBasicDto
                    {
                        Amount = x.Amount,
                        Date = x.Date,
                        CreditAccountId = x.CreditAccountId,
                        CreditAccountName = x.CreditAccountName,
                        DebitAccountId = x.DebitAccountId,
                        DebitAccountName = x.DebitAccountName,
                        Description = x.Description,
                        Id = x.Id,
                        TransactionTypeId = x.TransactionTypeId
                    })
                    .ToList();

                    return g;
                }
                catch (Exception e)
                {
                    var b = e;
                    throw;
                }


            }

Problem is that my GroupBy fails now. When I remove all the data = data... and have the GroupBy right after the first .Where, it works.

But it seems when I separate it and add the conditional wheres, it breaks with the error:

Value does not fall within the expected range

This happens within the try/catch.

It's as if I am not allowd to split the statement.

Before, I had:

.Where(x => x.Transaction.CreditAccount.UserAccount == userAccount && x.Transaction.Deleted == null)
.GroupBy(x => new
                    {
                        Id = x.Transaction.ExternalId,
                        CreateOrder = x.Transaction.Id,

And that worked. But splitting the statement to add those where Where clauses causes this error. Am I doing it wrong?

at System.SharedTypeExtensions.GetSequenceType(Type type) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel) at Remotion.Linq.Clauses.MainFromClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel) at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ProjectionExpressionVisitor.VisitSubQuery(SubQueryExpression expression) at Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression) at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection1 nodes, String callerName) at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.VisitNew(NewExpression newExpression) at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel) at Remotion.Linq.Clauses.SelectClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel) at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel) --- End of stack trace from previous location where exception was thrown --- at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger1 logger, Type contextType) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass13_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Remotion.Linq.QueryableBase1.GetEnumerator() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at DataAccess.Concrete.TransactionData.GetListAsync(TransactionListFilterRequest request) in C:\Storage\Software Repositories\Personal\AccuFinanceV2Backend\ Backend\04. DataAccess\Concrete\TransactionData.cs:line 374

Answer 1

Trying an approach where the Where predicate is built up completely before the first Where is called.

After some research I came across multiple incarnation of the PredicateBuilder

Reference A universal PredicateBuilder

Reference Dynamically Composing Expression Predicates

and modified it to suit my needs.

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

    public static Expression<Func<T, bool>> False<T>() { return x => false; }

    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right) {
        return left.Compose(right, Expression.OrElse);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right) {
        return left.Compose(right, Expression.AndAlso);
    }

    static Expression<T> Compose<T>(this Expression<T> left, Expression<T> right, Func<Expression, Expression, Expression> merge) {
        var p1 = left.Parameters.First();//Only handling with one parameter
        // replace parameters in the second lambda expression with the parameters in the first
        var rightBody = ParameterRebinder.Replaceparameter(p1, right.Body);
        // create a merged lambda expression with parameters from the first expression
        return Expression.Lambda<T>(merge(left.Body, rightBody), left.Parameters);
    }

    private class ParameterRebinder : ExpressionVisitor {
        private ParameterExpression source;

        public ParameterRebinder(ParameterExpression source) {
            this.source = source;
        }

        internal static Expression Replaceparameter(ParameterExpression source, Expression root) {
            return new ParameterRebinder(source).Visit(root);// (Expression<TOutput>)VisitLambda(root);
        }

        protected override Expression VisitParameter(ParameterExpression node) {
            // Replace non matching parameters, visit other params as usual.
            return node.Name != source.Name ? source : base.VisitParameter(node);
        }
    }
}

With that the predicate is constructed first based on the desired conditions...

//...

var predicate = PredicateBuilder.Create<TransactionLine>(x => x.Transaction.CreditAccount.UserAccount == userAccount && x.Transaction.Deleted == null);

if (request.FromDate.HasValue)
    predicate = predicate.And(x => x.Transaction.Date >= request.FromDate);

if (request.ToDate.HasValue)
    predicate = predicate.And(x => x.Transaction.Date <= request.ToDate);

if (request.AccountId.HasValue)
    predicate = predicate.And(x => x.Transaction.CreditAccount.ExternalId == request.AccountId || x.Transaction.DebitAccount.ExternalId == request.AccountId);

if (request.HistoricDaysFromNow.HasValue)
    predicate = predicate.And(x => x.Transaction.Date >= DateTime.UtcNow.AddDays(request.HistoricDaysFromNow.Value * -1));

//...

And then applied to the target collection in one call

//...

var data = context.TransactionLine
    .Include(x => x.Transaction)
    .Include(x => x.Transaction.CreditAccount)
    .Include(x => x.Transaction.DebitAccount)
    .Where(predicate);

if (request.LimitResultCount.HasValue)
    data = data.Take(request.LimitResultCount.Value);

try{
    var g = data
        .GroupBy(x => ...


//...