0

I need to do basically what is done here

SELECT t.id, t.key
FROM
(
    SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
    FROM datatable
) AS t
WHERE t.rownum % n = 0   
ORDER BY t.key

but using Linq to Entities. I just can't figure out how. A method ROW_NUMBER is not known and using an index in the where clause gives me an error.

Query.Where((s, i) => i % n == 0);

Edit: more precisely

Query.Select((s, i) => new { index = i, data = s } ).Where(s => s.index % n == 0).Select(s => s.data)

The only solutions that worked for me so far is to temporarily run the SQL (toList) and filter it afterwards. This however slows down my server significantly.

rst
  • 2,510
  • 4
  • 21
  • 47
  • EF is an ORM, not a replacement for SQL. It won't provide every available method and function nor should it. Like any ORM, its job is to Map Objects to Relational entities like tables and views. Create a view or stored procedure that returns the results you want and map to it – Panagiotis Kanavos Feb 06 '18 at 16:14
  • Take a look at the raw SQL capabilities in EF also: https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx – Ian Mercer Feb 06 '18 at 16:22
  • What error do you get with the `Where` that has an index? – Sergey Kalinichenko Feb 06 '18 at 17:13
  • @dasblinkenlight That would presumably be a not supported exception for trying to use the indexed `Where` on a SQL data provider. – NetMage Feb 06 '18 at 20:10
  • 1
    @Alex: indeed, it's basically the same problem. Unfortunately, I can't use the proposed solution but they already mention the impossibility of my endeavor – rst Feb 07 '18 at 09:10

0 Answers0