CODEWITHSUNDEEP
c#sqllinqlinq-to-sql
Mike H.
Mike H.

Reputation: 1751

Optimize or ditch LINQ query?

So I have a LINQ (to SQL) query that pulls information from a database into a grid. There is a function to aggregate the grid data based on the current filter parameters which will sum the amount of recurring "X"'s in the grid data.

For instance, lets assume the grid displays customer vists to a grocery store. The original data may show the follow:

Date     | Name       | No. Prod   | Total $
--------------------------------------------
01/02/13 | Customer A | 4 products | $23.00 
01/02/13 | Customer B | 2 products | $3.26
01/02/13 | Customer C | 7 products | $47.42
01/16/13 | Customer A | 3 products | $26.22

Clicking the summation function for the clients column will display the following grid data:

Cnt| Name       | Tot. Prod  | Total $  
--------------------------------------
2  | Customer A | 7 products | $49.22 
1  | Customer B | 2 products | $3.26
1  | Customer C | 7 products | $47.42

My problem is that I am doing the summation logic in a LINQ query. I assumed this would be fast...but it is just the opposite. Here is a sample.

Expression<Func<OrdersView, bool>> filter;
filter = m => m.RecordCreated >= fromDate && m.RecordCreated <= toDate && m.DepartmentID == _depID;

var ClientAggOrders = dataContext.OrdersView
    .Where(filter)
    .GroupBy(m => m.Name)
    .Select(gr => new
        {
            Name = gr.Key,
            Count = gr.Where(s => s.ID != null).Count(),
            id = gr.Select(s => s.ID),
            S1 = gr.Sum(s => s.Tare < s.Gross ? s.Tare : s.Gross),
            S2 = gr.Sum(s => s.Tare < s.Gross ? s.Gross : s.Tare),
            NetWeight = gr.Sum(s => s.NetWeight),
            Price = gr.Sum(s => s.NetPrice)
        }
    ).ToList();

My question is, why is this such bad practice? LINQ allows for these expressions in the SELECT clause, but the time it takes to execute is beyond absurd to the point where I don't see it being beneficial in any real world scenario.

Am I using LINQ wrong and should I just move my logic outside of the query or can this be optimized and done within LINQ properly? Thanks for any advice!

Upvotes: 2

Views: 158

Answers (2)

Edward
Edward

Reputation: 8596

You can use LINQPad to see the SQL that is generated.

Because of the way LINQ to SQL works, id = gr.Select(s => s.ID) causes a subquery to be executed for every group. Remove this, and instead get the ID+Name in your GroupBy: .GroupBy(m => new{m.ID, m.Name})

You should find that the generated SQL will now be a single statement, instead of the main statement plus a statement for each group.

Upvotes: 4

Simone S.
Simone S.

Reputation: 1916

Perform grouping only in memory? Solve your problem?

var ordersView = 
    dataContext.OrdersView
    .Where(m => m.RecordCreated >= fromDate && m.RecordCreated <= toDate && m.DepartmentID == _depID)
    .ToList(); 

var ClientAggOrders = ordersView.GroupBy(m => m.Name).Select(gr => new
{
    Name = gr.Key,
    Count = gr.Where(s => s.ID != null).Count(),
    id = gr.Select(s => s.ID),
    S1 = gr.Sum(s => s.Tare < s.Gross ? s.Tare : s.Gross),
    S2 = gr.Sum(s => s.Tare < s.Gross ? s.Gross : s.Tare),
    NetWeight = gr.Sum(s => s.NetWeight),
    Price = gr.Sum(s => s.NetPrice)
}).ToList();

Upvotes: 0

Related Questions