CODEWITHSUNDEEP
c#linq-to-sql
pm86
pm86

Reputation: 230

LINQ TO SQL Compare nullable date time in where condition

I am using LINQ TO SQL.
Following is the code I am using to fetch data from Database.

contractMaintenances = mdbDataContext.ContractMaintenances
                         .Where(c => c.ContractID == contractId
                             && c.ChargeFrequencyID == periodFrequencyId
                             && c.FirstPeriodStartDate == (firstPeriodStartDate == null ? null : firstPeriodStartDate)
                             && c.FinalRenewalEndDate == (finalBillingPeriodEndDate.HasValue ? finalBillingPeriodEndDate : (DateTime?)null)
                             && c.IsDeleted == false).ToList();

Here above FinalRenewalEndDate is nullable in database, So if finalBillingPeriodEndDate has value then use that value otherwise null.

but if is not working I have checked in SQL profiler below is the profiler SQL.

WHERE ([t0].[ContractID] = @p0) 
    AND ([t0].[ChargeFrequencyID] = @p1) 
    AND ([t0].[FirstPeriodStartDate] = @p2) 
    AND ([t0].[FinalRenewalEndDate] = @p3) 
    AND (NOT ([t0].[IsDeleted] = 1))',N'@p0 int,@p1 int,@p2 datetime,@p3 datetime',@p0=16422,@p1=4,@p2='2012-05-01 00:00:00',@p3=NULL

but if I compare null value directly with field then it is working fine.

&& c.FinalRenewalEndDate == null

if I pass null as directly then it will generate proper output me. It will not pass p3 parameter in SQL Profiler.

WHERE ([t0].[ContractID] = @p0) 
    AND ([t0].[ChargeFrequencyID] = @p1) 
    AND ([t0].[FirstPeriodStartDate] = @p2) 
    AND ([t0].[FinalRenewalEndDate] IS NULL) 
    AND (NOT ([t0].[IsDeleted] = 1))',N'@p0 int,@p1 int,@p2 datetime',@p0=16422,@p1=4,@p2='2012-05-01 00:00:00'

What will be the fix. I have 3 optional date parameter. If I wan't find any solution then I have to write multiple if condition to check for null parameter and I dont want to write those conditions.

Upvotes: 1

Views: 1187

Answers (2)

Marko Juvančič
Marko Juvančič

Reputation: 5890

Maybe you could try writing your condition without ?: operator.

 && c.FinalRenewalEndDate == finalBillingPeriodEndDate

Upvotes: 0

nharrer
nharrer

Reputation: 636

Try to use object.Equals instead of the equality operator:

object.Equals(c.FinalRenewalEndDate, finalBillingPeriodEndDate.HasValue ? finalBillingPeriodEndDate : (DateTime?)null)

Upvotes: 3

Related Questions