CODEWITHSUNDEEP
c#linqentity-framework-5
Yargicx
Yargicx

Reputation: 1734

Linq Dynamic Query With Group By

I need extra where clause for my Linq query. For example if customer choose a date filter so i need to date filter to my query etc... When i try to myQuery.Where predicate there is visible just group by's field.

How can i append new where condition to my query. 

//for example i need dynamically append o.OrderDate==Datetime.Now or another where clause

var myQuery =(from o in _db.Orders
join l in _db.OrderLines.Where(x => x.ParaBirimi == model.ParaBirimi) on o.orderId equals
    l.OrderId
where o.OrderDate.Value.Year == year1 
group o by new {o.OrderDate.Value.Month}
into g
select
    new
    {
        Month = g.Key.Month,
        Total = g.Select(t => t.OrderLines.Sum(s => s.OrderTotal)).FirstOrDefault()
    });

Upvotes: 0

Views: 534

Answers (1)

xanatos
xanatos

Reputation: 111820

You are too late at the end of the query to add new Where. You have already grouped the data, and projected it, removing nearly all the fields.

Try:

var baseQuery = from o in _db.Orders
                join l in _db.OrderLines.Where(x => x.ParaBirimi == model.ParaBirimi) on o.orderId equals l.OrderId
                where o.OrderDate.Value.Year == year1
                select new { Order = o, OrderLine = l };

if (something)
{
     baseQuery = baseQuery.Where(x => x.Order.Foo == "Bar");
}

var myQuery = (from o in baseQuery
               group o by new { o.Order.OrderDate.Value.Month }
                   into g
                   select
                       new
                       {
                           Month = g.Key.Month,
                           Total = g.Sum(t => t.OrderLine.OrderTotal)
                       });

Clearly you can have multiple if. Each .Where() is in && (AND) with the other conditions.

Note how the result of the join is projected in an anonymous class that has two properties: Order and OrderLine

Upvotes: 2

Related Questions