c# – EF 4.3多重枚举问题

我们将EF 4.3用于数据层,并具有通用的存储库模式.后端是SQL 2008 R2,项目是.NET 4.0 / MVC 3,但我不认为这会影响问题.

基本上,我们在两个对象的数据库中有一对多的关系.一个是“陷阱”,第二个是“陷阱活动”.这意味着,一旦部署了这些“陷阱”之一,发生在该陷阱中的任何内容都会保留在“陷阱活动”表中.这应该是一种相当直接的方式.

该关系在“陷阱活动”表中使用FK定义到“陷阱”表的PK.两个表都定义了PK.

在我们的服务层中,我需要查询一个“陷阱”列表,其中列出了这些陷阱的部署日期.这是通过以下代码片段完成的：

var traps = this.trapRepository.Find(x => x.DeploymentYear == 2012).Select(x => new TrapHomeViewModel
            {
                County = x.County.Name,DeploymentDate = x.TrapActivities.First(y => y.ActivityType == 1).ActivityDate,State = x.County.CountyState.Abbreviation,Latitude = x.Latitude,Longitude = x.Longitude,TrapId = x.TrapID,TrapNumber = x.SerialNumber,Centroid = x.TrapCentroid
            }).ToList();

问题出在DeploymentDate属性周围.如上所述,这需要25秒才能返回大约3000个项目的列表.更新陷阱表以使部署日期存储在那里并填充此行：

DeploymentDate = x.DeploymentDate.Value.Date

结果响应时间不到1秒.现在我想我知道这里发生了什么(数据集的多个枚举),但我认为会发生类似于以下的查询：

SELECT     Counties.Name,TrapActivities.ActivityDate,States.Abbreviation,Traps.Latitude,Traps.Longitude,Traps.TrapID,Traps.SerialNumber,Traps.TrapCentroid
    FROM         TrapActivities INNER JOIN
                          Traps ON TrapActivities.TrapID = Traps.TrapID INNER JOIN
                          Counties ON Traps.CountyID = Counties.CountyID INNER JOIN
                          States ON Counties.State = States.FIPS_Code
    WHERE     (TrapActivities.ActivityType = 1)

……但似乎并非如此.有了上面的所有背景信息,我在哪里填写这个视图模型？我不认为我之前遇到过这个问题,但这也是一个比我们其他项目更大的数据集.任何有关这方面的指导都会有所帮助.如果我需要提供任何其他信息,请告诉我.

编辑

根据要求,GenericRepository Find方法和构造函数：

public class GenericRepository<T> : IGenericRepository<T>
        where T : class
    {
        private readonly IObjectSet<T> objectSet;
        private ObjectContext context;

        public GenericRepository()
            : this(new APHISEntities())
        {
        }

        public GenericRepository(ObjectContext context)
        {
            this.context = context;
            this.objectSet = this.context.CreateObjectSet<T>();
        }    

        public IEnumerable<T> Find(Func<T,bool> predicate)
        {
            return this.objectSet.Where(predicate);
        }

编辑2

这是上面代码生成的SQL示例：

exec sp_executesql N'SELECT 
[Extent1].[TrapActivityID] AS [TrapActivityID],[Extent1].[TrapID] AS [TrapID],[Extent1].[ActivityType] AS [ActivityType],[Extent1].[Notes] AS [Notes],[Extent1].[AgentID] AS [AgentID],[Extent1].[ActivityDate] AS [ActivityDate],[Extent1].[CreatedOn] AS [CreatedOn],[Extent1].[EditedOn] AS [EditedOn],[Extent1].[Deleted] AS [Deleted],[Extent1].[VisualInspectionID] AS [VisualInspectionID]
FROM [dbo].[TrapActivities] AS [Extent1]
WHERE [Extent1].[TrapID] = @EntityKeyValue1',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='FEBC7ED4-E726-4F5E-B2BA-FFD53AB7DF34'

在我看来,它正在获取一个Trap ID列表,然后为每个ID运行一个查询,导致生成数千个SQL语句.它似乎也在为县信息运行个别查询.

var q = from t in traps 
        where t.DeploymentYear == 2012
        select new TrapFirstDeployment {
            Trap = t,DeploymentActivity = t.TrapActivities.Where(ta=>ta.FirstOrDefault(a=>a.ActivityType=1))
        };

return q.Where(tfd=>tfd.DeploymentActivity != null);

var q = from t in this.objectSet.Include('TrapActivities')
        select t;