Search code examples
c#linq-to-sqlcaching

How to store results of a LINQ To SQL query in memory

I was wondering how I can pull down a portion of a table using LINQ To SQL into C# memory space. Let's say I have a database with thousands of records and a GUI interface that shows one record at a time.

When the user clicks the next / previous record button on the GUI the LINQ To SQL datacontext has to go and get the next record - this takes time.

What I would like to do is get a small block of records, say the last n records and store them in memory so the next and previous buttons react much faster.

What I have tried is create a new field of type Table <vw_RawData> called Table_RawData where vw_RawData is a table in my database and was created by Visual Studio when building the datacontext. Then I tried to fill it (onLoad) using

this._Table_Rawdata = (Table<vw_RawData>) 
from c 
in this._Db.vw_RawDatas 
where c.Date > DateTime.Now.AddMonths(-1) 
select c;

This is compiling OK, but at runtime I get the following error... help??

System.InvalidCastException: Unable to cast object of type 'System.Data.Linq.DataQuery1[vw_RawData]' to type 'System.Data.Linq.Table1[vw_RawData]'.

Solution

  • You can easily get block of records using Take and Skip methods:

    yourTable.OrderBy(x => x.Date).Skip(5).Take(3)

    This will return 3 records starting from record number 6.