问题描述
我正在使用Entity Framework 5连接到SQL Server2008.我已将存储过程导入到我的项目中,没有任何问题.过去,存储过程一直没有任何问题,直到我从在LINQ中使用.GroupBy().First()
获得独特的结果切换为通过在存储过程中使用OUTER APPLY
获得独特的结果为止.
I'm using Entity Framework 5 to connect to SQL Server 2008. I've imported a stored procedure into my project with no issues. The stored procedure has executed in the past with no issues at all up until I switched from using .GroupBy().First()
in LINQ to get distinct results to getting unique results by using an OUTER APPLY
in the stored procedure.
我从LINQ中的.GroupBy()
切换到在SQL Server上使用OUTER APPLY
,因为SQL当然快得多.
I switched to using an OUTER APPLY
on SQL Server from the .GroupBy()
in LINQ because, of course SQL is much faster.
这是我的SQL:
@USERID VARCHAR(MAX) = NULL,
@ITEMTYPE VARCHAR(MAX) = NULL,
@ITEMSUBTYPE VARCHAR(MAX) = NULL,
@ITEMGROUP VARCHAR(MAX) = NULL,
@ITEMNO VARCHAR(MAX) = NULL
SELECT distinct orderformdump.itemno,
case when @userid = '' then NULL else CAST((SELECT top 1 [UNITPRICE] FROM [ICPRICP] WHERE [ITEMNO] = ICITEM.ITEMNO AND [PRICELIST] in (select priclist from ARCUS where IDCUST in (select CUSTID from WEBLOGINACCESS where [USER] = @USERID)) and [CURRENCY] = 'CDN' and DPRICETYPE = 1) AS DECIMAL(18,2)) end as price,
isnull(deals.isindeal, CAST(0 AS BIT) ) isindeal
FROM ORDERFORMDUMP
INNER JOIN ICITEM ON ICITEM.FMTITEMNO = orderformdump.itemno
outer apply
(
select top 1 CAST(1 AS BIT) as isindeal
from PRD2 INNER JOIN PRH on PRD2.CODE = PRH.CODE
where ICITEM.ITEMNO = PRD2.ITEMNO and PRH.ACTIVE = 1
and cast(GETDATE() as DATE) between PRH.STARTDATE and isnull(PRH.ENDDATE, cast(GETDATE() as DATE))
) deals
where
(@ITEMNO IS NULL or ICITEM.FMTITEMNO = @ITEMNO)
and
(@ITEMSUBTYPE is null or ORDERFORMITEMSUBTYPEDUMP.ITEMSUBTYPE = @ITEMSUBTYPE)
and
(@ITEMTYPE is null or ORDERFORMITEMTYPEDUMP.ITEMTYPE = @ITEMTYPE)
and
(@ITEMGROUP is null or ORDERFORMITEMGROUPDUMP.ITEMGROUP = @ITEMGROUP)
之前,我有两个LEFT JOIN
而不是OUTER APPLY
,并且isindeal
列位于CASE
语句中:
Before, instead of the OUTER APPLY
I had two LEFT JOIN
s and the isindeal
column was inside a CASE
statement:
...
case when PRH.ACTIVE = 1 and PRH.STARTDATE < cast(GETDATE() as DATE) and (PRH.ENDDATE IS NULL OR PRH.ENDDATE >= cast(GETDATE() as DATE)) then CAST(1 AS BIT) ELSE CAST(0 AS BIT) END as isindeal
...
LEFT JOIN PRD2 on ICITEM.ITEMNO = PRD2.ITEMNO
LEFT JOIN PRH on PRD2.CODE = PRH.CODE
...
这是Entity Framework中导入的类:
Here's the imported class in Entity Framework:
public partial class PRODUCTS_Result
{
public string itemno { get; set; }
public Nullable<decimal> price { get; set; }
public bool isindeal { get; set; }
}
这是存储过程的调用方式:
This is how the stored procedure is called:
db.PRODUCTS(userid, itemtype, itemsubtype, itemgroup, itemno)
这是以前被称为的方式:
This is how it was being called before:
db.PRODUCTS(userid, itemtype, itemsubtype, itemgroup, itemno).GroupBy(i => i.itemno).Select(x => x.First());
奇怪的是,仅在使用所有空参数(例如db.PRODUCTS(null, null, null, null, null)
)调用存储过程时才会发生超时
Strangely enough, the timeout only happens when the stored procedure gets called with all null paramaters (ex. db.PRODUCTS(null, null, null, null, null)
)
我尝试将超时设置为5分钟,而不是默认的30秒((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 300;
,但是它只运行了整整5分钟,仍然超时.
I tried setting the timeout to 5 minutes instead of the default 30 seconds, ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 300;
, but it just runs for the entire 5 minutes and still times out.
存储过程在3-5秒内用参数的所有NULL值在SQL Server中完成执行,我检查了所有数据类型并且它们匹配,所以我不知道为什么只收到此超时错误有时.
The stored procedure finishes execution in SQL Server in 3-5 seconds with all NULL values for the paramters, I've checked all the data types and they match up, so I've no idea why I get this timeout error only sometimes.
推荐答案
简短答案:向此查询添加OPTION(RECOMPILE)
Short answer: add OPTION (RECOMPILE) to this query
长答案:请阅读Erland Sommarskog的 TSQL中的动态搜索条件
Long answer: Read Erland Sommarskog's Dynamic Search Conditions in TSQL
这篇关于EntityCommandExecutionException超时仅在某些时候过期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!