Query Result Differences

edited June 2016
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();

Comments

  • It might be worth comparing the SQL statements generated by those EF queries to see how they differ
  • What's the best way to do that?
  • 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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!