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?
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 FieldSetGroup
with 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 ofInclude
and select only the data you actually plan to use. UseInclude
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();
You cannot do sorting (OrderBy
) inside the Include
method. Sort the data after the query.