Entity Framework 4.0 Entity SQL passing null ObjectParameter parameters

.net entity-framework .net-4.0 entity-framework-4 entity-sql
Mas picture Mas · Jan 27, 2011 · Viewed 8.4k times · Source

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?

Answer

Davide Icardi picture Davide Icardi · Jan 27, 2011

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

Related questions

What is the difference between Entity Framework and LINQ to SQL by .NET 4.0?

I was checking 2nd edition of Professional ASP.NET MVC and realized EF replaced LINQ to SQL. I am familiar to LINQ to SQL from the first book but I know nothing about EF. Anyway while reading the code, it …

Read More
MetadataException: Unable to load the specified metadata resource

All of a sudden I keep getting a MetadataException on instantiating my generated ObjectContext class. The connection string in App.Config looks correct - hasn't changed since last it worked - and I've tried regenerating a new model (edmx-file) from …

Read More
Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist

I am using Entity Framework 1 with .net 3.5. I am doing something simple like this: var roomDetails = context.Rooms.ToList(); foreach (var room in roomDetails) { room.LastUpdated = DateTime.Now; } I am getting this error when I try to do: context.SaveChanges(); …

Read More