How to run SQL Queries on EF ?

Question

I am working on EF with C# and WPF as front end. I need to provide a UI for so user can create his own query and get the result. The UI will be list of tables and list of columns to select from (Not happy with UI. Need to improve but new ticks in my mind).

So my question is how create, merge(existing query) and execute the queries.

There are sql class Entity Client provider, objectquery class. I used ObjectQuery

string querystring = @"SELECT PrjDev FROM prjscenario"; 
ObjectQuery<PrjDev> prjdevquery = new ObjectQuery<PrjDev>(querystring, ptxobjcontext);
string cpmmandtext = prjdevquery.CommandText;
int prjdevnum =  prjdevquery.Count();

It is working. But when i run some complex query. It is not working. Example code :

string querystring = @"SELECT PrjDev FROM prjscenario WHERE PrjDev.PrjDevType = 10";

Error :

'PrjDevType' is not a member of 'Transient.collection[Skm.Ptx.Data.Emf.PrjDev(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection. Near simple identifier, line 1, column 45.

Any Idea, why it is good for one simple query but it is not working for complex queries?

Thanks in Advance, N

Answer 1

The query passed as a parameter to the ObjectQuery<T> constructor is NOT an SQL query, it's an ESQL (Entity SQL) query. While similar in syntax, they are very different languages. You can learn more about ESQL on this page.

If you want to execute real SQL against the ObjectContext's underlying database, you can use the ObjectContext.ExecuteStoreQuery method, or just retrieve the connection through the ObjectContext.Connection property and write "classic" ADO.NET code from there.