C# Expression to sort generic query by key field

Question

I have a generic method in which I want to sort an IQueryable<T> by its key field (it is safe to assume there is only one). Thus:

void DoStuff<T>(...) 
{
   IQueryable<T> queryable = ... // given
   PropertyInfo keyField = ... // given
   var sortedQueryable = queryable.OrderBy(<some expression here>);
   ...
}

How do I define an Expression that will return the keyField property of T so that this will work?

@DavidG The problem here is not expression itself but the engine that will try to convert it to `SQL` such as `Linq To SQL` or other — Fabjan, Mar 25 '19 at 16:36
@Fabjan I know, I'm saying it's possible to build an expression to do it - I've done this myself more than once. — DavidG, Mar 25 '19 at 16:37
@DavidG You would better write a good answer to this question then if you know *exactly* how to do it — Fabjan, Mar 25 '19 at 16:39
@AleksAndreev: It says in the question that `PropertyInfo keyField` is a "given." — Robert Harvey, Mar 25 '19 at 16:46
@shaul It seems you deleted your follow up question - did you get it sorted? — DavidG, Mar 26 '19 at 11:29
@DavidG I asked prematurely; I had made a stupid mistake. Still working on it. Thanks for asking! — Shaul Behr, Mar 26 '19 at 11:34

score 9 · Accepted Answer · answered Mar 25 '19 at 16:45

9

This isn't too difficult, but you need to invoke the OrderBy with reflection as you don't know the type of the key field ahead of time. So given the code you already show, you would do something like this:

// Build up the property expression to pass into the OrderBy method
var parameterExp = Expression.Parameter(typeof(T), "x");
var propertyExp = Expression.Property(parameterExp, keyField);
var orderByExp = Expression.Lambda(propertyExp, parameterExp);

// Note here you can output "orderByExp.ToString()" which will give you this:
//  x => x.NameOfProperty

// Now call the OrderBy via reflection, you can decide here if you want 
// "OrderBy" or "OrderByDescending"
var orderByMethodGeneric = typeof(Queryable)
    .GetMethods()
    .Single(mi => mi.Name == "OrderBy" && mi.GetParameters().Length == 2);

var orderByMethod = orderByMethodGeneric.MakeGenericMethod(typeof(T), propertyExp.Type);

// Get the result
var sortedQueryable = (IQueryable<T>)orderByMethod
    .Invoke(null, new object[] { queryable, orderByExp });

answered Mar 25 '19 at 16:45

DavidG

113,891
12
217
223

Can't we just build the `Expression>` instead and pass it to the "normal" `OrderBy()` method? – haim770 Mar 25 '19 at 16:50
@haim770 Sure if you know the type of `TProp` ahead of time. I'm assuming we don't – DavidG Mar 25 '19 at 16:50
1

You're right. But since `DoStuff()` *does* define a generic parameter than can be resolved as `TEntity`, there's a chance `keyField` points to a property of constant type that can be resolved as `TProp`. Anyway, great answer. – haim770 Mar 25 '19 at 17:01
@haim770 I was assuming `keyField` is somehow pulled out of the EF model, it's hard to guess without OPs code for doing that. – DavidG Mar 25 '19 at 17:02
@DavidG OK, I've reopened the [follow-up question](https://stackoverflow.com/q/55355917/7850). The Guid comparison stuff is still problematic. – Shaul Behr Mar 26 '19 at 12:00

Noel · Answer 2 · 2019-03-25T23:16:07.880

1

I like to use an interface IBaseEntity that has an Id property of type T. That would make your query:

void DoStuff<IBaseEntity>(...) 
{
   IQueryable<IBaseEntity> queryable = ... // given
   var sortedQueryable = queryable.OrderById(e=> e.Id); //extension method
   ...
}

Extension Method:

public static IQueryable<IBaseEntity<T>> OrderById<T>(this IQueryable<IBaseEntity<T>> query)
{
   return query.OrderBy(e => e.Id);
}

Each entity would implement IBaseEntity and have something like

public partial class MyEntity : IBaseEntity<long>
{
  [Required]
  public override long  Id 
  {
    get { return base.Id;}
    set { base.Id = value;}
  }
}

Then in the context

modelBuilder
  .Entity<MyEntity>()
  .ToTable("DatabaseTable", "DatabaseSchema")
  .HasKey(e => e.Id)
  .Property(e => e.Id)
  .HasColumnName("DatabasePrimaryKey")                
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

Note: This requires some setup in the context and entities. The database can have whatever keys you want, you're mapping them individually to the property Id in the OnModelCreating method of the context.

edited Mar 25 '19 at 23:16

answered Mar 25 '19 at 16:42

Noel

600
16
37

This is OK if you know the name of the property is always going to be the same. In practice though, this is rarely the case. – DavidG Mar 25 '19 at 16:46
As @DavidG says, my key fields are all named differently, i.e. `CustomerId`, `InvoiceId` etc. – Shaul Behr Mar 25 '19 at 16:47
@ShaulBehr - I use the context to make the keys the same as far as entity framework is concerned, but they map to the named primary key in the database. – Noel Mar 25 '19 at 17:03
Doesn't help if the property type isn't consistent. It may be `int` for one entity but `Guid` or `string` elsewhere. – DavidG Mar 25 '19 at 18:13
@DavidG - I simplified in the answer, but what we've done is IBaseEntity Where T is the type of primary key – Noel Mar 25 '19 at 18:32
OK, but if you have `IBaseEntity` then your code won't compile. – DavidG Mar 25 '19 at 19:03
@DavidG - I updated my answer. The query has to be IQueryable>, and I threw in an extension method. This probably doesn't work for projected queries, but does work on queries that return entities that inherit from IBaseEntity – Noel Mar 25 '19 at 23:19

score 1 · Answer 3 · answered Mar 25 '19 at 16:59

Same idea as in DavidG answer but different approach

// build lambda expression (T t) => t.KeyField
var type = typeof(T);
var parameter = Expression.Parameter(type, "k");
var lambda = Expression.Lambda(Expression.Property(parameter, keyField), parameter);

// get source expression
var baseExpression = queryable.Expression;

// call to OrderBy
var orderByCall = Expression.Call(
    typeof(Queryable),
    "OrderBy",
    new[] {type, keyField.PropertyType},
    baseExpression, lambda
);

// sorted result
var sorted = queryable.Provider.CreateQuery<T>(orderByCall);

C# Expression to sort generic query by key field

3 Answers3

Linked