CODEWITHSUNDEEP
c#linqasp.net-coreentity-framework-core
R. Warning
R. Warning

Reputation: 97

Dynamic Linq statement not working in EF core

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

Upvotes: 1

Views: 507

Answers (2)

R. Warning
R. Warning

Reputation: 97

Sorry to be late with this. Thanks for your help. But the problem was something different. In Asp.net.core you send the result via return new JsonResult(uit); to the client. The problem is that capitalized field names are changed to non capitalized names. so If you send them back to the server again with an Ajax call you have to Capitalize them again!

Upvotes: 0

Nkosi
Nkosi

Reputation: 247521

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

Upvotes: 2

Related Questions