Entity Framework 4.0 Code-First Dynamic Query

Question

I would like to query a table based on a list of KeyValuePair. With a Model-First approach, I could do the following:

var context = new DataContext();
var whereClause = new StringBuilder();
var objectParameters = new List<ObjectParameter>();

foreach(KeyValuePair<string, object> pair in queryParameters)
{
    if (whereClause.Length > 0)
        whereClause.Append(" AND ");
    whereClause.Append(string.Format("it.[{0}] = @{0}", pair.Key));
    parameters.Add(new ObjectParameter(pair.Key, pair.Value));
}

var result = context.Nodes.Where(whereClause.ToString(), parameters.ToArray());

Now I'm using a Code-First approach and this Where method is not available anymore. Fortunately, I saw an article somewhere (I can't remember anymore) which suggested that I could convert the DbContext to a IObjectContextAdapter then call CreateQuery like this:

var result = ((IObjectContextAdapter)context)
                .ObjectContext.CreateQuery<Node>(whereClause.ToString(), parameters.ToArray());

Unfortunately, this throws an error:

'{ColumnName}' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly.

Where {ColumnName} is the column specified in the whereClause.

Any ideas how I can dynamically query a DbSet given a list of key/value pairs? All help will be greatly appreciated.

We use System.Linq.Dynamic for this. It's an add-on, but worth it IMHO. Not really the question you're asking, but it's a possible solution. — Craig Stuntz
– Craig Stuntz, Commented Jun 9, 2011 at 18:11
Thanks again Craig!! I really liked your approach and it works. — Alfero Chingono
– Alfero Chingono, Commented Jun 9, 2011 at 22:06

Ladislav Mrnka · Accepted Answer · 2011-06-09 19:57:25Z

2

I think your very first problem is that in the first example you are using Where on the entity set but in the second example you are using CreateQuery so you must pass full ESQL query and not only where clause! Try something like:

...
.CreateQuery<Node>("SELECT VALUE it FROM ContextName.Nodes AS it WHERE " + yourWhere)

The most problematic is full entity set name in FROM part. I think it is defined as name of the context class and name of the DbSet exposed on the context. Another way to do it is creating ObjectSet:

...
.ObjectContext.CreateObjectSet<Node>().Where(yourWhere)

edited Jun 9, 2011 at 19:57

answered Jun 9, 2011 at 19:21

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

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

5 Comments

Craig Stuntz Over a year ago

+1 for being generally right, but use params or sanitize if the "where" involves user data.

Ladislav Mrnka Over a year ago

@Craig: I agree. I just made the example too slovenly. Where clause should not be build with string concatenation.

Alfero Chingono Over a year ago

When I try the second suggestion, I get an error: DbContext does not contain a definition of 'CreateObjectSet' and no extension method....

Ladislav Mrnka Over a year ago

That is not method of DbSet but the method of ObjectContext. You must convert DbContext to ObjectContext as you did before and then you can call this method.

Alfero Chingono Over a year ago

Thank you! I really appreciate your help. I must also mention that I tried the suggestion by @Craig and it works too. I prefer that approach however, this is the answer to my question.

Collectives™ on Stack Overflow

Entity Framework 4.0 Code-First Dynamic Query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related