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]");

I think, you should use raw query in this case. stackoverflow.com/questions/18096519/… — vmeln
– vmeln, Commented Feb 12, 2014 at 10:36
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
– Peter Smith, Commented Feb 12, 2014 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
– foxiter, Commented Feb 12, 2014 at 10:45
To add more details: I have to use Code First (so, without TVF) and SQL function which uses parameters. — foxiter
– foxiter, Commented Feb 13, 2014 at 11:26

Wahid Bitar · Accepted Answer · 2016-10-08 10:41:27Z

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

edited Oct 8, 2016 at 10:41

answered Feb 12, 2014 at 13:54

Wahid Bitar

14.1k13 gold badges83 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

foxiter Over a year ago

I use Code First so I cant write complex computed column property which executes SQL Function (with parameters).

FireAlkazar · Accepted Answer · 2014-02-13 10:26:39Z

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();

answered Feb 13, 2014 at 10:26

FireAlkazar

1,8901 gold badge16 silver badges30 bronze badges

2 Comments

foxiter Over a year ago

It's very interesting but as you mention, only for not very complex query and it's a little risky :)

FireAlkazar Over a year ago

@foxiter yeap, but still very strange we can't do that by dynamic linq or something like that

Collectives™ on Stack Overflow

Entity Framework - sort by SQL function

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related