CODEWITHSUNDEEP
c#sqllinqentity-framework
Chris
Chris

Reputation: 27394

Viewing SQL generated by LINQ - Cast exception

I want to profile the SQL LINQ is generating when talking to Entity Framework but I am running into an exception. I found the ToTraceString() code on SO but it throws:

Unable to cast object of type 'WhereEnumerableIterator`1[Models.DeviceLogEntry]' to type 'System.Data.Objects.ObjectQuery'.

EF definition:

public virtual ICollection<DeviceLogEntry> Errors { get; set; }

Code

var result = from e in dbDevice.Errors
             where e.Current == true && e.LogEntryType == Models.DeviceLogEntryType.Error
             select e;

var trace = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

Upvotes: 0

Views: 1414

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

Actually your result will be of type DbQuery<DeviceLogEntry>, not ObjectQuery (DbQuery<T> is an adapter over InternalQuery, which uses ObjectQuery, but both are internal and not available from public API). But if you want to trace generated query, it's very simple - just call ToString() on query:

 var trace = result.ToString();

DbQuery<T> has overridden ToString() method, which internally calls objectQuery.ToTraceString() and returns string representation of the underlying query.

UPDATE: Thus it turns out that dbDevice is an instance of Device entity, and Errors is just a navigation property, then Entity Frameworks works in following way - if lazy-loading is enabled, then during first access of Errors property, it loads all realted DeviceLogEntry entities to dbDevice entity. That happens internally, by proxy class which EF generates for lazy-loading. You cannot get SQL which is executed in that case. All queries to Errors collection will happen in memory (i.e. it will be Linq to Objects instead of Linq to Entities). So, your result will be simple in-memory iterator of already loaded logs.

In order to see generated query, you should create DbSet for DeviceLogEntries on your DbContext class. Then you should define query:

var query =  from e in db.DeviceLogEntries
             where e.Current == true && 
                   e.LogEntryType == Models.DeviceLogEntryType.Error &&
                   e.DeviceId = dbDevice.Id // or use join
             select e;

Now query.ToString() will show you SQL query generated by Entity Framework.

NOTE: If you are using Entity Framework 6, then you also can use db.Database.Log to log executed database queries.

Upvotes: 2

Related Questions