Ordering query using lambda expression and Include method

Question

I am working on a Entity-Framework-Core 2.0 query. The query needs to sort 2 tables by the "order" field. So far that's what I have:

return await _context.FieldsetGroup
                .Include(e => e.Fieldsets.OrderBy(o => o.Order))
                .ThenInclude(e => e.FieldsetFields.OrderBy(o => o.Field.Order))
                .ThenInclude(e => e.Field)
                .FirstOrDefaultAsync(fsg => fsg.FieldsetGroupId == fieldSetGroupId);

This query returns an exception: "The property expression 'e => {from Fieldset o in e.Fieldsets orderby [o].Order asc select [o]}' is not valid. The expression should represent a property access: 't => t.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393."

How can I sort the 2 tables?

either use the .Join method which allows you to join modified DbSet/IQueryables or sort the navigation property after retrieval of your object. — DevilSuichiro

Harald Coppoolse Harald Coppoolse · Accepted Answer · 2018-10-01T13:13:45

One of the slower parts of database queries is the transport of your selected data from the DBMS to your local process. Hence it is wise to limit the amount of transferred data.

Apparently your FieldSetGroup has zero or more FieldSets. Each FieldSet belongs to exactly one FieldsetGroup. This is identified by the foreign key FieldSetGroupId. The value of this field equals the Id of the FieldSetGroup.

So if you have FieldSetGroupwith Id = 10, and this FieldSetGroup has, 1000 FieldSets, then every FieldSet will have a value of foreign key FieldSetGroupId of 10. No need to transfer this value 1000 times.

Advice: To limit the amount of transferred data, avoid transferring more data than needed, use Select instead of Include and select only the data you actually plan to use. Use Include if you plan to update the fetched data.

If you use Select, you can order whatever you want:

var result = dbContext.FieldsetGroup
   .Where((fieldSetGroup => fieldSetGroup.FieldsetGroupId == fieldSetGroupId)
   .Select(fieldSetGroup => new
   {
       ... // select the fieldSetGroup properties you plan to use

       FieldSets = fieldSetGroup.FieldSets
           .OrderBy(fieldSet => fieldSet.Order)
           .Select(fieldSet => new
           {
                ... // only select the fieldSet properties you plan to use

                FieldSetFields = fieldSet.FieldSetFields
                    .OrderBy(fieldSetField => fieldSetField.Order)
                    .Select(fieldSetField => new
                    {
                        ...
                    })
                    .ToList(),
                })
                .ToList(),
           })
           .ToList(),
    })
    .FirstOrDefault();

Ordering query using lambda expression and Include method

2 Answers