The most efficient collection for storing data from LINQ in Entities?

Question

Over the years, I read several different sources that show that when storing a collection of data, it is List<T>effective when you want to insert objects, and IEnumerable<T>it is best to list by collection.

LINQ-to-Entities has a function AsEnumerable()that will return IEnumerable<T>, but will not allow, the SQL generated by the LINQ statement until you start enumerating through the list.

What if I want to store objects from LINQ to Entities in a collection and then request this collection later?

Using this strategy causes SQL to be resolved by adding a WHERE clause and querying each record separately. I specifically don't want to do this because I am trying to limit network chatter:

var myDataToLookup = context.MyData.AsEnumerable();

for(var myOtherDatum in myOtherDataList)
{
    // gets singular record from database each time.
    var myDatum = myDataToLookup.SingleOrDefault(w => w.key == myOtherDatum.key)        
}

How do I enable SQL formatting, so myDataToLookup actually contains data in memory? I tried ToArray:

var myDataToLookup = context.MyData.ToArray();

But I recently found out that it actually uses more memory than ToList: Is it better to call ToList () or ToArray () in LINQ queries?

Should I use a connection instead?

var myCombinedData = from o in myOtherDataList
                     join d in myDataToLookup on
                         o.key equals d.key
                     select { myOtherData: o, myData: d};

Should I use ToDictionaryand store the key as a key for the dictionary? Or am I too worried about this?

Answer 1

LINQ to Entities, , ToArray , ToList. LINQ to Entities .

. , . . , , . , , , , . , .

Answer 2

IEnumerable, ICollection IList/IDictionary , . . IEnumerable . ICollection IList, , , -. . .

, . , , Entity . .

, .

, , .

SO, , , , .

Answer 3

, , , .

, 100 . 1 ( ).

, . , 1. , 1, . 1 10 .

10 , 3 .

, 2 - 10 . , IEnumberable . , 1 1 .

, .