Returning each record using LINQ
I have a query that returns 10,000 records that are used as plot points on a map. In order to reduce the load and increase the speed of the application, we are trying to implement what basically constitutes a Level of Detail . Basically, when zoomed in, display 50% of the points. When zoomed in, display 100% of the dots.
Ultimately I need the final SQL so that it looks like this:
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 so, the Where criteria I create use "between" instead of "where RowNumber% 2 = 0" which I want.
Did I fit right? To get the full performance we're looking for here, this exception really has to happen on the SQL server.
source to share
What columns do you have in the table? If you, for example, have an int id primary key column, you can use this ...
from mt in dc.MyTable
where mt.ID %2 == 0
select mt
... or...
where mt.SomeDataTime.Millisecond % 2 == 0
... said where are you trying to reduce the load?
The T-SQL in your post as well as the two solutions I mentioned will force a full table scan, so if your table is large, then it would be better that you can shrink the records based on the indexed ones (and where the where where predicate might actually use an index) ...
source to share
The morelinq project has a TakeEvery that does this, but this only works on IEnumerable.
It should be possible to create an extension method to solve this problem (I don't have my development environment available at the moment, so I can't test it right now).
For your specific example, I assume an extension method for the table will suffice.
If you want to give it a try, there is an example of using an extension method for a table here .
source to share