LINQ to SQL Query Issue in Entity Framework

Question

I have the following query working fine in LINQ to SQL. Now I want to change it to Entity Framework

var _sale = from emp in setupEmployees
            join sales in vwSaleTargets on emp.EmployeeID equals sales.EmployeeID
            join price in vwPeriodPricings
               on new { sales.SKUID, sales.PeriodID } 
               equals new { SKUID = (int?)price.SKUID, PeriodID = (int?)price.PeriodID }
            join sk in setupSKUs on sales.SKUID equals sk.SKUID
            join br in setupBrands on sk.BrandID equals br.BrandID
            where emp.EmployeeID == 123 && sales.StartDate.Year == 2012 
            select new { emp, sales, price, sk, br };

var lstSale = _sale.ToList(); //to avoid n+1 queries in case of grouping
var sale2 = from x in lstSale
            group x by new { x.sk, x.emp } into grouping
            select new 
            {
                 EmployeeName = grouping.Key.emp.EmployeeName,
                 SKUID = grouping.Key.sk.SKUID,
                 SKUName = grouping.Key.sk.Title,
                 MonthSale =(double?)grouping
                          .Where(x => x.sales.StartDate.Month == 2 && 
                                      x.sales.StartDate.Year == 2012)
                          .Select(t=>t.sales.SalesQuantity)
                          .Sum(t=>t.Value)?? 0,
                 MonthSaleValue = (double?)grouping
                          .Where(x => x.sales.StartDate.Month == 2 && 
                                      x.sales.StartDate.Year == 2012)
                          .Sum(x => x.sales.SalesQuantity * x.price.ExFactoryPrice)  
                             ?? 0,
            };
Console.WriteLine(sale2.OrderBy(x => x.SKUName).ToList());

In Entity Framework It is giving me result Like this

Name SKUID SKUName MonthSale MonthSaleValue 
EMP1  36    SKU1     113     61375.95 
EMP1  17    SKU2     113     6656.83 
EMP1  18    SKU3     113     9984.68 
EMP1  19    SKU4     113     15169.12

In L2S I am getting me correct result like this

Name SKUID SKUName MonthSale MonthSaleValue 
    EMP1  36    SKU1     74     40193.1 
    EMP1  17    SKU2     113     6656.83 
    EMP1  18    SKU3     461     40733.96
    EMP1  19    SKU4     2     268.48

Regards

I've edited the formatting a lot - tabs don't play nicely with Markdown. — Jon Skeet
– Jon Skeet, Commented Mar 3, 2012 at 8:01
Thanks! pleas have a look at the issue. My main issue is Monthly sale is repeating for all skus — Tassadaque
– Tassadaque, Commented Mar 3, 2012 at 8:06
I've just noticed the ToList() part - that means you should be able to see the difference in lstSale, as the rest will be done in LINQ to Objects. — Jon Skeet
– Jon Skeet, Commented Mar 3, 2012 at 8:07
I am making list to avoid N+1 queries. If I remove tolist() query is taking to much time to execute — Tassadaque
– Tassadaque, Commented Mar 3, 2012 at 8:17
That's interesting in itself. But I would try to simplify it as much as possible in order to work out what's going wrong. — Jon Skeet
– Jon Skeet, Commented Mar 3, 2012 at 8:19

kaj · Accepted Answer · 2012-03-03 11:45:41Z

As an approach to finding the answer...

To diagnose, as suggested by @Jon Skeet, you need to simplify it AND look at what you're getting within lstSale to compare LINQ to SQL to EntityFramework.

So something along the following lines may help (not necessarily syntactically correct because I haven't got all your source objects to check, however I'm just looking at the query and simplifying it down where it appears you can)

var _sale = from emp in setupEmployees
            join sales in vwSaleTargets on emp.EmployeeID equals sales.EmployeeID
            join price in vwPeriodPricings
               on new { sales.SKUID, sales.PeriodID } 
               equals new { SKUID = (int?)price.SKUID, PeriodID = (int?)price.PeriodID }
            join sk in setupSKUs on sales.SKUID equals sk.SKUID
            where emp.EmployeeID == 123 && sales.StartDate.Year == 2012 && sales.StartDate.Month == 2
            select new 
            { 
                EmployeeName = emp.EmployeeName, 
                StartDate = sales.StartDate,
                SalesQuantity = sales.SalesQuantity, 
                ExFactoryPrice = price.ExFactoryPrice, 
                SKUID = sk.SKUID,
                SKUName = sk.SKUName 
            };

var lstSale = _sale.ToList(); //to avoid n+1 queries in case of grouping

// Run through lstSale here
foreach(var item in lstSale)
{
  Console.WriteLine(item);
}

var sale2 = from x in lstSale
            group x by new { x.SKUID, x.EmployeeName } into grouping
            select new 
            {
                 EmployeeName = grouping.Key.EmployeeName,
                 SKUID = grouping.Key.SKUID,
                 SKUName = grouping.SKUName,
                 MonthSale =(double?)grouping
                          .Where(x => x.StartDate.Month == 2 && 
                                      x.StartDate.Year == 2012)
                          .Select(t=>t.SalesQuantity)
                          .Sum(t=>t.Value)?? 0,
                 MonthSaleValue = (double?)grouping
                          .Where(x => x.StartDate.Month == 2 && 
                                      x.StartDate.Year == 2012)
                          .Sum(x => x.SalesQuantity * x.ExFactoryPrice)  
                             ?? 0,
            };
Console.WriteLine(sale2.OrderBy(x => x.SKUName).ToList());

Changes (which may not all be valid):
1. Removed branding since it isn't consumed in the second query (you could use it as a join in the first but not add to the new type if its part of the restriction)
2. Simplified what's included in the anonymous type generated by the first query - if you're only consuming parts of emp/sales/price then it may make it clearer as to what's going on
3. Added restriction to SalesMonth in the first part (what you're doing in the second) because that may reduce your data, increase performance and allow you to focus on what's actually going wrong (I have left the second SalesMonth restriction in place)
4. I assume SKUID is the relevant part of sk for grouping and not all of the object is required

The point of the foreach is so that you can see what's in the list at the interim step. Not about forcing the execution of the query.

Ladislav Mrnka · Accepted Answer · 2012-03-03 09:34:16Z

0

Start by investigating generated SQL for var lstSale = _sale.ToList();

In case of Linq-To-SQL you can use:

context.Log = Console.Out;

In case of EntityFramework ObjectContext API you can use

Console.WriteLine(((ObjectQuery)_sale).ToTraceString());

or DbContext API

Console.WriteLine(_sale.ToString());

There can be some difference how providers handle your Linq query which will not be visible without digging int SQL commands.

answered Mar 3, 2012 at 9:34

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Collectives™ on Stack Overflow

LINQ to SQL Query Issue in Entity Framework

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related