Entity Framework 4.0 Entity SQL passing ObjectParameter null parameters

Question

I have a SQL Entity query:

SELECT VALUE t FROM MyEntities AS t WHERE t.Name = @p OR (@p IS NULL AND t.Name IS NULL) 

I can execute the request as follows:

 var results = context.CreateQuery<WorkflowInstance>( query, new ObjectParameter("p", name)).ToList(); 

However, if the variable 'name' is null, I get a System.ArgumentNullException. So I also tried using DBNull.Value if the name was null, and I get the following exception:

System.ArgumentOutOfRangeException Error

Message = The specified parameter type "System.DBNull" is not valid. Only scalar types, such as System.Int32, System.Decimal, System.DateTime, and System.Guid, are supported.

I would like to have parameterized queries where null values ​​are also possible parameter values. How to achieve this with Entity SQL?

Answer 1

You are right, it seems that this is an error in the ObjectParameter constructor. But the Value property seems to be null. Try replacing the code:

 var prm = new ObjectParameter("p", typeof(string)); prm.Value = name; var results = context.CreateQuery<WorkflowInstance>( query, prm).ToList(); 

If you assign the Value parameter directly, the code seems to work.

David

Answer 2

Good post by Davide, I used this fix to pass an integer value;

 var prm = new ObjectParameter("pName", typeof(int)); prm.Value = pmId;