Geek Answers Handbook

Nullable Field and SQL are Null Issue

There are tons of Q & A on stackoverflow related to my question, but I cannot deduce the argument of the problem and the solution that works best in this scenario;

So, I have a method that allows you to pass in the parent identifier and based on the value records will be filtered using the LINQ query. A field in the database is nullable. Now, if I compare the fields using the == operator in the where clause, the sql output is incorrect ( IS NULL not used for comparison), and therefore the query gives 0 results. I solved this with the Object.Equals() method. This worked, but now I get an exception when passing a NON NULL value, an integer

Unable to create a constant value of type "System.Object". In this context, only primitive or enumeration types are supported.

So I wrote a simple method

 using (TestEntities context = new Entities()) { return from c in context.ItemMappings where c.ParentID.Equals(parentID) select new ItemDTO { ItemID = c.Item.ItemID, ItemName = c.Item.ItemName, ItemType = new ItemTypeDTO { TypeID = c.Item.Type.TypeID, TypeName =c.Item.Type.TypeName }; }
+6
source share
3 answers

Yes, your problem will also arise if SQL. You must explicitly handle null, and this should work:

 Where (parentID == null && c.ParentID == null) || (parentID == c.ParentID)

It is assumed that you need to match zero. If you want null to return all results without filtering, do this instead:

 Where (parentID == null) || (parentID == c.ParentID)

I had problems with this even sometimes, and found how LINQ would translate correctly all the time:

 Where (parentID == null) || (parentID != null && parentID == c.ParentID)

This is because even in SQL, if you execute ParentID = @ParentID , a null match does not return any results, and you must use ISNULL to avoid it being empty.

+3
source

To allow nullable you can also try Like this

  GSectionID = emp.SectionID ?? Guid.Empty,
+1
source

In EF6, you can use UseCSharpNullComparisonBehavior to solve this problem. You must set the UseCSharpNullComparisonBehavior context parameter to true, and it will behave like C #.

 objectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;

You can see more in the following link: http://entityframework.codeplex.com/workitem/145

+1
source

More articles:


All Articles