检查值是否为空,如果是,则执行额外(子)查询
本文关键字:查询 执行 是否 检查 如果 | 更新日期: 2023-09-27 18:33:21
是否可以
在 LINQ 查询中检查空值,当该值为 null 时,它是否同时执行额外的(子)查询?
解释
我在数据库中声明了默认按钮,并带有默认描述。用户可以自定义这些按钮,这些设置存储在ButtonLocations
表中。现在,每个按钮都有一个标准描述,用户可以编辑此描述。当用户编辑说明时,它将存储在数据库的Descriptions
表中。当我检索所有按钮时,我首先检查按钮是否具有特定的描述(在按钮位置中,带有左连接)。如果这不是真的(所以空),我检索默认描述。
目前,我获取所有实体及其描述,然后循环遍历所有实体以检查值是否为空。这会导致对数据库进行多次查询。
var temp = (from bl in context.buttonLocations
join b in context.Buttons
on bl.ButtonID equals b.ButtonID into buttons
from button in buttons.DefaultIfEmpty()
join d in context.Descriptions
on new
{
ID = bl.ButtonLocationID,
langID = languageID,
originID = descriptionOriginID
}
equals new
{
ID = d.ValueID,
langID = d.LanguageID,
originID = d.DescriptionOriginID
}
into other
where bl.ButtonGroupID == buttonGroupId
from x in other.DefaultIfEmpty()
select new
{
Button = button,
ButtonLocation = bl,
Description = x
}).ToList();
// Retrieve default descriptions if no specific one is set
foreach (var item in temp)
{
if (item.Description == null)
{
item.Description = context.Descriptions
.FirstOrDefault(x => x.ValueID == item.Button.ButtonID && x.LanguageID == languageID && x.DescriptionOriginID == (short)DescriptionOriginEnum.Button);
}
}
我认为 Colin 使用合并运算符的答案应该有效,但如果不能做到这一点,您可以尝试做一个子选择,它同时获得两个选项,然后按自定义源的偏好排序,并获取最高记录。(我在这里假设任何给定的按钮实际上只有一个描述,并且多个描述不应该导致多个按钮。
var temp = (from bl in context.buttonLocations
join b in context.Buttons
on bl.ButtonID equals b.ButtonID into buttons
from button in buttons.DefaultIfEmpty()
let description = (
from d in context.Descriptions
where
d.LanguageID == languageID
&& (
(
d.ValueID == bl.ButtonLocationID
&& d.DescriptionOriginID == descriptionOriginID
)
||
(
d.ValueID == b.ButtonID
d.DescriptionOriginID == (short)DescriptionOriginEnum.Button
)
)
// this line puts custom descriptions first
orderby d.DescriptionOriginID == (short)DescriptionOriginEnum.Button
? 1
: 0
select d
)
// this will get a custom description if there was one, otherwise
// the first one will be the default description
.FirstOrDefault()
where bl.ButtonGroupID == buttonGroupId
select new
{
Button = button,
ButtonLocation = bl,
Description = description
})
.ToList();
这显然有点尴尬,可能不是最有效的查询。我会先尝试将合并运算符移动到let description = d ?? /*subselect*/
行。
空合并运算符应该在这里适合您。像这样:
.....
select new
{
Button = button,
ButtonLocation = bl,
Description ?? context.Descriptions
.FirstOrDefault(
x => x.ValueID == button.ButtonID
&& x.LanguageID == languageID
&& x.DescriptionOriginID == (short)DescriptionOriginEnum.Button)
})