CODEWITHSUNDEEP
linqentity-framework
paulpitchford
paulpitchford

Reputation: 560

Nullable DateTime in Where Clause in EF Linq Query

Hi I have a table where there is a RespondBy property which is of DateTime and is Nullable. Here is the linq I'm trying to run over EF6:

IEnumerable<Enquiry> ASAPEnquiries = db.Enquiries
       .Where(enq => enq.RespondBy == null && enq.JobCostings.Count == 0)
       .OrderBy(enq => enq.FlReference);

However, when I run if (ASAPEnquiries.Count() > 0) I get an error stating Nullable object must have a value. How would one query the database using linq if you want to check null DateTime columns?

Thank you.

EDIT:

The SQL that is produced by EF when tested in MSSMS brings back the desired result FYI. SQL Produced:

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[FlReference] AS [FlReference], 
    [Project1].[EnquiryDate] AS [EnquiryDate], 
    [Project1].[ContactName] AS [ContactName], 
    [Project1].[ProjectReference] AS [ProjectReference], 
    [Project1].[EnquiryDetails] AS [EnquiryDetails], 
    [Project1].[RespondBy] AS [RespondBy], 
    [Project1].[CreatedBy] AS [CreatedBy], 
    [Project1].[Created] AS [Created], 
    [Project1].[ModifiedBy] AS [ModifiedBy], 
    [Project1].[Modified] AS [Modified], 
    [Project1].[RowVersion] AS [RowVersion], 
    [Project1].[Enquiry_Customer] AS [Enquiry_Customer], 
    [Project1].[Enquiry_EnquiryStatus] AS [Enquiry_EnquiryStatus]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[FlReference] AS [FlReference], 
        [Extent1].[EnquiryDate] AS [EnquiryDate], 
        [Extent1].[ContactName] AS [ContactName], 
        [Extent1].[ProjectReference] AS [ProjectReference], 
        [Extent1].[EnquiryDetails] AS [EnquiryDetails], 
        [Extent1].[RespondBy] AS [RespondBy], 
        [Extent1].[CreatedBy] AS [CreatedBy], 
        [Extent1].[Created] AS [Created], 
        [Extent1].[ModifiedBy] AS [ModifiedBy], 
        [Extent1].[Modified] AS [Modified], 
        [Extent1].[RowVersion] AS [RowVersion], 
        [Extent1].[Enquiry_Customer] AS [Enquiry_Customer], 
        [Extent1].[Enquiry_EnquiryStatus] AS [Enquiry_EnquiryStatus], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[JobCostings] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[JobCosting_Enquiry]) AS [C1]
        FROM [dbo].[Enquiries] AS [Extent1]
    )  AS [Project1]
    WHERE ([Project1].[RespondBy] IS NULL) AND (0 = [Project1].[C1])
    ORDER BY [Project1].[FluidReference] ASC

Also, if I iterate over the collection, I get no error so it seems to be something to do with calling .Count() on the collection.

Upvotes: 1

Views: 2598

Answers (3)

paulpitchford
paulpitchford

Reputation: 560

It would seem I have egg on my face. The if statement was calling a void where I was then using item.RespondBy.Value.ToShortDateTime(). How embarresing. Thank you all for your help though, I appreciate your time.

Upvotes: 0

Wahid Bitar
Wahid Bitar

Reputation: 14100

Please make sure that your Enquiry class assign value to the JobCostings property in it's constructor e.g.

public Enquiry()
{
    this.JobCostings = new HashSet<JobCosting>();
    //.......
}

and try this one :

IEnumerable<Enquiry> ASAPEnquiries = db.Enquiries
       .Where(enq => enq.RespondBy == null && !enq.JobCostings.Any())
       .OrderBy(enq => enq.FlReference);

Upvotes: 0

Adam Łepkowski
Adam Łepkowski

Reputation: 2078

Use the HasValue property to check if a Nullable has value.

IEnumerable<Enquiry> ASAPEnquiries = db.Enquiries
       .Where(enq => enq.RespondBy.HasValue && enq.JobCostings.Count == 0)
       .OrderBy(enq => enq.FlReference);

Upvotes: 1

Related Questions