LINQ or Entity Framework creates unbounded SQL statement

Question

I work on speed optimization of my application, and what I have found is that LINQ (or EF) is creating some strange SQL for me that works slow.

Here is some code :

SomeList.AddRange(_databaseView
                .Select(l=> new SomeViewModel
                                {
                                    Date = l.Date,
                                    Details = l.Details,
                                    Level = l.LevelName,
                                    Id = l.ViewID,
                                    Message = l.Message,
                                    ProjectName = l.projectName,
                                    StatusId = l.StatusID,
                                    StatusName = l.StatusName
                                })
                .Skip(50)
                .Take(25));

And in theory it should've created SQL statement that takes 25 record but profiler showes following SQL for it :

    SELECT [Extent1].[Date]  AS [Date],
       [Extent1].[ID]            AS [ID],
       [Extent1].[LevelID]       AS [LevelID],
       [Extent1].[StatusID]      AS [StatusID],
       [Extent1].[projectName]   AS [projectName],
       [Extent1].[LevelName]     AS [LevelName],
       [Extent1].[StatusName]    AS [StatusName],
       [Extent1].[Message]       AS [Message],
       [Extent1].[Details]       AS [Details],
       [Extent1].[LogViewID]     AS [LogViewID]
FROM   (SELECT [v_MyView].[Date]       AS [Date],
               [v_MyView].[ProjectID]     AS [ProjectID],
               [v_MyView].[LevelID]       AS [LevelID],
               [v_MyView].[StatusID]      AS [StatusID],
               [v_MyView].[projectName]   AS [projectName],
               [v_MyView].[LevelName]     AS [LevelName],
               [v_MyView].[StatusName]    AS [StatusName],
               [v_MyView].[Message]       AS [Message],
               [v_MyView].[Details]       AS [Details],
               [v_MyView].[ViewID]        AS [ID]
        FROM   [dbo].[v_MyView] AS [v_MyView]) AS [Extent1]

_databaseView is IQueryable object on which all my sorting and filtering logic is done.

Here is something I figured : if I don't do any filtering SQL is normal with SELECT TOP (25) on it. But whenever I do filtering something gets messed up. Here is the code to one of my filters:

if (Filters.ProjectName != null && Filters.ProjectName[0] != 0)    // check if "all" is not checked
    _databaseView = Filters.ProjectName
        .Join(_databaseView,  f => f, l => l.ProjectID,  (f,l) => new SomeViewModel
                                                                           {
                                                                               Date = l.Date,
                                                                               Details = l.Details,
                                                                               LevelName = l.LevelName,
                                                                               ViewID = l.ViewID,
                                                                               Message = l.Message,
                                                                               projectName = l.projectName,
                                                                               StatusID = l.StatusID,
                                                                               StatusName = l.StatusName
                                                                           })
    .AsQueryable();

And it is without any constraint. How do I make this LINQ-EF thing to produce some good SQL ?

Thx in advance!

Answer 1

You don't say what _DatabaseView is, but my wild guess based on your results is that it's not an ObjectQuery<T>. Which would explain your problem. ObjectQuery will convert to SQL; IEnumerable<T>.Skip() won't. Calling AsQueryable() on an enumerable is not enough to make this happen.

For example, this:

var foo = MyObjectContext.SomeEntitySet.AsEnumerable().AsQueryable().Take(10);

...won't put the TOP in the SQL.

But this:

var bar = MyObjectContext.SomeEntitySet.Take(10);

... will.

Once again: You haven't said what _DatabaseView is. Try this operation directly on your ObjectContext and you will see that it works. The bug is in the code you use to assign _DatabaseView, which you haven't shown us.