4ell for a while today, I’ve been trying to find a way to execute a specific dynamic query with the Entity Framework v4 – a query which could be used on a variety of different tables.
I have a series of tables which share a common column definition (lets call it “DateEnd”, of type DateTime). Our example use case scenario is that we might want to query one of the several tables for the most recent value for this common column.
So normally what we would create is something akin to the following (where Table is set dynamically):
SELECT TOP(1) [DateEnd]
FROM [<table>]
ORDER BY [DateEnd] desc
Now, the main problem here is how can you execute such a query within the constraints of the Entity Framework?
If you’ve investigated the API you’ll find it isn’t necessarily obvious how we would proceed.
The first problem is that the design ties it strongly to mapped entities (as opposed to scalar values), and secondly the syntax ties it heavily to explicitly typed (using EntityTypes) queries where you have to know the base type at compile time – i.e. the following LINQ query requires we specify the source table at compile time:
DateTime mostRecentDate = (from d in db.Statistics
orderby d.DateEnd descending
select d.DateEnd).Take(1).FirstOrDefault();
Luckily, I have a solution which will achieve the desired result!
The answer is to make use of the data ObjectContext.CreateQuery<T>(..) method.
With CreateQuery, we must specify the expected type (in our example, we use DateTime). We can create our query as you would normally expect – here’s a sample:
Note that values in italics should be the name of your DataContext type.
using(ContextName db = new ContextName())
{
string currentTable = “Statistics”;
string query = String.Format(“select value c.DateEnd from {0} as c order by c.DateEnd desc”, currentTable);
DateTime query = db.CreateQuery<DateTime>(query).FirstOrDefault();
}
Now, here’s the trick – the query has to be specifically formatted. A straight T-SQL compatible query won’t work!
Let’s examine the example query from above:
string query = String.Format(“select value top(1) c.DateEnd from {0} as c order by c.DateEnd desc”, currentTable);
The two values in bold, above, are needed. We have to specify that we are returning a value (hence, the first highlighted word) and then we must use the qualified object name – not the table name. In other words if you are pluralizing tables in your model, the table “Statistic” would be “Statistics” – and therefore you would need to use “Statistics”. Also note that the column used uses an alias (c.).
Now if we profile (trace) our query you can see it successfully queries as we would expect:
So if you are getting something along the lines of this error message:
“[..] 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. Near simple identifier, line 1, column [..].”
It most likely means you aren’t correctly using an alias. Make sure you are using a table alias and prefixing it where appropriate.
Now you can go query mad! Just kidding, please use this wisely. Unfortunately, this method makes it possible to execute unwieldy queries against the data store directly. So please use it very sparingly, and only if Stored Procedures or Functions are not appropriate to your scenario.
Cheers…R
