Building LINQ Expression Getting Ignored

Question

I'm trying to build a linq query coming from a table grid from the client side, so im expecting page offset, page start, order and the traditional paging parameters. I have the following code:

  [Route("api/settings/logs")]
   public Rest.DatatablesResponse GetLogs(int draw, int start, int length) {

       var query_string = Request.GetQueryNameValuePairs().ToDictionary(x => x.Key, x => x.Value);
       var search = query_string["search.value"];

       int order_column = int.Parse(query_string["order[0].column"]);
       var order_direction = query_string["order[0].dir"];

       var count = db.Logs.Count(q => q.Mode == 2);
       var logs = (from l in db.Logs
                   where l.Mode == 2
                   select new {
                       id = l.ID,
                       mode = l.Mode,
                       phase_id = l.Phase.ID,
                       created = l.Created,
                       user = l.User.Name,
                       blender_name = l.Blender.Name,
                       oil_name = l.Oil,
                       oil_quantity = l.OilQuantity,
                       production_cycle_name = l.ProductionCycle.Name
                   });

       if (order_direction == "asc") {
           if (order_column == 0) logs.OrderBy(q => q.created);
           else if (order_column == 2) logs.OrderBy(q => q.production_cycle_name);
       } else {
           if (order_column == 0) logs.OrderByDescending(q => q.created);
           else if (order_column == 2) logs.OrderByDescending(q => q.production_cycle_name);
       };

       if (!string.IsNullOrEmpty(search)) {
           logs.Where(q => q.blender_name.Contains(search) ||
                      q.oil_name.Contains(search) ||
                      SqlFunctions.StringConvert((decimal)q.id).Contains(search));
       }

      logs.Skip(start).Take(length);




        DateTime dtDateTime = new DateTime(1970,1,1,0,0,0,0,System.DateTimeKind.Utc);


        var steps = from l in logs.ToList()
                   select new {
                      id = l.id,
                      message = StringHelpers.FormatWith(_tpl_message[l.phase_id.ToString() +  l.mode.ToString() ], l) ,
                      created = dtDateTime.AddSeconds(l.created).ToString("h:mmtt - MMMM d, yyyy"),     
                      production_cycle_name = l.production_cycle_name
                   };

       return new Rest.DatatablesResponse {
           draw = draw,
           recordsTotal = count,
           recordsFiltered = count,
           data = steps.ToArray()
       };
   }

My problem is the skip and take and orderby expressions are getting ignored for some reason, and this is the SQL code generated just before converting my linq expressions to a list. From my understanding, the query should not be executed or evaluated until my logs.ToList() call, so the ordering and take/skip should be taken into account, but it's not:

{SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Mode] AS [Mode], 
    [Extent1].[Phase_ID] AS [Phase_ID], 
    [Extent1].[Created] AS [Created], 
    [Extent2].[Name] AS [Name], 
    [Extent3].[Name] AS [Name1], 
    [Extent1].[Oil] AS [Oil], 
    [Extent1].[OilQuantity] AS [OilQuantity], 
    [Extent4].[Name] AS [Name2]
    FROM    [dbo].[Steps] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[User_Id] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Blenders] AS [Extent3] ON [Extent1].[Blender_ID] = [Extent3].[ID]
    LEFT OUTER JOIN [dbo].[ProductionCycles] AS [Extent4] ON [Extent1].[ProductionCycle_ID] = [Extent4].[ID]
    WHERE 2 = [Extent1].[Mode]}

Irrelevant P.S. I'm using the not so clever ifs for building the order expression instead of using DynamicLINQ since i have only two sortable columns.

Answer 1

logs.Skip(start).Take(length);

Creates a IQueryable<T> where T is the same anonymous type of which logs is an IQueryable<T> but with start items skipped. Then from that it creates a similarly typed IQueryable<T> where lenght items are the most that will be taken.

Then it throws that away and lets it be garbage collected. (Or ideally the compiler or jitter steps will realise it's thrown away and cut out the whole thing).

Then logs.ToList() goes back to the logs you still have and creates a list from it.

You should replace the Skip and Take line with:

logs = logs.Skip(start).Take(length);

So that you are actually making use of this skipping and taking.

I'm using the not so clever ifs for building the order expression instead of using DynamicLINQ since i have only two sortable columns.

There's nothing particularly not-clever about that, except that you make the same mistake; apply the OrderBy and then throwing away the result instead of using it. Likewise with the Where. You need logs = logs.OrderBy(...) etc.

I'd also question from l in logs.ToList() select new {…} here.

It might be the best approach, if obtaining that list in one step has some advantage. However otherwise:

from l in logs select new {…}

Do the select work on the database, retrieving just what you need.

from l in logs.AsEnumerable() select new {…}

Do the select work in the application, appropriate if part of it cannot be converted to database work, but do it as it comes rather than loading it all into memory first.

from l in await logs.ToListAsync() select new {…}

Has the downside of ToList() but in asynchronous uses, then (assuming your provider has a ToListAsync() method) allows for awaiting.

ToList() is rarely the best option here.