Call with a 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 the values ​​for brandCode and programCode, I return the expected value from the database. When I make a call, but explicitly setting x.ProgramCode and x.BrandCode to null, I get the expected default value from the database:

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

However, when I call a method with null for codeCode and brandCode, I return null from the database!

I tried changing value == to .Equals () to answer this problem: Optional 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 not working.

This article says that no solution was found, and he / she had to use the stored procedure: EF 4 Query - a problem with several parameters I really don't know, I need to go there.

Any ideas?

Answer 1

I don’t know which version of EF you are using, but zero comparison was a problem prior to version 5. If you check the SQL, which is actually emitted, you will probably see that IS NULL not used in the query.

In EF 6, you can set the UseDatabaseNullSemantics configuration parameter open on DbContext :

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

For EF 5, you can use the UseCSharpNullComparisonBehavior parameter in the underlying ObjectContext :

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

However, for your project you will need to use the .NET Framework 4.5. If you do not want to use 4.5, you can use one of the workarounds listed in How can I query for null values ​​in the entity infrastructure? .

Answer 2

It turns out? operator-operator really works, I just did not apply it to both sides of the == operators. Thus, 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 leads to the fact that the empty string and zero are equivalent. Not ideal.