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

Try this instead:

DataContext dc = new DataContext();

var allStores = dc.Stores.ToList();

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

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.