如何在 linq 中的 where 条件下编写快速子查询
本文关键字:查询 条件下 linq 中的 where | 更新日期: 2023-09-27 18:28:58
我尝试在linq where condition中编写子查询。但是当我在sql服务器探查器上查看我的sql语法时,我的查询看起来既复杂又长,并且在服务器中出现超时错误。
我想写一个如下所示的查询。
SELECT
T1.Id,
T2.Id,
DATEADD(HOUR,
ISNULL(
(
SELECT
MAX(T3.MaxHour) AS [MaxHour]
FROM TABLE3 T3 WITH(NOLOCK)
WHERE
T1.CategoryId=T3.CategoryId
),
0),T1.EndDate
),
T1.StartDate
FROM
TABLE1 T1 WITH(NOLOCK)
LEFT OUTER JOIN TABLE2 T2 WITH(NOLOCK) ON T1.Id=T2.Id
WHERE
DATEADD(HOUR,
ISNULL(
(
SELECT
MAX(T3.MaxHour) AS [MaxHour]
FROM TABLE3 T3 WITH(NOLOCK)
WHERE
T1.CategoryId=T3.CategoryId
),
0),T1.EndDate
)
> '2014-11-05 00:00:00'
AND
T1.StartDate < '2014-11-05 06:00:00'
ı 像这样写我的 linq 表达式。
var actualData = from T1 in _context.TABLE1
join T2 in _context.TABLE2 on T1.Id equals T2.Id into data
from x in data.DefaultIfEmpty()
where
EntityFunctions.AddHours(T1.EndDate,
(
(int?)((from T3 in _context.TABLE3
where
T3.CategoryId == T1.CategoryId
select
T3).Max(row => row.MaxHour))
) ?? 0
) >= '2014-11-05 00:00:00'
&& T1.StartDate < '2014-11-05 06:00:00'
select
new
{
Id1 = T1.Id,
Id2 = T2.Id,
StartDate = T1.StartDate,
EndDate = EntityFunctions.AddHours(T1.EndDate,
(
(int?)((from T3 in _context.TABLE3
where
T3.CategoryId == T1.CategoryId
select
T3).Max(row => row.MaxHour))
) ?? 0
)
};
但它似乎不是我想看到的 sql。
如何在 linq 中写入?
谢谢。
尝试:
var actualData = from T1 in _context.TABLE1
join T2 in _context.TABLE2 on T1.Id equals T2.Id into data
from x in data.DefaultIfEmpty()
let endate = EntityFunctions.AddHours(T1.EndDate,
(
(
from T3 in _context.TABLE3
where T3.CategoryId == T1.CategoryId
select T3).Max(row => row.MaxHour)
)
) ?? 0
)
where
enddate >= '2014-11-05 00:00:00'
&& T1.StartDate < '2014-11-05 06:00:00'
select
new
{
Id1 = T1.Id,
Id2 = T2.Id,
StartDate = T1.StartDate,
EndDate = enddate
};
而且我怀疑您可以将演员表放到(int?)
,因为默认情况下max(int)
返回int?