How can I convert this linq expression?

Question

Let's say I have an object that I want to query with ranking:

public class Person: Entity { public int Id { get; protected set; } public string Name { get; set; } public DateTime Birthday { get; set; } } 

In my request, I have the following:

 Expression<Func<Person, object>> orderBy = x => x.Name; var dbContext = new MyDbContext(); var keyword = "term"; var startsWithResults = dbContext.People .Where(x => x.Name.StartsWith(keyword)) .Select(x => new { Rank = 1, Entity = x, }); var containsResults = dbContext.People .Where(x => !startsWithResults.Select(y => y.Entity.Id).Contains(x.Id)) .Where(x => x.Name.Contains(keyword)) .Select(x => new { Rank = 2, Entity = x, }); var rankedResults = startsWithResults.Concat(containsResults) .OrderBy(x => x.Rank); // TODO: apply thenby ordering here based on the orderBy expression above dbContext.Dispose(); 

I tried to arrange the results before choosing an anonymous object with the Rank property, but the order ends up being lost. It seems that linq for entities discards the ordering of individual sets and converts back to natural ordering during Concat and Union .

What I can possibly do is dynamically transform the expression defined in the orderBy variable from x => x.Name to x => x.Entity.Name , but I'm not sure how:

 if (orderBy != null) { var transformedExpression = ??? rankedResults = rankedResults.ThenBy(transformedExpression); } 

How can I use Expression.Lambda to port x => x.Name to x => x.Entity.Name ? When I hardcode x => x.Entity.Name to ThenBy , I get the ordering I want, but orderBy provided by the calling class of the request, so I don't want to hardcode it. it is hardcoded in the above example just for ease of explanation.

Answer 1

This should help. However, for this you will need a specific type of anonymous type. My LinqPropertyChain will not work with it, since it will be difficult to create Expression<Func<Anonymous, Person>> , while its anonymous.

 Expression<Func<Person, object>> orderBy = x => x.Name; using(var dbContext = new MyDbContext()) { var keyword = "term"; var startsWithResults = dbContext.People .Where(x => x.Name.StartsWith(keyword)) .Select(x => new { Rank = 1, Entity = x, }); var containsResults = dbContext.People .Where(x => !startsWithResults.Select(y => y.Entity.Id).Contains(x.Id)) .Where(x => x.Name.Contains(keyword)) .Select(x => new { Rank = 2, Entity = x, }); var rankedResults = startsWithResults.Concat(containsResults) .OrderBy(x => x.Rank) .ThenBy(LinqPropertyChain.Chain(x => x.Entity, orderBy)); // TODO: apply thenby ordering here based on the orderBy expression above } public static class LinqPropertyChain { public static Expression<Func<TInput, TOutput>> Chain<TInput, TOutput, TIntermediate>( Expression<Func<TInput, TIntermediate>> outter, Expression<Func<TIntermediate, TOutput>> inner ) { Console.WriteLine(inner); Console.WriteLine(outter); var visitor = new Visitor(new Dictionary<ParameterExpression, Expression> { {inner.Parameters[0], outter.Body} }); var newBody = visitor.Visit(inner.Body); Console.WriteLine(newBody); return Expression.Lambda<Func<TInput, TOutput>>(newBody, outter.Parameters); } private class Visitor : ExpressionVisitor { private readonly Dictionary<ParameterExpression, Expression> _replacement; public Visitor(Dictionary<ParameterExpression, Expression> replacement) { _replacement = replacement; } protected override Expression VisitParameter(ParameterExpression node) { if (_replacement.ContainsKey(node)) return _replacement[node]; else { return node; } } } } 

Figured out a way to do this with less Explicite Generics.

 Expression<Func<Person, object>> orderBy = x => x.Name; Expression<Func<Foo, Person>> personExpression = x => x.Person; var helper = new ExpressionChain(personExpression); var chained = helper.Chain(orderBy).Expression; // Define other methods and classes here public class ExpressionChain<TInput, TOutput> { private readonly Expression<Func<TInput, TOutput>> _expression; public ExpressionChain(Expression<Func<TInput, TOutput>> expression) { _expression = expression; } public Expression<Func<TInput, TOutput>> Expression { get { return _expression; } } public ExpressionChain<TInput, TChained> Chain<TChained> (Expression<Func<TOutput, TChained>> chainedExpression) { var visitor = new Visitor(new Dictionary<ParameterExpression, Expression> { {_expression.Parameters[0], chainedExpression.Body} }); var lambda = Expression.Lambda<Func<TInput, TOutput>>(newBody, outter.Parameters); return new ExpressionChain(lambda); } private class Visitor : ExpressionVisitor { private readonly Dictionary<ParameterExpression, Expression> _replacement; public Visitor(Dictionary<ParameterExpression, Expression> replacement) { _replacement = replacement; } protected override Expression VisitParameter(ParameterExpression node) { if (_replacement.ContainsKey(node)) return _replacement[node]; else { return node; } } } } 

Answer 2

Since you order Rank first, and Rank values ​​are identical in each sequence, you should be able to just sort independently and then combine. It seems like the hiccup here would be that, according to your post, the Entity Framework does not support sorting by Concat or Union operations. You can get around this by causing concatenation to happen on the client side:

 var rankedResults = startsWithResults.OrderBy(orderBy) .AsEnumerable() .Concat(containsResults.OrderBy(orderBy)); 

This should make the Rank property unnecessary and, possibly, simplify the SQL queries executed in your database, and does not require cheating with expression trees.

The disadvantage is that after calling AsEnumerable() you no longer have the ability to add additional operations on the database side (that is, if you attach additional LINQ statements after Concat , they will use LINQ-to-collections). Looking at your code, I don't think this will be a problem for you, but it is worth mentioning.