首页 新闻 搜索 专区 学院

EF为何生成了2个子查询?

0
悬赏园豆:5 [待解决问题]

是这样的:

我有一张AreaOrder表,这个表里面有3个外键字段,分别是AreaID,ProjectInfoID,State,这3个字段分别关联了3张表。我用EF5.0,并且是通过根据数据库生成ADO.NET实体数据模型来使用EF的。

下面这个方法是查询的代码:

public IEnumerable<BidOrderInfo> GetBidOrderInfos(string projectName, string orderYard, string applicant, string stateCode, int areaOrderID, int areaID, int pageIndex, int pageSize, out int totalRows)        

{            

IEnumerable<BidOrderInfo> orderInfoList;             //总记录数默认值为0             totalRows = 0;            

using (TransactionCenterContext context = new TransactionCenterContext()) {                

IQueryable<AreaOrder> tempOrderInfoList = context.AreaOrders;                

//如果项目名称不为空,则把项目名称作为筛选条件                

if (!string.IsNullOrEmpty(projectName))                

{                    

tempOrderInfoList = tempOrderInfoList.Where(o => o.ProjectInfo.ProjectName.Contains(projectName));                

}                

//如果预约场地不为空,则把预约场地作为筛选条件                

if (!string.IsNullOrEmpty(orderYard))                

{                    

tempOrderInfoList = tempOrderInfoList.Where(o => o.Area.aName.Contains(orderYard));                

}                

//如果申请人不为空,则把申请人作为筛选条件                

if (!string.IsNullOrEmpty(applicant))                

{                    

tempOrderInfoList = tempOrderInfoList.Where(o => o.Applicant.Contains(applicant));                

}                

//如果状态不为空,则把状态作为筛选条件                

if (!string.IsNullOrEmpty(stateCode))                

{                    

tempOrderInfoList = tempOrderInfoList.Where(o => o.State == stateCode);

}                

/*                 

* 如果areaOrderID和areaID参数值都不等于0,表示查询的是与被审核场地有关的预约信息                 

* 不等于0,则筛选出预约开始时间大于等于当前时间并且预约场地是当前场地的记录

* 同时要筛选掉当前正被审核的预约信息                 

*/                

if (areaOrderID != 0 && areaID != 0)                

{                    

tempOrderInfoList = tempOrderInfoList.Where(o => o.AreaOrderID != areaOrderID && o.AreaID == areaID && o.OrderStartTime.CompareTo(System.DateTime.Now) >= 0);                

}

//为总记录数赋值                

totalRows = tempOrderInfoList.Count();

//抽取分页数据                

tempOrderInfoList = tempOrderInfoList.OrderBy(o => o.AreaOrderID).Skip((pageIndex - 1) * pageSize).Take(pageSize);

orderInfoList = tempOrderInfoList.Select(o => new BidOrderInfo()                 {                    

AreaOrderID = o.AreaOrderID,                    

AreaID = o.AreaID,                    

ProjectName = o.ProjectInfo.ProjectName,                    

ProjectOwner = o.ProjectInfo.ProjectOwner,                    

OrderStartTime = o.OrderStartTime,                    

OrderEndTime = o.OrderEndTime,                    

OrderYard = o.Area.aName,                    

OpengBidRoomCount = o.Area.Tender_RoomNum,                     EvaluationBidRoomCount = o.Area.Evaluation_RoomNum,                     OpenBidTime = o.OpenBidTime,                    

ExpectedAttenderCount = o.ExpectedAttenderCount,                     ActualAttenderCount = o.ActualAttenderCount,                    

Applicant = o.Applicant,                    

ApplicantPhone = o.ApplicantPhone,                    

State = o.CodeDictionary.Name                 }).ToList();            

}

return orderInfoList;        

}

 

下面是EF生成的SQL语句:

exec sp_executesql N'SELECT TOP (10)
[Project1].[AreaOrderID] AS [AreaOrderID],
[Project1].[AreaID] AS [AreaID],
[Project1].[ProjectName] AS [ProjectName],
[Project1].[ProjectOwner] AS [ProjectOwner],
[Project1].[OrderStartTime] AS [OrderStartTime],
[Project1].[OrderEndTime] AS [OrderEndTime],
[Project1].[aName] AS [aName],
[Project1].[Tender_RoomNum] AS [Tender_RoomNum],
[Project1].[Evaluation_RoomNum] AS [Evaluation_RoomNum],
[Project1].[OpenBidTime] AS [OpenBidTime],
[Project1].[ExpectedAttenderCount] AS [ExpectedAttenderCount],
[Project1].[ActualAttenderCount] AS [ActualAttenderCount],
[Project1].[Applicant] AS [Applicant],
[Project1].[ApplicantPhone] AS [ApplicantPhone],
[Project1].[Name] AS [Name]
FROM ( SELECT [Project1].[AreaOrderID] AS [AreaOrderID], [Project1].[AreaID] AS [AreaID], [Project1].[OrderStartTime] AS [OrderStartTime], [Project1].[OrderEndTime] AS [OrderEndTime], [Project1].[OpenBidTime] AS [OpenBidTime], [Project1].[ExpectedAttenderCount] AS [ExpectedAttenderCount], [Project1].[ActualAttenderCount] AS [ActualAttenderCount], [Project1].[Applicant] AS [Applicant], [Project1].[ApplicantPhone] AS [ApplicantPhone], [Project1].[ProjectName] AS [ProjectName], [Project1].[ProjectOwner] AS [ProjectOwner], [Project1].[aName] AS [aName], [Project1].[Evaluation_RoomNum] AS [Evaluation_RoomNum], [Project1].[Tender_RoomNum] AS [Tender_RoomNum], [Project1].[Name] AS [Name], row_number() OVER (ORDER BY [Project1].[AreaOrderID] ASC) AS [row_number]
 FROM ( SELECT
  [Extent1].[AreaOrderID] AS [AreaOrderID],
  [Extent1].[AreaID] AS [AreaID],
  [Extent1].[OrderStartTime] AS [OrderStartTime],
  [Extent1].[OrderEndTime] AS [OrderEndTime],
  [Extent1].[OpenBidTime] AS [OpenBidTime],
  [Extent1].[ExpectedAttenderCount] AS [ExpectedAttenderCount],
  [Extent1].[ActualAttenderCount] AS [ActualAttenderCount],
  [Extent1].[Applicant] AS [Applicant],
  [Extent1].[ApplicantPhone] AS [ApplicantPhone],
  [Extent2].[ProjectName] AS [ProjectName],
  [Extent2].[ProjectOwner] AS [ProjectOwner],
  [Extent3].[aName] AS [aName],
  [Extent3].[Evaluation_RoomNum] AS [Evaluation_RoomNum],
  [Extent3].[Tender_RoomNum] AS [Tender_RoomNum],
  [Extent4].[Name] AS [Name]
  FROM    [dbo].[AreaOrder] AS [Extent1]
  INNER JOIN [dbo].[ProjectInfo] AS [Extent2] ON [Extent1].[ProjectInfoID] = [Extent2].[ProjectInfoID]
  INNER JOIN [dbo].[Area] AS [Extent3] ON [Extent1].[AreaID] = [Extent3].[AreaID]
  INNER JOIN [dbo].[CodeDictionary] AS [Extent4] ON [Extent1].[State] = [Extent4].[Code]
  WHERE [Extent1].[State] = @p__linq__0
 )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[AreaOrderID] ASC',N'@p__linq__0 varchar(8000)',@p__linq__0='ZTB001'

我听说EF会多生成一个select子查询,但我这里为什么生成了2个子查询,谁知道是哪里出了问题?

Jeffrey.Liang的主页 Jeffrey.Liang | 初学一级 | 园豆:43
提问于:2013-01-18 10:07
< >
分享
所有回答(1)
0

o.ProjectInfo.ProjectName

o.Area.aName

 

这两个字段是外键表的列。你把这两个WHERE注销后试下。

geass.. | 园豆:1705 (小虾三级) | 2013-01-18 11:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册