Entity Framework - sort by SQL function

Question

Is it possible to sort in Entity Framework by sql function from database? I mean something like:

 var xx = DbContext.Set<Article>()
      .Where(x=>x.Name.Contains("X")).OrderBy("[dbo].[MySQLFunction]");

Answer 1

var xx = DbContext.Set<Article>()
      .Where(x=>x.Name.Contains("X"))
      .Select(x=> new 
        {
           Article = x,
           orderItem = SqlFunction.//Any function you want to use.
                                   // Or may you want to use DbFunctions               
        })
      .OrderBy(x=>x.orderItem);

Here is more information about DbFunctions and SqlFunctions

---

Update:

By the way, if you mean to use your custom SQLFunction then I advise you to make a computed column in your table and make this column use this SQLFunction then you could OrderBy or filter against this field using normal Linq queries

Answer 2

The only option I found - is something like bad workaround, and I guess this is limited to simple queries.

var objectContext = ((IObjectContextAdapter)DbContext).ObjectContext;
var query = (System.Data.Objects.ObjectQuery)objectContext.CreateObjectSet<User>().Where(u => u.Id != Guid.Empty);

var initialQueryString = query.ToTraceString();
var resultQueryString = initialQueryString + " order by [dbo].[MySQLFunction]";

//careful here, if you use MS SQL you need to use SqlParameter instead of NpgsqlParameter
var paramValues = new List<NpgsqlParameter>();
foreach (var param in query.Parameters)
{
    paramValues.Add(new NpgsqlParameter(param.Name, param.Value));
}

var result = objectContext.ExecuteStoreQuery<User>(resultQueryString, paramValues.Cast<object>().ToArray()).ToList();