Ordering query using lambda expression and Include method

entity-framework entity-framework-core

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?

1
0
9/27/2018 1:03:09 PM

Accepted Answer

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();
0
10/1/2018 2:06:23 PM

Popular Answer

You cannot do sorting (OrderBy) inside the Include method. Sort the data after the query.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow