Linq query not working but its equivalent SQL Server query is working

Question

Following SQL query works fine (Give expected results) in SQL Server.

SELECT * FROM ImportedReportDataCRR IRD 
INNER JOIN ImportedReport IR ON IRD.ImportedReportId = IR.Id
WHERE 
IR.StartDate >= CONVERT(DATETIME, '7/1/2022 12:00:00 AM') AND IR.EndDate <= CONVERT(DATETIME, '7/31/2022 11:59:59 PM')

But when I use equivalent Linq (C# / ASP.Net) its not working as expected (Empty result set returned).

My Linq statement is

var ImportedReportDataList = DbContext.ImportedReportDataCRRs.Where(w =>
                (w.ImportedReport.StartDate.Value >= StartDate && w.ImportedReport.EndDate.Value <= EndDate)).ToList();

-- Update Starts --

Linq converts into following SQL query

exec sp_executesql N'SELECT 
    [Extent1].* 
    FROM  
    [dbo].[ImportedReportDataCRR] AS [Extent1]
    INNER JOIN [dbo].[ImportedReport] AS [Extent2] ON [Extent1].[ImportedReportId] = [Extent2].[Id]
    WHERE 
    ([Extent2].[StartDate] >= @p__linq__0) AND 
    ([Extent2].[EndDate] <= @p__linq__1)',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2022-07-01 00:00:00.0010000',@p__linq__1='2022-07-31 23:59:59.9970000'

i.e. Linq is converting StartDate and EndDate into datetime2(7) which is creating issue.

-- Update Ends --

Variable StartDate = 7/1/2022 12:00:01 AM

Variable EndDate = 7/31/2022 11:59:59 PM

ImportedReport.StartDate = 2022-07-01 00:00:00.000 (DB field value)

ImportedReport.EndDate = 2022-07-31 00:00:00.000 (DB field value)

What to you mean by the start or end? A datetime value isn't a range, it's a point in time. 2022-07-01 00:00:00.000 represents exactly midnight on 01 July 2022, and nothing else. A day starts at midnight, and ends immediately before the next one starts (I intentionally don't give a time here as leap seconds exist), so midnight can't be the end of a day. Are you, perhaps, trying to use BETWEEN on date time values? — Thom A
– Thom A ♦, Commented Aug 10, 2022 at 18:58
Post the comparison query. Sounds like there's an issue in the query or you're interpreting the results incorrectly. — squillman
– squillman, Commented Aug 10, 2022 at 18:59
Quite simply, never try to find the end of any period (whether it's a day, a month, a year, what have you). Because different data types change what it means, it's always safer to perform range queries in the form >= start of period and < start of _next_ period. See all the links involving BETWEEN at Dating Responsibly. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 10, 2022 at 19:03
(w.EmployeeName.Trim().ToLower() == "test") <-- This won't work either btw. — Dai
– Dai, Commented Aug 10, 2022 at 19:51

Aaron Bertrand · Accepted Answer · 2022-08-10 19:39:51Z

3

As mentioned in the comments, don't try to simulate BETWEEN where you find the end of a date range. This is problematic because different data types consider the "end" of a period differently. In this case, much better to say "greater than or equal to the first of July, and less than the first of August." Also using non-regional and unambiguous date formats:

SELECT <cols>
FROM dbo.ImportedReportData AS IRD 
INNER JOIN dbo.ImportedReport AS IR ON IRD.ImportedReportId = IR.Id
WHERE IR.StartDate     >= '20220701'
  AND IR.EndDate       <  '20220801'
  AND IRD.EmployeeName =  'test';

Please read all of the links at Dating Responsibly.

I suppose in Linq you could say:

&& w.ImportedReport.EndDate.Value 
   < { whatever you do in Linq to add a day to EndDate }

answered Aug 10, 2022 at 19:39

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gulfam Over a year ago

When I checked Linq converted query using Profile then Linq was converting DateTime to datetime2(7) which is creating issue. Don't know why this is happening, but I guess I need to compare date only and should truncate the time part.

Gulfam Over a year ago

Please check question now. I have included converted query and more details as well. The problem is that Linq return empty result set while SQL Server query return expected rows.

Gulfam Over a year ago

Thanks @Aaron Bertrand , issue has been resolved. A millisecond was there in StartDate (Linq) which was creating problem. Its should had been 00:00:00.000 but it was 00:00:00.001. DateTim2(7) was not the issue . It was my mistake but I learned a lot from your replies about Date comparisons. Date comparison in SQL Server can be very tricky and unexpected.

Collectives™ on Stack Overflow

Linq query not working but its equivalent SQL Server query is working

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related