实体框架4.1。最有效的方法通过主键来获得多个实体？ [英] Entity Framework 4.1. Most efficient way to get multiple entities by primary key?

问题描述

什么是最有效的方式通过主键来选择多个实体？

public IEnumerable<Models.Image> GetImagesById(IEnumerable<int> ids)
{

    //return ids.Select(id => Images.Find(id));       //is this cool?
    return Images.Where( im => ids.Contains(im.Id));  //is this better, worse or the same?
    //is there a (better) third way?

}

我知道我可以做一些性能测试来比较，但我想知道是否有其实比这两个更好的办法，并正在寻找这两个查询之间的区别是什么一些启示，如果有的话，一旦他们已被翻译。

I realise that I could do some performance tests to compare, but I am wondering if there is in fact a better way than both, and am looking for some enlightenment on what the difference between these two queries is, if any, once they have been 'translated'.

推荐答案

使用包含的实体框架其实是很慢的。这是真的，它转换成SQL的在条款和自身快速执行的SQL查询。但问题和性能瓶颈是在翻译从您的LINQ查询到SQL。这将创建的EX pression树被扩展成一个长或串联链，因为没有提供原生EX pression从而重新presents的在。当SQL创建此EX $ P $的pssion许多或 s的认可和折叠放回SQL 在条款。

Using Contains in Entity Framework is actually very slow. It's true that it translates into an IN clause in SQL and that the SQL query itself is executed fast. But the problem and the performance bottleneck is in the translation from your LINQ query into SQL. The expression tree which will be created is expanded into a long chain of OR concatenations because there is no native expression which represents an IN. When the SQL is created this expression of many ORs is recognized and collapsed back into the SQL IN clause.

这并不意味着使用包含比你的 IDS 集合发出每个单元一个查询差（你第一个选项）。这可能得更好 - 至少不会过大集合。但对于大集合这是非常糟糕的。我记得我曾经测试过前段时间包含查询与工作，但前后花了一分钟约12.000元，即使在更短的执行比第二次的查询SQL。

This does not mean that using Contains is worse than issuing one query per element in your ids collection (your first option). It's probably still better - at least for not too large collections. But for large collections it is really bad. I remember that I had tested some time ago a Contains query with about 12.000 elements which worked but took around a minute even though the query in SQL executed in less than a second.

有可能是值得到测试多个往返的组合的性能到数据库为每个往返在包含前pression元件数量较少的

It might be worth to test the performance of a combination of multiple roundtrips to the database with a smaller number of elements in a Contains expression for each roundtrip.

此方法，并使用也限制包含与实体框架显示在这里解释：

This approach and also the limitations of using Contains with Entity Framework is shown and explained here:

<一个href="http://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama/7936350#7936350">Why请问载有（）运算符降低实体框架的表现那么厉害？

这有可能是一个原始的SQL命令将执行最好在这种情况下，这将意味着你打电话 dbContext.Database.SqlQuery＆lt;图像＆GT;（的SqlString）或 dbContext.Images.SqlQuery（的SqlString），其中的SqlString 是@所示的SQL符文的答案。

It's possible that a raw SQL command will perform best in this situation which would mean that you call dbContext.Database.SqlQuery<Image>(sqlString) or dbContext.Images.SqlQuery(sqlString) where sqlString is the SQL shown in @Rune's answer.

修改

下面是一些测量：

我已经这样做了一桌子55万的记录和11列（编号从1开始无间隙），并随机挑选20000 IDS：

I have done this on a table with 550000 records and 11 columns (IDs start from 1 without gaps) and picked randomly 20000 ids:

using (var context = new MyDbContext())
{
    Random rand = new Random();
    var ids = new List<int>();
    for (int i = 0; i < 20000; i++)
        ids.Add(rand.Next(550000));

    Stopwatch watch = new Stopwatch();
    watch.Start();

    // here are the code snippets from below

    watch.Stop();
    var msec = watch.ElapsedMilliseconds;
}

测试1

var result = context.Set<MyEntity>()
    .Where(e => ids.Contains(e.ID))
    .ToList();

结果 - > 毫秒= 85.5秒

测试2

var result = context.Set<MyEntity>().AsNoTracking()
    .Where(e => ids.Contains(e.ID))
    .ToList();

结果 - > 毫秒= 84.5秒

AsNoTracking 的这个微小的影响是非常不寻常的。这表明瓶颈不是对象具体化（而不是SQL如下图所示）。

This tiny effect of AsNoTracking is very unusual. It indicates that the bottleneck is not object materialization (and not SQL as shown below).

有关两个测试中，它可以在SQL探查可以看出，SQL查询到达数据库很晚。 （我没有精确计算，但它是晚于70秒。）显然，这LINQ查询到SQL的翻译是非常昂贵的。

For both tests it can be seen in SQL Profiler that the SQL query arrives at the database very late. (I didn't measure exactly but it was later than 70 seconds.) Obviously the translation of this LINQ query into SQL is very expensive.

测试3

var values = new StringBuilder();
values.AppendFormat("{0}", ids[0]);
for (int i = 1; i < ids.Count; i++)
    values.AppendFormat(", {0}", ids[i]);

var sql = string.Format(
    "SELECT * FROM [MyDb].[dbo].[MyEntities] WHERE [ID] IN ({0})",
    values);

var result = context.Set<MyEntity>().SqlQuery(sql).ToList();

结果 - > 毫秒= 5.1秒

测试4

// same as Test 3 but this time including AsNoTracking
var result = context.Set<MyEntity>().SqlQuery(sql).AsNoTracking().ToList();

结果 - > 毫秒= 3.8秒

这一次禁止追踪的效果更noticable。

This time the effect of disabling tracking is more noticable.

测试5

// same as Test 3 but this time using Database.SqlQuery
var result = context.Database.SqlQuery<MyEntity>(sql).ToList();

结果 - > 毫秒= 3.7秒

我的理解是， context.Database.SqlQuery＆LT; myEntity所＆GT;（SQL）是一样的 context.Set＆LT; myEntity所＆GT;（）。 SqlQuery类（SQL）.AsNoTracking（），所以没有区别有望测试4和测试5。在

My understanding is that context.Database.SqlQuery<MyEntity>(sql) is the same as context.Set<MyEntity>().SqlQuery(sql).AsNoTracking(), so there is no difference expected between Test 4 and Test 5.

（的结果集的长度并不总是相同的，由于随机ID选择后可能的重复，但它总是19600和19640元件之间。）

(The length of the result sets was not always the same due to possible duplicates after the random id selection but it was always between 19600 and 19640 elements.)

编辑2

测试6

甚至20000往返数据库比使用快包含：

Even 20000 roundtrips to the database are faster than using Contains:

var result = new List<MyEntity>();
foreach (var id in ids)
    result.Add(context.Set<MyEntity>().SingleOrDefault(e => e.ID == id));

结果 - > 毫秒= 73.6秒

请注意，我已经使用的SingleOrDefault ，而不是中查找。使用相同的code与查找很慢（我取消了几分钟后测试），因为查找通话 DetectChanges 内部。禁用自动变化检测（ context.Configuration.AutoDetectChangesEnabled = FALSE ）导致大致相同的性能的SingleOrDefault 。使用 AsNoTracking 缩短时间一两秒钟。

Note that I have used SingleOrDefault instead of Find. Using the same code with Find is very slow (I cancelled the test after several minutes) because Find calls DetectChanges internally. Disabling auto change detection (context.Configuration.AutoDetectChangesEnabled = false) leads to roughly the same performance as SingleOrDefault. Using AsNoTracking reduces the time by one or two seconds.

试验是与数据库客户端（控制台应用程序）和数据库服务器在同一台机器上完成。最后的结果可能会显著更糟远程数据库由于许多往返。

Tests were done with database client (console app) and database server on the same machine. The last result might get significantly worse with a "remote" database due to the many roundtrips.

这篇关于实体框架4.1。最有效的方法通过主键来获得多个实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！