Query Result Differences
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
C# Query
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
Then you'll be able to compare the generated SQL by clicking the 'SQL' tab in LINQPad.