Get LINQ to preload a complete table

Question

I need LINQ to grab a whole table, but this seems not to be working... everytime i select on the values via the pkey, a select is fired again..

So, actually this code:

        DataContext dc = new DataContext();

        dc.Stores.ToList();

        Store st = dc.Stores.SingleOrDefault(p => p.Id == 124671);

is making a

select * from store

at the "ToList()" method and an ADDITIONAL

select * from store where id = 124671

at the selection part below it...

Of course, i want to prevent it to make the second select..

How would i do it? (I DON'T want to store the ToList() result in an additional property like List< Store > )

UPDATE:

Regarding your answers that would mean, that:

Store st = stores.SingleOrDefault(p => p.Id == 124671);
Store st = stores.SingleOrDefault(p => p.Id == 124671);

would trigger 2 selects to the DB, which would make the LINQ-idea useless?! Or what am i getting wrong here?

I thought LINQ would basically save all the data i grabbed in the selects and ONLY performs another request when the data was not found in the "cache".. So, i thought of it like some kind of "magical" storagelayer between my application and the database..

UPDATE #2

Just that you get the idea.. i want to lose the performance at the beginning ( when grabbing all data ) and win it back alter when i select from the "cached" data...

Answer 1

Short answer - you don't need to have the ToList()

Long answer - your ToList() call is totally rundant in the supplied example. When working with Linq, nothing it actually executed on your database server until you actually enumerate the IQueryable.

If you just want to get a single record by PK, then just need to work with the data-context directly:

var theStore = dc.Stores.SingleOrDefault(p => p.Id == 124671);

If you actually want to get all of the records and iterate over them, the you can call ToList() on the table:

var allStores = dc.Stores.ToList()

If all you want is a single record, performing

var allStores = dc.Stores.ToList();
Store st = allStores.SingleOrDefault(p => p.Id == 124671);

is a complete failure. You are bringing all of the results into memory and then get .Net to filter the list, thus losing all benefits of indexes on your database etc.

Answer 2

(I'm assuming it knows that the id is the primary key etc)

Which exact version? That looks like LINQ-to-SQL; in 3.5 (no service packs), yes - the identity manager was a bit rubbish at this. It got better in 3.5 SP1, and is supposedly fixed in 4.0.

In .NET 3.5 SP1, the Single(predicate) approach works IIRC, so perhaps use that and catch block? A try/catch will be faster than a network hop.

From the later connect post:

This bug has now been fixed and will be included in .NET Framework 4.0. The optimization to search the cache first for ID-based lookups will now be done for Single/SingleOrDefault/First/FirstOrDefault(predicate) as well as Where(predicate).Single/SingleOrDefault/First/FirstOrDefault(), where predicate has the same restrictions as before.

Answer 3

Try this instead:

DataContext dc = new DataContext();

var allStores = dc.Stores.ToList();

Store st = allStores.SingleOrDefault(p => p.Id == 124671);

Answer 4

What you are doing is getting the result as a list, throw it away, and query the original again. That's why you get the double trips to the database.

The ToList method returns the list as a result, which you can store in a variable and query from:

var stores = dc.Stores.ToList();

Store st = stores.SingleOrDefault(p => p.Id == 124671);