All geek questions in one place

LINQ or Entity Framework creates unlimited SQL statement

I am working on optimizing the speed of my application, and I found that LINQ (or EF) creates for me some strange SQL that is slow.

Here is the 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 theoretically, he should have created an SQL statement that takes 25 records, but the profiler shows the following SQL for him:

    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 an IQueryable object that runs all of my sorting and filtering logic.

Here's what I realized: if I do not do the filtering, then SQL works fine with SELECT TOP (25). But whenever I do the filtering, something gets messed up. Here is the code for 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 this is without any restrictions. How to do this LINQ-EF thing to create good SQL?

thanks in advance!

+3
5

, _DatabaseView, , , , ObjectQuery<T>. . ObjectQuery SQL; IEnumerable<T>.Skip() . AsQueryable() , .

, :

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

... TOP SQL.

:

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

... .

: , _DatabaseView. ObjectContext, , . , _DatabaseView, .

+3

, SQL, , - SQL.

, Skip Take LINQ SQL. , - , LINQ.

-

(From l In DataBaseView Select 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)

, .

. - , , SQL, 25 .

+2

LINQ Parser Skip Take LINQ to Entities , Object Services , SQL-.
2 SQL WHERE [Extent1].[row_number] > 50 SELECT TOP (25) Skip Take .

, ? ObjectQuery.ToTraceString, , Profiler, sql:

var query = _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));
string sql = (query as ObjectQuery).ToTraceString();
+2

If you cannot get SQL to work well enough with the corresponding indexes, you can always try writing a stored procedure and simply calling it from LINQ.

0
source

Try moving Skip and Take to choice.

0
source

Source: https://habr.com/ru/post/1771318/

More articles:


All Articles