5

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]");
foxiter
  • 51
  • 4
  • 1
    I think, you should use raw query in this case. http://stackoverflow.com/questions/18096519/entity-framework-raw-sql-query – vmeln Feb 12 '14 at 10:36
  • 2
    I'm fairly certain this is not possible and you can't mix LINQ/delta notation with SQL although you can use SQL queries via EF. Can you add you're SQLFunction as a C# function? – Peter Smith Feb 12 '14 at 10:37
  • I have to take same data with paging but it should be sorted by complex function . I think I could write it in multiple complex expressions, but can't merge into one expression. – foxiter Feb 12 '14 at 10:45
  • To add more details: I have to use Code First (so, without TVF) and SQL function which uses parameters. – foxiter Feb 13 '14 at 11:26

2 Answers2

3
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

Wahid Bitar
  • 13,776
  • 13
  • 78
  • 106
  • I use Code First so I cant write complex computed column property which executes SQL Function (with parameters). – foxiter Feb 13 '14 at 11:21
1

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();
FireAlkazar
  • 1,795
  • 1
  • 14
  • 27