Dynamic Linq statement not working in EF core

asp.net-core c# entity-framework-core linq

Question

Working on a asp net core 2.2 application. I want to dynamic order a query result. this is the code I have:

 public IActionResult OnGetRecords(int pagenum, int pagesize, string sortDataField, string sortOrder)
    {

        sortOrder = sortOrder ?? "asc";
        var Mut = from M in _DB.Mutations
                  join S in _DB.Shifts on M.ShiftId equals S.ShiftId
                  join U in _DB.RoosterUsers on M.UserId equals U.RoosterUserId
                  select new MutationModel
                  {
                      MutId=M.MutationId,
                      Naam=U.FirstName + " " + U.LastName,
                      UserId=M.UserId,
                      MutationType =S.publicName,
                      DateVan=M.DateStartOn,
                      DateTot=M.DateTill
                  };
        if (sortDataField != null)
        {
            if (sortOrder == "asc")
            {
                Mut = Mut.OrderBy(m => m.GetType().GetProperty(sortDataField).GetValue(m, null));
            }
            else
            {
                Mut = Mut.OrderByDescending(m => m.GetType().GetProperty(sortDataField).GetValue(m, null));
            }
        }

        int total = Mut.Count();
        var Tresult = Mut.Skip(pagenum * pagesize).Take(pagesize);

        var uit = new
        {
            TotalRows = total,
            Rows = Tresult
        };
        return new JsonResult(uit);
    }

}

But it is not working, when I try to order on a field, the line:

Mut = Mut.OrderBy(m => m.GetType().GetProperty(sortDataField).GetValue(m, null));

is not giving an error but is returning an result with out records. Is EF core different from the 'old' EF in this? somebody knows how to do this in EF Core

1
1
1/12/2020 11:49:57 AM

Popular Answer

m => m.GetType().GetProperty(sortDataField).GetValue(m, null)

Is not a valid expression for OrderByin this case that can be translated into valid SQL for EF to execute

You will need to use the sortDataField to build an expression dynamically to use with the OrderBy calls.

The following is done as an extension method for convenience

public static Expression<Func<TModel, object>> GetPropertyExpression<TModel>(this IEnumerable<TModel> model, string propertyName) {
    // Manually build the expression tree for 
    // the lambda expression m => m.PropertyName.

    // (TModel m) =>
    var parameter = Expression.Parameter(typeof(TModel), "m");
    // (TModel m) => m.PropertyName
    var property = Expression.PropertyOrField(parameter, propertyName);
    // (TModel m) => (object) m.PropertyName
    var cast = Expression.Convert(property, typeof(object));

    var expression = Expression.Lambda<Func<TModel, object>>(cast, parameter);
    return expression;
}

It builds up the expression tree for sorting that can then be used like

if (sortDataField != null) {
    //m => m.sortDataField
    var keySelector = Mut.GetPropertyExpression(sortDataField);

    if (sortOrder == "asc") {
        Mut = Mut.OrderBy(keySelector);
    } else {
        Mut = Mut.OrderByDescending(keySelector);
    }
}

to order the query

2
1/12/2020 11:38:30 AM


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