实体框架高效查询 [英] Entity Framework efficient querying

问题描述

让我们说我有一个模型，Article具有大量的列，并且数据库包含超过100,000行.如果我执行类似var articles = db.Articles.ToList()的操作，它正在为数据库中的每个文章检索整个文章模型并将其保存在内存中，对吗?

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?

根据此，

跟踪对象中返回的对象需要一定的成本 语境.检测对象的变化并确保多个 对相同逻辑实体的请求返回相同的对象实例 要求将对象附加到ObjectContext实例.如果你 不打算对对象进行更新或删除，并且不需要 身份管理，请考虑在以下情况下使用NoTracking合并选项: 您执行查询.

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.

似乎我应该使用NoTracking，因为数据不会被更改或删除，只能显示.因此，我的查询现在变为var articles = db.Articles.AsNoTracking().ToList().我还应该做些其他事情来提高效率吗?

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?

我还有一个问题是，根据此答案，使用.Contains(...)会导致交易时性能大幅下降与大型数据库.在大型数据库中搜索条目的推荐方法是什么?

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?

推荐答案

它称为投影，在SQL中仅转换为SELECT column1, column2, ...:

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();

代替a => new { ... }(创建匿名"对象的列表)，您还可以使用命名的帮助器类(或视图模型"):a => new MyViewModel { ... }仅包含选定的属性(但不能使用) a => new Article { ... }作为实体本身).

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).

对于这种投影，您不需要AsNoTracking()，因为无论如何都不跟踪投影的数据，仅跟踪完整的实体对象.

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

不是使用Contains，而是更常见的方法是使用Where，例如:

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();

这只会选择不超过一年的文章. Where只是转换为SQL WHERE语句，然后在数据库中执行过滤器(这与SQL查询的速度一样快，具体取决于表的大小和适当的索引等).仅此过滤器的结果会加载到内存中.

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.

修改

请参阅下面的评论:

请勿将IEnumerable<T>.Contains(T t)与string.Contains(string subString)混淆.您在问题中链接的答案是有关Contains的第一个版本的.如果要搜索文本正文中包含字符串"keyword"的文章，则需要第二个Contains版本:

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();

这将转换为SQL中的WHERE Body like N'%Entity Framework%'之类的内容.关于Contains性能不佳的答案根本不适用于此版本的Contains.

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.

这篇关于实体框架高效查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！