Returning Every Other Record with LINQ

Question

I have a query that returns 10000's of records that are used as plot points on a map. In an effort to reduce load, and increase app speed we're trying to implement what basically amounts to Level of Detail logic. Basically, when zoomed out, display 50% of the points. When zoomed in, display 100% of the points.

This is ultimately what I need the final SQL to look like:

SELECT *
FROM 
    (SELECT 
     [t0].[RecordName], 
     [t0].[LastMaintenanceDate]
     ,Row_Number() OVER (ORDER BY [t0].Ticker) as RowNumber 
     FROM 
      [dbo].[TableName] AS [t0]
    )as [t1]
WHERE RowNumber % 2 = 0

In LINQ I can use .Skip and .Take to get the Row_Number() Over part (example here), but when doing this, the Where criteria generated uses 'between' instead of the "where RowNumber % 2 = 0" that I need.

Am I approaching this correctly? In order to gain the full performance gains we're looking for here, this exclusion really needs to happen on the SQL server.

Answer 1

This is a very interesting problem, and unfortunately I don't know the answer using pure LINQ to SQL.

The only thing I can suggest is that LINQ to SQL does support ad-hoc raw SQL queries. Your problem may be a valid case for doing this.

Answer 2

The morelinq project has a TakeEvery function that does this, but this only works on IEnumerable.

It should be possible to create an extension method to solve this (I do not have my development environment available at the moment, so I cannot test it right now).

In your specific example I am guessing an extension method to Table would suffice.

If you wish to try this there is an 'example' of making an extension method to a table here.

Answer 3

What columns do you have in your table? If you for example have a int identity primary key column you could use that...

from mt in dc.MyTable
where mt.ID %2 == 0
select mt

...or...

where mt.SomeDataTime.Millisecond % 2 == 0

...that said, where are you trying to reduce load?

The T-SQL in your post, as well as the two solutions I have mentioned will all force full table scans, so if your table is large-ish then it would be better you can reduce records based on something indexed (and where the where clause predicate can actually use the index)...