Entity Framework query ToString won't produce SQL query

c# entity-framework entity-framework-5 entity-framework-6

Question

Through my research I've discovered that, since at least EF 4.1, the .ToString() method on an EF query will return the SQL to be run. Indeed, this very frequently works for me, using Entity Framework 5 and 6.

However, occasionally I call this method and get the runtime type of the query object. Here is my specific example:

Entity input = ...;
IQueryable<Entity> query = dbContext.SetOfEntity.Where(e => e.Prop == input.Prop);

More specifically, I'm setting a breakpoint in VS2013 and hovering over the query object, and seeingSystem.Data.Entity.Infrastructure.DbQuery<Namespace.Entity> instead of the SQL to run that query. Interestingly enough, if I hover over the DBSet property (dbContext.SetOfEntity), I do see the basic select SQL for the associated table. It's only when I filter the results that I lose the SQL.

Obviously, this is a pretty simple query and I could work out the SQL for myself, but this problem has happened on more complex queries, and it would be nice to be able to debug the SQL being sent to the server without running a database trace.

Some background

A while back, I was using EF5 and the ToString() seemed to work. Shortly before switching to EF6, it seemed that none of the queries showed me SQL, but after switching to EF6, it went back to the correct behavior. Additionally, whenever I hover over IQueryable queries and try to use the IDE's "Results View" feature, it tells me "Children could not be evaluated". This may be a separate issue, but I figure I'd include it in case it had a common cause.

1
2
4/22/2014 3:00:35 PM

Accepted Answer

If you do not need the SQL BEFORE it is executed against the DB you can do the following:

dbContext.Database.Log = s => Debug.WriteLine(s);

This would print the SQL (and some additional data) to the debug output.
See the following link for details: http://msdn.microsoft.com/de-DE/data/dn469464

Also check, as martin_costello suggested, that you are not querying the DB before you try to get the SQL via ToString(). It happened to me too, that I already got objects, because of using IEnumerable<> "to early" (instead if IQueryable<>) and so got way to many entities from the DB and did some filtering "in code" instead of "in SQL"...

3
4/22/2014 3:14:45 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow