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

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)

CodePudding user response：

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 }