Does ORM (Linq-SQL EF) load entire data set from table?

Question related to how ORM (Linq to SQL,EF 4) handles loading of data. Is the entire data set loaded, if so can we prevent the entire data set from loading and load a subset? For example when instantiating the context and qu开发者_如何学Cerying it seems like there would be a huge performance issue if all data was returned (hundreds of thousand records) versus instantiating a connection, executing a proc for given subset of data and querying for this subset of data. I have not seen many threads address this, thanks in advance!

---

No it doesn't, as Linq to Sql/Eft will parse the query to produce an optimized sql query to retrive your data, with that being said a misuse of a extension method (which calls GetEnumrator()) or query might end up loading all items from a Table.

Misuse of extension methods that call GetEnumerator()

context.Books.ToList().Where(somePredicate).Select(someSelector);

This will cause the whole Books table to be loaded, since the ToList() will invoke the sql query to retrive all Book objects. From there you Where and Select are actually operating over Linq to objects and not Linq To Sql/Entities.

context.Books.Where(somePredicate).Select(someSelector).ToList();

Simply pushing your .ToList() to the end of the statement will cause EF to parse the query into a valid sql query and retrieve a portion of your books when .ToList() is invoked.

Misuse of Func<T, bool>

Consider the following,

Func<Book, bool> predicate = b=>b.Id == 3;
context.Where(predicate).ToList();

Now this looks perfectly valid, however once you run the query you will find the context is loading all Books, what gives? Simply put EF can not parse the Func<T, bool> predicate to valid sql syntax since a Func<> is just a delegate. This simple mistake can lead to EF loading all Books into the context and then running Linq to Objects against the loaded Books.

Expression<Func<Book, bool>> predicate = b=>b.Id == 3;
context.Where(predicate).ToList();

Now here we are going to use an Expression<Func<Book, bool>> which is simply an expression tree, EF knows how to parse an expression tree and is able to create a valid sql statement that only loads one Book with and Id of 3.

I am sure other people will be able to chime in with a few more examples that can cause the whole Table to be loaded, however these are the most likely you will run into.

---

If I understand your question, Linq 2 Sql does not load everything in the data set. Once you create your context and build your linq 2 sql statement, only the tables referenced in your statement will be pulled back unless you explicitly tell the dataContext to load related tables using the DataLoadOptions.

Now if you are talking about paging, use Skip and Take.

Someone else can chime in on EF as I don't have much experience with it, but I can't imagine it would be too different. I love Linq 2 sql though and it has been plenty fast for me.