Entity Framework 4.0 Entity SQL passing null ObjectParameter parameters

Question

I have an Entity SQL 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 query as follows:

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

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

System.ArgumentOutOfRangeException was caught
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 do I achieve this with Entity SQL?

Davide Icardi · Accepted Answer · 2011-01-27 11:09:05Z

12

You are right, seems to be a bug in the ObjectParameter constructor. But the Value property seems to accept null values. Try to replace your code with:

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.

Davide

answered Jan 27, 2011 at 11:09

Davide Icardi

12.3k8 gold badges61 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oliver Gray · Accepted Answer · 2012-02-02 16:03:47Z

1

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

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

answered Feb 2, 2012 at 16:03

Oliver Gray

8646 silver badges17 bronze badges

Collectives™ on Stack Overflow

Entity Framework 4.0 Entity SQL passing null ObjectParameter parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related