Geek asks and answers

Effective access to the Entity Framework

Suppose I have a model, Articlewhich has a large number of columns, and the database contains more than 100,000 rows. If I do something like var articles = db.Articles.ToList(), does it extract the entire article model for each article in the database and keep it in memory correctly?

So, if I fill in a table that only shows the date of the record, and the header is a way to only retrieve only these columns from the database using the entity structure, and will it be more efficient?

According to this ,

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

It looks like I should use NoTracking, since the data does not change or delete, it is displayed only. Therefore, my request now becomes var articles = db.Articles.AsNoTracking().ToList(). Are there other things I have to do to make this more efficient?

Another question, which is that according to this answer, usage .Contains(...)will lead to a big decrease in performance when working with a large database. What is the recommended method for finding records in a large database?

+4
source share
1 answer

It is called projection and simply translates to SELECT column1, column2, ...in SQL:

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

Instead 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 cannot use it a => new Article { ... }as an entity itself).

AsNoTracking(), , .

Contains Where, :

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

Edit

:

IEnumerable<T>.Contains(T t) string.Contains(string subString). , , Contains. "keyword" , Contains:

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

WHERE Body like N'%Entity Framework%' SQL. Contains Contains.

+4

More articles:


All Articles