0

How can I implement the equivalent to a custom IComparer for Linq to Entities OrderBy()?

A custom comparer is not supported in Linq to Entities as MSDN documentation states:

Most of the LINQ ordering methods are supported in LINQ to Entities, with the exception of those that accept an IComparer, because the comparer cannot be translated to the data source. For more information, see Standard Query Operators in LINQ to Entities Queries.

bdukes
  • 152,002
  • 23
  • 148
  • 175
Jim
  • 3
  • 3
  • You'll need to give some more information about what you're trying to do. Depending on the exact reason you need a custom comparison, the implementation may be different. – bdukes May 17 '12 at 21:23
  • I need it for 2 cases. (1) for a query within an algorithm for a webservice and (2) as part of of a query that populates a SelectList for a view. Is that enough clarification? – Jim May 17 '12 at 21:32
  • I am comparing Int64 Times (TimeSpan ticks). – Jim May 17 '12 at 21:38
  • Is a ToList() applied to the resultset prior to the orderby safe or am I introducing side effects? The restriction seems to apply to IQueryable extension methods and not IEnumerable. – Jim May 17 '12 at 21:42
  • If you call `ToList` (or `AsEnumerable`), the sorting will work, but it will happen on the server, not in the database. – bdukes May 18 '12 at 14:24
  • That is performance concern but I am not sure I can avoid it given the complexity i use in the comparer. Does that mean that the whole query gets executed on the server or just from the prior resultset onward to `ToList`/`AsEnumerable`? – Jim May 18 '12 at 16:16
  • Everything up to the point where it switches from `IQueryable` to `IEnumerable` will happen in the database. – bdukes May 18 '12 at 16:45
  • K good. At least the performance hit is minimized. Tx! – Jim May 18 '12 at 16:55

1 Answers1

1

If you want to use a custom IComparer, you need to first ask yourself whether it's possible to do the sorting in the database. If it's not, just call AsEnumerable on the sequence, and then sort it on the server.

If you can (and want to) sort it in the database, then you'll need to think through what conversions need to be made so that the sorting can happen. If you have a complex conversion, you could write it as a SQL function, and add it to your data context:

from e in context.Entities
let hours = context.Get32HourValue(e.Time)
orderby hours
select e
bdukes
  • 152,002
  • 23
  • 148
  • 175
  • Tks bdukes. The reason i need a custom comparer is because I am evaluating time based on a `32` hour clock and am making `0-8` and `24-32` hours equivalent in the comparison. If I can somehow make this a store operation I would be thrilled but I am not sure I can unless I write TSQL and invoke it instead of the custom comparer. – Jim May 18 '12 at 16:21
  • Would it work to create a function in SQL and add it to your db context, to generate the value to sort on? – bdukes May 18 '12 at 16:48
  • I am not sure how you mean that. Create a function in SQL Server? If so, that should work but I prefer to keep the code in the VS Project. If you mean that there is a way to code a SQL function within VStudio (`SQLCommand?`), that might be an option. If you mean anything other, I would love to hear it! (thanks for your input!) – Jim May 18 '12 at 17:03
  • Right, I was talking about writing a function in the database (and, I agree, it's preferable to keep the code in the application). That was just the only workaround that I could see to have it all running in the database. – bdukes May 18 '12 at 17:04