6

I'm using LINQ to Entities to fetch elements from a MSSQL data base and I need them sorted by the last index of a string:

var data = db.GetEntities().
           OrderBy(e=>e.StringProperty.LastIndexOf("Foo")).ToList()

However, LINQ to Entities does not support LastIndexOf. I've tried searching for similar questions but all I've found was this which does not address my issue (ordering). Searching on MSDN did not yield any results.

What would be the simplest way to accomplish this using LINQ to Entities (I don't want to have to do this after the ToList()).

Community
  • 1
  • 1
user3198545
  • 63
  • 1
  • 4

2 Answers2

7

You could try

OrderBy(e => e.StringProperty.Length - EntityFunctions.Reverse(e.StringProperty).IndexOf("ooF"))

I think Reverse and IndexOf are supported.

Rawling
  • 49,248
  • 7
  • 89
  • 127
  • 1
    I think you would need to use [EntityFunctions.Reverse](http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions_methods(v=vs.110).aspx). – James Jan 15 '14 at 14:52
  • @James Thanks, regular `Reverse` gives you an `IEnumerable` which isn't much use :) – Rawling Jan 15 '14 at 14:54
  • If the string is not found, the result would by the length, not zero. But otherwise great solution. – MarcelDevG Jan 15 '14 at 14:56
  • I mean -1, not zero if the string is not found. – MarcelDevG Jan 15 '14 at 15:05
  • @MarcelDevG You're right, although I'm sure it's not too hard to work around :) – Rawling Jan 15 '14 at 15:25
  • In EF6, `EntityFunctions` has been replaced by [`DbFunctions`](http://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions%28v=vs.113%29.aspx). – Pluto Aug 30 '14 at 18:35
4

Do the sorting using LINQ to Objects

var data = db.GetEntities()
    .AsEnumerable()
    .OrderBy(e => e.StringProperty.LastIndexOf("Foo"))
    .ToList();

Using AsEnumerable will allow you to retain deferred execution, it's the better compromise than calling ToList and then performing the OrderBy.

James
  • 80,725
  • 18
  • 167
  • 237
  • 1
    That misses the point completely - OP specifically asked `I don't want to have to do this after the ToList()` meaning he doesn't want to enumerate the entities. This misses the point of EF – Benjamin Gruenbaum Jan 15 '14 at 14:33
  • 1
    @BenjaminGruenbaum I think you maybe misunderstand how `AsEnumerable` works, it doesn't enumerate the entities, the query is still only evaluated when `ToList` is called... – James Jan 15 '14 at 14:35
  • But the sorting would be on the client, not on the server – MarcelDevG Jan 15 '14 at 14:57
  • @MarcelDevG yes it would, however, it's not always such a bad idea, EF is restricted by the DB so sometimes complicated queries can't be done server-side. If it's not a massive amount of data I don't really see what the problem is. – James Jan 15 '14 at 15:03
  • @James, True, if it is only sorting, and in some cases it's even preferable. But if you combine with a where clause, I would not do it this way. – MarcelDevG Jan 15 '14 at 15:07
  • @MarcelDevG you would simply do the `Where` before you call `AsEnumerable` though. – James Jan 15 '14 at 15:09