Home

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

Sign In or Register to comment.