Call is failing with null parameter

Question

I have the following code:

public static ContactEventValue GetContactEventValue(ContactEventType contactEventType, string programCode, string brandCode)
{
    AdvocacyEntities ent = AdvocacyEntities.GetReadOnlyInstance();
    ContactEventValue value = ent.ContactEventValues.SingleOrDefault(
        x => x.ContactEventTypeID == contactEventType.ContactEventTypeID
        && x.ProgramCode == programCode && x.BrandCode == brandCode);
}

When I call it with values for brandCode and programCode, I get the expected value back from the database. When I make the call but explicitly setting x.ProgramCode and x.BrandCode to null I get the expected default value back from the database:

ContactEventValue value = ent.ContactEventValues.Single(
        x => x.ContactEventTypeID == contactEventType.ContactEventTypeID
        && x.ProgramCode == null && x.BrandCode == null);

However, when I call the method with null for programCode and brandCode, I get null back from the database!

I tried changing the == to .Equals() per the answer to this issue: Nullable optional parameter

So x.BrandCode.Equals(brandCode) replaced x.BrandCode == brandCode, and x.ProgramCode.Equals(programCode) replaced x.ProgramCode == programCode, but that still didn't work.

I also tried using the ?? operator, still didn't work.

This issue says an solution wasn't found, and s/he had to used a stored procedure: EF 4 Query - Issue with Multiple Parameters I really don't want to have to go there.

Any ideas?

Answer 1

I don't know what version of EF you're using, but null comparison was an issue prior to version 5. If you inspect the SQL that is actually being emitted, you'll probably see that IS NULL is not used in the query.

In EF 6, you'll be able to set the UseDatabaseNullSemantics configuration option exposed on DbContext:

public class MyContext : DbContext
{
    public MyContext()
    {
        this.Configuration.UseDatabaseNullSemantics = true;
    }
}

For EF 5, you can use the UseCSharpNullComparisonBehavior setting on the underlying ObjectContext:

public class MyContext : DbContext
{
    public MyContext()
    {
        var objectContextAdapter = this as IObjectContextAdapter;
        objectContextAdapter.
            ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;    
    }
}

However, you will need to use .NET Framework 4.5 for your project. If you don't wish to use 4.5, then you can use one of the workarounds listed at How can i query for null values in entity framework?.

Answer 2

Turns out the ?? operator solution does work, I simply didn't apply it to both sides of the == statements. So the following code solves the problem:

public static ContactEventValue GetContactEventValue(ContactEventType contactEventType, string programCode, string brandCode) {
    AdvocacyEntities ent = AdvocacyEntities.GetReadOnlyInstance();
    ContactEventValue value = ent.ContactEventValues.SingleOrDefault(
            x => x.ContactEventTypeID == contactEventType.ContactEventTypeID
            && (x.ProgramCode ?? "") == (programCode ?? "")
            && (x.BrandCode ?? "") == (brandCode ?? ""));

However, this causes empty string and null to be equivalent. Not ideal.