Entity Framework efficient querying

Question

Lets say I have a model, Article that has a large amount of columns and the database contains more than 100,000 rows. If I do something like var articles = db.Articles.ToList() it is retrieving the entire article model for each article in the database and holding it in memory right?

So if I am populating a table that only shows the date of the entry and it's title is there a way to only retrieve just these columns from the database using the entity framework, and would it be more efficient?

According to this,

There is a cost required to track returned objects in the object context. Detecting changes to objects and ensuring that multiple requests for the same logical entity return the same object instance requires that objects be attached to an ObjectContext instance. If you do not plan to make updates or deletes to objects and do not require identity management , consider using the NoTracking merge options when you execute queries.

it looks like I should use NoTracking since the data isn't being changed or deleted, only displayed. So my query now becomes var articles = db.Articles.AsNoTracking().ToList(). Are there other things I should do to make this more efficient?

Another question I have is that according to this answer, using .Contains(...) will cause a large performance drop when dealing with a large database. What is the recommended method to use to search through the entries in a large database?

BTW: `Contains` is not slow because the database table is large. `Contains` asks: "*Give me the entities that have PropertyXValue = 1 OR PropertyXValue = 5 OR PropertyXValue = 7, etc., etc.*" `Contains` is slow if the collection of those "OR values" you are asking for is large. Then it is also slow for a small table, even for an empty table. — Slauma, Jul 21 '12 at 12:38

Slauma · Accepted Answer · 2012-07-21T12:56:03.157

It's called a projection and just translates into a SELECT column1, column2, ... in SQL:

var result = db.Articles
    .Select(a => new
    {
        Date = a.Date,
        Title = a.Title
    })
    .ToList();

Instead of a => new { ... } (creates a list of "anonymous" objects) you can also use a named helper class (or "view model"): a => new MyViewModel { ... } that contains only the selected properties (but you can't use a => new Article { ... } as an entity itself).

For such a projection you don't need AsNoTracking() because projected data are not tracked anyway, only full entity objects are tracked.

Instead of using Contains the more common way is to use Where like:

var date = DateTime.Now.AddYears(-1);
var result = db.Articles
    .Where(a => date <= a.Date)
    .Select(a => new
    {
        Date = a.Date,
        Title = a.Title
    })
    .ToList();

This would select only the articles that are not older than a year. The Where is just translated into a SQL WHERE statement and the filter is performed in the database (which is as fast as the SQL query is, depending on table size and proper indexing, etc.). Only the result of this filter is loaded into memory.

Edit

Refering to your comment below:

Don't confuse IEnumerable<T>.Contains(T t) with string.Contains(string subString). The answer you have linked in your question talks about the first version of Contains. If you want to search for articles that have the string "keyword" in the text body you need the second Contains version:

string keyword = "Entity Framework";
var result = db.Articles
    .Where(a => a.Body.Contains(keyword))
    .Select(a => new
    {
        Date = a.Date,
        Title = a.Title
    })
    .ToList();

This will translate into something like WHERE Body like N'%Entity Framework%' in SQL. The answer about the poor performance of Contains doesn't apply to this version of Contains at all.

I use `Where` for most things, however when it comes to finding an article that has 'keyword' in the body, is `.Contains` the best way to search? Also in your example you have `.Where(a => date <= a.Date)` shouldn't it be `.Where(a => a.Date >= date)` or does it matter? — Garrett Fogerlie, Jul 21 '12 at 12:48
@GarrettFogerlie: About `Contains` see my Edit above. But if you have a lot of those queries for keywords, a "full text search" capability might be a better solution. For the `date <= a.Date` or `a.Date >= date`, it doesn't matter, it will yield the same result. — Slauma, Jul 21 '12 at 12:57

Entity Framework efficient querying

1 Answers1

Linked