实体框架，通用存储库模式和奇怪的SQL生成 [英] Entity Framework, Generic Repository Pattern and strange SQL generation

问题描述

 <$ c我已经为Entity Framework 4实现了一个通用存储库。这是一个虚拟版本，其中AllAppContainer是EF4对象上下文。 $ c> public class Repository< T>其中T：class 
 {
 protected AllAppContainer objectContext; 
受保护的ObjectSet< T> EntitySet的; 
 
 public Repository（）
 {
 objectContext = new AllAppContainer（）; 
 entitySet = objectContext.CreateObjectSet< T>（）; 
 
 $ b public int QueryCount（Func< T，bool> predicate）
 {
 int queryCount = entitySet.Count（predicate）; 
返回queryCount; 
 
 
 code $ 
 
 
 
一种方法是QueryCount（），我想要作为选择Count（*）...其中一行SQL（不返回实际记录）。 
 
 
直接转到？你会认为......首先，让我们做一个同样的事情的非Repository版本，对Item实体进行计数： 
 
 
  AllAppContainer allAppContainer = new AllAppContainer（）; 
 int nonRepCount = allAppContainer.Items.Count（item => item.Id> 0）; 
  
 SQL Server Profiler表示生成的SQL为：
  SELECT 
 [GroupBy1]。[A1] AS [C1] 
 FROM（SELECT 
 COUNT（1）AS [A1] 
从[DBO] [项目] AS [Extent1] 
 WHERE [Extent1] [ID]> 0 
）AS [GroupBy1] 
  
 Woo-hoo！ Score！
 
 
 
现在让我们使用我的Repository QueryCount调用相同的函数： 
 
 
 库<项目> repository = new Repository< Item>（）; 
 int repCount = repository.QueryCount（item => item.Id> 0）; 
  
下面是生成的SQL：
  SELECT 
 [Extent1]。[ID] AS [ID]，
 [Extent1] [SmallField] AS [SmallField] 
从[DBO]。[ Items] AS [Extent1] 
  
是的，EF正在返回全套数据，然后调用 Count（）在内存中。
 
 
 
为了好玩，我试着改变了Repository QueryCount中的相关行： 
 
 
  int queryCount = new AllAppContainer（）。CreateObjectSet< T>（）。Count（predicate）; 
  
以及非存储库行：
  int nonRepCount = allAppContainer1.CreateObjectSet< Item>（）。Count（item => item.Id> 0）; 
  
但每个生成的SQL都与以前一样。
 
 
 
现在为什么所有这个存储库返回-all-matching-records-then-counts会发生，当它不适用于非存储库？有没有办法做我想通过我的通用资源库，即数据库。我无法将内存中的计数性能降低。
解决方案
您需要使用表达式< Func< TSource，bool>>  Count 的谓词，否则框架使用 Enumerable.Count< TSource>方法（IEnumerable的< TSource>中Func键< TSource，布尔>）它得到从DB整个集合到能够调用为每个项目，所以你的方法应该是：
  public int QueryCount（Expression< Func< T，Boolean>>谓词）
 {
 int queryCount = entitySet.Count（predicate ）; 
返回queryCount; 
} 
  
 
I've implemented a generic repository for Entity Framework 4.  Here's a dumbed down version, where AllAppContainer is the EF4 object context:
public class Repository<T> where T : class
{
    protected AllAppContainer objectContext;
    protected ObjectSet<T> entitySet;

    public Repository()
    {
        objectContext  = new AllAppContainer();
        entitySet = objectContext.CreateObjectSet<T>();
    }

    public int QueryCount(Func<T, bool> predicate)
    {
        int queryCount = entitySet.Count(predicate);
        return queryCount;
    }
}
The one method is QueryCount(), which I want to act as a select Count(*) ... where line of SQL (not returning the actual records).

Straight-forward?  You'd think...  First, let's do a non-Repository version of the same thing, performing a count on Item entities:
AllAppContainer allAppContainer = new AllAppContainer();
int nonRepCount = allAppContainer.Items.Count(item => item.Id > 0);
SQL Server Profiler says the generated SQL is:
SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Items] AS [Extent1]
    WHERE [Extent1].[Id] > 0
)  AS [GroupBy1]
Woo-hoo! Score!

Now let's call the same using my Repository QueryCount:
Repository<Item> repository = new Repository<Item>();
int repCount = repository.QueryCount(item => item.Id > 0);
Here's the generated SQL:
SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[SmallField] AS [SmallField]
FROM [dbo].[Items] AS [Extent1]
Yep, EF is returning the full set of data, then calling Count() on it in-memory.

For fun I tried changing the relevant line in Repository QueryCount to:
int queryCount = new AllAppContainer().CreateObjectSet<T>().Count(predicate);
and the non-repository line to:
int nonRepCount = allAppContainer1.CreateObjectSet<Item>().Count(item => item.Id > 0);
but the generated SQL for each is the same as before.

Now why would all this repository-returns-all-matching-records-then-counts be happening, when it doesn't for non-repository?  And is there any way to do what I want via my generic repository i.e. count at db.  I can't take the in-memory count performance hit.
解决方案
you need to use Expression<Func<TSource, bool>> predicate for your Count otherwise the framework uses the Enumerable.Count<TSource> Method (IEnumerable<TSource>, Func<TSource, Boolean>) which gets the whole collection from DB to be able to call for each item, so your method should be:
public int QueryCount(Expression<Func<T, Boolean>> predicate)
{
    int queryCount = entitySet.Count(predicate);
    return queryCount;
}


                        
这篇关于实体框架，通用存储库模式和奇怪的SQL生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！