MVCMiniProfiler does not show WHERE part of Linq Query

Question

I'm using MVCMiniProfiler to profile some database queries. For one query it fails to show the Where part of the query that I have defined in my code.

code is as follows:

MyAppDataContext.cs:

public partial class MyAppDataContext : System.Data.Linq.DataContext {
    public static MyAppDataContext CreateNewContext() {

        var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyAppConnectionString"].ToString());
        var profiledConnection = new ProfiledDbConnection(sqlConnection, MiniProfiler.Current);
        return new MyAppDataContext(profiledConnection);

    }
}

Program code:

MyAppDataContext DataContext = MyAppDataContext.CreateNewContext();
IEnumerable<Requests> Entities = DataContext.Requests;

using (profiler.Step("get data")) {
    var dataset = Entities.Where(x => x.ControleStatus == 4).OrderBy(x => x.ID)
}

I've also tried:

using (profiler.Step("get data")) {
    var dataset = from x in Entities 
                  where x.ControleStatus == 4
                  orderby x.ID
                  select x;
}

This is what MVC Mini Profiler shows me:

SELECT [t0].[ID], [t0].[DatumOntvangst], [t0].[DatumRapport], [t0].[FK_SID], 
    [t0].[ControleStatus], [t0].[SStatus] FROM [dbo].[Request] AS [t0]

As you can see, there's no Where statement. Btw: The output contains the right results, but as this query takes 30 seconds to complete I'm wondering if all the data is first retrieved from the database and then the where condition is applied in Code (as opposed to on the database).

How is Entities defined? Specifically, what is the return type? — Jim Wooley, Jun 14 '12 at 12:47
the return type is an IEnumerable. Entities is AppDataContext.Requests — jao, Jun 14 '12 at 16:59

score 3 · Accepted Answer · answered Jun 14 '12 at 18:40

3

You need to make it return IQueryable. By returnning IEnumerable, you are not allowing the rest of the expression tree to be converted by the IQueryable provider. The LINQ to Objects versions of the query operators work on IEnumerable. LINQ to SQL requires the IQueryable version.

answered Jun 14 '12 at 18:40

Jim Wooley

10,169
1
25
43

I'm sorry, that didn't fix it. – jao Jun 15 '12 at 06:11
Could you include the definition for Entities in your question. At this point we're trying to help with incomplete information which is affecting your results. – Jim Wooley Jun 15 '12 at 15:21
Thank you ery much. By adding the code to update my question I found out that Entities is in fact a IEnumerable. I changed the rest of the code to IQueryable but did not change Entities. Now that I did that, the Where condition is showed. – jao Jun 15 '12 at 19:13
1

Glad that I could help. Often the solution comes from trying to explain the problem. – Jim Wooley Jun 15 '12 at 20:10
by changing my code to IQueryable, it also executes 10x faster (which was the actual problem, see my question here http://stackoverflow.com/questions/11018153/w3wp-exe-high-network-on-database-call). Why is IQueryable faster than IEnumerable? – jao Jun 16 '12 at 13:32
1

It's faster because it no longer needs to hydrate all of the rows and then filter using LINQ to Objects. The filter is now being sent to the database reducing the number of resulting rows. – Jim Wooley Jun 17 '12 at 01:38

MVCMiniProfiler does not show WHERE part of Linq Query

1 Answers1