Order by child records using dynamic field with entity framework

Question

I am interested in sorting a query using entity framework and am having trouble sorting using a dynamic order by expression,

I have a parent record (DatasetRecord) with a one to many relationship consisting of a number of associated values (DatasetValues), I would like to be able to sort based on the data stored within the value if it matches a certain field,

So I need to sort by the Value of DatasetValues where it matches a supplied FieldID

public IEnumerable<DatasetRecordDto> FetchData(ModuleSettingsDto settings)
{
    var query = _context.DatasetRecords.AsQueryable().Where(r => r.DatasetId == settings.DatasetId && r.IsDeleted == false);

    foreach (var filter in settings.Filters)
    {
        // this works fine, check we have current field and value matches
        query = query.Where(i => i.DatasetValues.Any(x => x.DatasetFieldId == filter.DatasetFieldId && x.Value == filter.Value));
    }

    foreach (var sort in settings.Sort)
    {
        switch (sort.SortDirection)
        {
            case "asc":
                // THIS IS WHERE I NEED THE DYNAMIC ORDER BY EXPRESSION, 
                // THIS SHOULD SORT BY DATETIME WHERE FIELD ID MATCHES
                query = query.OrderBy(i => i.DatasetValues.Any(x => x.ValueDateTime && x.DatasetFieldId == sort.DatasetFieldId));
                break;
        }

    }

    return ShapeResults(query);
}

And here is my database schema:

CREATE TABLE [dbo].[DatasetRecords](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DatasetId] [int] NOT NULL
)

CREATE TABLE [dbo].[DatasetFields](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Label] [varchar](50) NOT NULL,
    [DatasetId] [int] NULL,
    [DataType] [int] NOT NULL
)

CREATE TABLE [dbo].[DatasetValues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DatasetRecordId] [int] NOT NULL,
    [DatasetFieldId] [int] NOT NULL,
    [ValueString] [varchar](8000) NULL,
    [ValueInt] [int] NULL,
    [ValueDateTime] [datetime] NULL,
    [ValueDecimal] [decimal](18, 2) NULL,
    [ValueBit] [bit] NULL
)

Please let me know if you need any further info, any help is greatly appreciated,

UPDATE: I am not having an issue with creating a dynamic order by expression, I am trying to apply a conditional sort order for parent records based on the values in a child table

have you seen this: [link](http://stackoverflow.com/questions/307512/how-do-i-apply-orderby-on-an-iqueryable-using-a-string-column-name-within-a-gene) — Jens Kloster, Feb 07 '13 at 09:58

score 0 · Answer 1 · answered Jul 12 '13 at 14:38

0

Have you tried running the Linq code on something like LinqPad, it lets you run Linq code against a dataset and shows you the generated SQL. You can take that SQL and run it manually to make sure it isn't some invalid SQL that is causing the error.

answered Jul 12 '13 at 14:38

Joshua G

2,086
3
19
22

Order by child records using dynamic field with entity framework

1 Answers1