Entity Framework: search for database entries with null columns

Question

I have the following model:

public class FactCache
{
    public int ID { get; set; }
    public DateTime MonthDate { get; set; }
    public string AttributeKey { get; set; }

    public decimal RawValue { get; set; }
    public decimal ManDayValue { get; set; }
    public decimal FTEValue { get; set; }

    public int? InputResourceID { get; set; }
    public int? PhaseID { get; set; }

    public virtual InputResources InputResource { get; set; }
    public virtual DimPhase Phase { get; set; }
}

As you can see above, InputResourceID and PhaseID are nullable optional fields.

I want to write a query to find the first entry for a given date and AttributeKey where both PhaseID and InputResourceID are null.

I have tried the following code:

FactCache fact = db.FactCache.FirstOrDefault(
  a => a.InputResourceID == null
  && a.PhaseID == null
  && a.MonthDate == monthDate
  && a.AttributeKey == key);

However, it returns a null object. What is the correct way to write the above query?

I have checked the database and there are indeed entries with null PhaseID and InputResourceID.

Have a look at [this SO question](http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework), which version of EF are you using? — Andomar, May 15 '13 at 15:21
Forgive the obvious question: does it still return null if you remove the `MonthDate` and `AttributeKey` checks? — Steve Wilkes, May 15 '13 at 15:28
I have fond something strange in the database (I am using code first): Appart from the `InputResourceID` column, code first has added a second `InputResources_ID` column. It seams convention over configuration has not worked (I added `public virtual ICollection FactCache { get; set; }` to the `InputResources` model) No idea if this has any relation — Chopo87, May 15 '13 at 15:41
@Andomar: I am using version 4. The link you provided uses a different notation to what I am use to, does that meen that the notation above cannot handle null values? — Chopo87, May 15 '13 at 15:41
After reading the post suggested by @Andomar and this link (http://stackoverflow.com/questions/16309866/call-is-failing-with-null-parameter) I think I am going to try to upgrade the project to EF6 — Chopo87, May 15 '13 at 15:47

score 1 · Accepted Answer · answered May 15 '13 at 15:46

1

Not sure, but perhaps...

FactCache fact = db.FactCache.FirstOrDefault(
  a => false == a.InputResourceID.HasValue
  && false == a.PhaseID.HasValue
  && a.MonthDate == monthDate
  && a.AttributeKey == key);

answered May 15 '13 at 15:46

object88

720
1
7
20

Yep, seems to work. Still wish I could use native C# `null`, but it looks like I'll have to wait until my team decides we can upgrade to EF5. Thanks – Chopo87 May 15 '13 at 16:09

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

Just a note, I you are lucky enough to be running EF version 5 or above try the solution proposed here: Call is failing with null parameter

edited May 23 '17 at 11:50

Community

1
1

answered May 15 '13 at 16:06

Chopo87

1,240
4
19
32

Entity Framework: search for database entries with null columns

2 Answers2