Query Result Differences

wizaerd

I have a fairly big query (LINQ), and when I run it in LINQPad, I get 90,189 records in my result set. Copying the LINQ statement to a C# app, and running the same LINQ statement results in 87,776 records. Why would there be a difference in the number of records returned?

The only difference between the two is the LINQPad is using LINQPads data context and entity model, and the C# app is using EntityFrameworks, but the underlying data should still be the same, no?

Also, 1 other changes was necessary; I had to remove a call to IndexOf, and replace it with SqlFunctions.CharIndex() call.

startDate = 2/1/2016 12:00:00 AM
endDate = 3/1/2016 12:00:00 AM

LINQPad Query
var query = (from epv in EMPLOYEE_PAYROLL_VOUCHERS join p in Wh_Peos on epv.Employer_ID equals p.PeoId into ps from p in ps.DefaultIfEmpty() join ceo in Wh_ClientEmployerOverrides on epv.Employer_ID.ToString() equals ceo.EmployerId into cs from c in cs.DefaultIfEmpty() where (epv.Pay_Date >= startDate && epv.Pay_Date <= endDate) && epv.Tot_Earn_Amt != 0.00m select new PayrollHeader(epv.U2_id) { PeoId = ((p.PeoId != 0) ? p.PeoId : (c.PeoId != 0) ? c.PeoId : 10), ClientId = Convert.ToInt32(epv.Client_ID), EmployerId = epv.Employer_ID ?? p.PeoId, EmployeeId = epv.EE_ID, EmployeeName = epv.EE_Sort_Name, CheckNumber = epv.CHK_No.ToString() ?? epv.CHK_Non_Numeric_ID, VoucherNumber = epv.U2_id.Substring(epv.U2_id.IndexOf('.') + 1), PayDate = epv.Pay_Date.Value, GrossPay = epv.Tot_Earn_Amt.Value, NetPay = epv.Pay_Net_Amt.Value, TotalDeductions = epv.Tot_Vol_Dedn_Amt.Value, TotalTaxes = epv.Tot_Tax_Withheld_Amt.Value, RetirementCatchUpAmount = epv.Retirement_Catch_Up_Amt ?? 0, RetirementLoanPayAmount = epv.Retirement_Loan_Pay_Amt ?? 0, RetirementRothAmount = epv.Retirement_Roth_Amt ?? 0, RetirementRothCatchUpAmount = epv.Retirement_Roth_Catch_Up_Amt ?? 0, RetirementSafe1 = epv.Retirement_Safe_1 ?? 0, RetirementSafe2 = epv.Retirement_Safe_2 ?? 0, TotalReportedTipAmount = epv.Tot_Reported_Tip_Amt ?? 0 }).OrderBy(x => x.PeoId).ThenBy(x => x.PayDate).ThenBy(x => x.ClientId).ThenBy(x => x.EmployeeId).ToList();

C# Query
var query = (from epv in db.EMPLOYEE_PAYROLL_VOUCHERs join p in db.wh_Peos on epv.Employer_ID equals p.PeoId into ps from p in ps.DefaultIfEmpty() join ceo in db.wh_ClientEmployerOverrides on epv.Employer_ID.ToString() equals ceo.EmployerId into cs from c in cs.DefaultIfEmpty() where (epv.Pay_Date >= startDate && epv.Pay_Date <= endDate) && epv.Tot_Earn_Amt != 0.00m select new PayrollHeader() { u2_id = epv.u2_id, PeoId = ((p.PeoId != 0) ? p.PeoId : (c.PeoId != 0) ? c.PeoId : 10), ClientId = epv.Client_ID, EmployerId = epv.Employer_ID ?? p.PeoId, EmployeeId = epv.EE_ID, EmployeeName = epv.EE_Sort_Name, CheckNumber = (epv.CHK_No.ToString() == "") ? epv.CHK_Non_Numeric_ID : epv.CHK_No.ToString(), VoucherNumber = epv.u2_id.Substring(SqlFunctions.CharIndex(".", epv.u2_id) ?? 0), PayDate = epv.Pay_Date.Value, GrossPay = epv.Tot_Earn_Amt.Value, NetPay = epv.Pay_Net_Amt.Value, TotalDeductions = epv.Tot_Vol_Dedn_Amt.Value, TotalTaxes = epv.Tot_Tax_Withheld_Amt.Value, RetirementCatchUpAmount = epv.Retirement_Catch_Up_Amt ?? 0, RetirementLoanPayAmount = epv.Retirement_Loan_Pay_Amt ?? 0, RetirementRothAmount = epv.Retirement_Roth_Amt ?? 0, RetirementRothCatchUpAmount = epv.Retirement_Roth_Catch_Up_Amt ?? 0, RetirementSafe1 = epv.Retirement_Safe_1 ?? 0, RetirementSafe2 = epv.Retirement_Safe_2 ?? 0, TotalReportedTipAmount = epv.Tot_Reported_Tip_Amt ?? 0 }).OrderBy(x => x.PeoId).ThenBy(x => x.PayDate).ThenBy(x => x.ClientId).ThenBy(x => x.EmployeeId).ToList();

kingkeith

It might be worth comparing the SQL statements generated by those EF queries to see how they differ

wizaerd

What's the best way to do that?

JoeAlbahari

LINQPad uses LINQ to SQL by default, but you can tell it to use Entity Framework by pointing it to the model you've created in Visual Studio.

Then you'll be able to compare the generated SQL by clicking the 'SQL' tab in LINQPad.