c# - 一个可重用表达式中的多个复杂选择 c# LINQ
问题描述
有没有办法制作一个执行复杂选择的单个表达式。我需要从我的联系人表中选择一行，这是加入联系人的候选表中的第一项。
dbContext.Contacts.Where( x=> x.ContactId == 9566)
.Select( x=> new { Candidate = x.Candidates.FirstOrDefault(), Contact = x})
.Select( x=> new
{
ContactId = x.Contact.ContactId,
UserTitleId = x.Contact.UserTitleId,
Forename = x.Contact.ForeName,
MiddleName = x.Contact.MiddleName,
Surname = x.Contact.Surname,
CandidateId = x.Candidate.CandidateId,
TeacherRefNum = x.Candidate.TeacherRefNum,
DateOfBirth = x.Candidate.DateOfBirth
})
因为可能有多个候选人，但我只想要第一个我需要做的 FirstOrDefault。如果这适用于每个参数，生成的 SQL 似乎效率很低。所以下面的表达式不好
public static Expression<Func<Contact, CandidateM>> ToM()
{
return x => new CandidateM
{
ContactId = x.ContactId,
UserTitleId = x.UserTitleId,
Forename = x.ForeName,
MiddleName = x.MiddleName,
Surname = x.Surname
CandidateId = x.Candidate.FirstOrDefault().CandidateId,
TeacherRefNum = x.Candidate.FirstOrDefault().TeacherRefNum,
DateOfBirth = x.Candidate.FirstOrDefault().DateOfBirth
};
}
我想生成一个可以在联系人表上使用的表达式，因为选择可以有效地执行此操作。
DbContext.Contact.Where( x=> x.ContactId == id ).Select(CandidateM.ToM()).FirstOrDefault();
我在上面使用多个 FirstOrDefault 方法执行的表达式生成的 SQL 如下所示。鉴于我想从中获得更多的结构，例如从候选人中选择所有着装领域，我认为这可能不是最好的做事方式。也许 LINQ to Entities 产生的这个庞大的 SQL 查询并不是什么大问题？
exec sp_executesql N'SELECT
[Limit6].[ContactId] AS [ContactId],
[Limit6].[C8] AS [C1],
[Limit6].[UserTitleId] AS [UserTitleId],
[Limit6].[ForeName] AS [ForeName],
[Limit6].[MiddleName] AS [MiddleName],
[Limit6].[Surname] AS [Surname],
[Limit6].[PreviousSurname] AS [PreviousSurname],
[Limit6].[KnownAs] AS [KnownAs],
[Limit6].[C9] AS [C2],
[Limit6].[C10] AS [C3],
[Limit6].[C11] AS [C4],
[Limit6].[C12] AS [C5],
[Limit6].[Email] AS [Email],
[Limit6].[WorkPhone] AS [WorkPhone],
[Limit6].[PersonalPhone] AS [PersonalPhone],
[Limit6].[Mobile] AS [Mobile],
[Limit6].[C1] AS [C6],
[Limit6].[C2] AS [C7],
[Limit6].[C3] AS [C8],
[Limit6].[C4] AS [C9],
[Limit6].[C5] AS [C10],
[Limit6].[C6] AS [C11],
[Limit6].[C7] AS [C12]
FROM ( SELECT TOP (1)
[Project14].[ContactId] AS [ContactId],
[Project14].[UserTitleId] AS [UserTitleId],
[Project14].[ForeName] AS [ForeName],
[Project14].[MiddleName] AS [MiddleName],
[Project14].[Surname] AS [Surname],
[Project14].[KnownAs] AS [KnownAs],
[Project14].[PreviousSurname] AS [PreviousSurname],
[Extent7].[Email] AS [Email],
[Extent7].[Mobile] AS [Mobile],
[Extent7].[WorkPhone] AS [WorkPhone],
[Extent7].[PersonalPhone] AS [PersonalPhone],
N'''' AS [C1],
N'''' AS [C2],
N'''' AS [C3],
N'''' AS [C4],
N'''' AS [C5],
N'''' AS [C6],
1 AS [C7],
[Project14].[C1] AS [C8],
[Project14].[C2] AS [C9],
[Project14].[C3] AS [C10],
[Project14].[C4] AS [C11],
[Project14].[C5] AS [C12]
FROM (SELECT
[Project12].[ContactId] AS [ContactId],
[Project12].[UserTitleId] AS [UserTitleId],
[Project12].[ForeName] AS [ForeName],
[Project12].[MiddleName] AS [MiddleName],
[Project12].[Surname] AS [Surname],
[Project12].[KnownAs] AS [KnownAs],
[Project12].[PreviousSurname] AS [PreviousSurname],
[Project12].[C1] AS [C1],
[Project12].[C2] AS [C2],
[Project12].[C3] AS [C3],
[Project12].[C4] AS [C4],
(SELECT TOP (1)
[Extent6].[JobTitle] AS [JobTitle]
FROM [dbo].[Candidate] AS [Extent6]
WHERE [Project12].[ContactId] = [Extent6].[ContactId]) AS [C5]
FROM ( SELECT
[Project11].[ContactId] AS [ContactId],
[Project11].[UserTitleId] AS [UserTitleId],
[Project11].[ForeName] AS [ForeName],
[Project11].[MiddleName] AS [MiddleName],
[Project11].[Surname] AS [Surname],
[Project11].[KnownAs] AS [KnownAs],
[Project11].[PreviousSurname] AS [PreviousSurname],
[Project11].[C1] AS [C1],
[Project11].[C2] AS [C2],
[Project11].[C3] AS [C3],
[Project11].[C4] AS [C4]
FROM ( SELECT
[Project9].[ContactId] AS [ContactId],
[Project9].[UserTitleId] AS [UserTitleId],
[Project9].[ForeName] AS [ForeName],
[Project9].[MiddleName] AS [MiddleName],
[Project9].[Surname] AS [Surname],
[Project9].[KnownAs] AS [KnownAs],
[Project9].[PreviousSurname] AS [PreviousSurname],
[Project9].[C1] AS [C1],
[Project9].[C2] AS [C2],
[Project9].[C3] AS [C3],
(SELECT TOP (1)
[Extent5].[NINumber] AS [NINumber]
FROM [dbo].[Candidate] AS [Extent5]
WHERE [Project9].[ContactId] = [Extent5].[ContactId]) AS [C4]
FROM ( SELECT
[Project8].[ContactId] AS [ContactId],
[Project8].[UserTitleId] AS [UserTitleId],
[Project8].[ForeName] AS [ForeName],
[Project8].[MiddleName] AS [MiddleName],
[Project8].[Surname] AS [Surname],
[Project8].[KnownAs] AS [KnownAs],
[Project8].[PreviousSurname] AS [PreviousSurname],
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
[Project8].[C3] AS [C3]
FROM ( SELECT
[Project6].[ContactId] AS [ContactId],
[Project6].[UserTitleId] AS [UserTitleId],
[Project6].[ForeName] AS [ForeName],
[Project6].[MiddleName] AS [MiddleName],
[Project6].[Surname] AS [Surname],
[Project6].[KnownAs] AS [KnownAs],
[Project6].[PreviousSurname] AS [PreviousSurname],
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
(SELECT TOP (1)
[Extent4].[DateOfBirth] AS [DateOfBirth]
FROM [dbo].[Candidate] AS [Extent4]
WHERE [Project6].[ContactId] = [Extent4].[ContactId]) AS [C3]
FROM ( SELECT
[Project5].[ContactId] AS [ContactId],
[Project5].[UserTitleId] AS [UserTitleId],
[Project5].[ForeName] AS [ForeName],
[Project5].[MiddleName] AS [MiddleName],
[Project5].[Surname] AS [Surname],
[Project5].[KnownAs] AS [KnownAs],
[Project5].[PreviousSurname] AS [PreviousSurname],
[Project5].[C1] AS [C1],
[Project5].[C2] AS [C2]
FROM ( SELECT
[Project3].[ContactId] AS [ContactId],
[Project3].[UserTitleId] AS [UserTitleId],
[Project3].[ForeName] AS [ForeName],
[Project3].[MiddleName] AS [MiddleName],
[Project3].[Surname] AS [Surname],
[Project3].[KnownAs] AS [KnownAs],
[Project3].[PreviousSurname] AS [PreviousSurname],
[Project3].[C1] AS [C1],
(SELECT TOP (1)
[Extent3].[TeacherRefNum] AS [TeacherRefNum]
FROM [dbo].[Candidate] AS [Extent3]
WHERE [Project3].[ContactId] = [Extent3].[ContactId]) AS [C2]
FROM ( SELECT
[Project2].[ContactId] AS [ContactId],
[Project2].[UserTitleId] AS [UserTitleId],
[Project2].[ForeName] AS [ForeName],
[Project2].[MiddleName] AS [MiddleName],
[Project2].[Surname] AS [Surname],
[Project2].[KnownAs] AS [KnownAs],
[Project2].[PreviousSurname] AS [PreviousSurname],
[Project2].[C1] AS [C1]
FROM ( SELECT
[Extent1].[ContactId] AS [ContactId],
[Extent1].[UserTitleId] AS [UserTitleId],
[Extent1].[ForeName] AS [ForeName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[Surname] AS [Surname],
[Extent1].[KnownAs] AS [KnownAs],
[Extent1].[PreviousSurname] AS [PreviousSurname],
(SELECT TOP (1)
[Extent2].[CandidateId] AS [CandidateId]
FROM [dbo].[Candidate] AS [Extent2]
WHERE [Extent1].[ContactId] = [Extent2].[ContactId]) AS [C1]
FROM [dbo].[Contact] AS [Extent1]
WHERE [Extent1].[ContactId] = @p__linq__0
) AS [Project2]
) AS [Project3]
) AS [Project5]
) AS [Project6]
) AS [Project8]
) AS [Project9]
) AS [Project11]
) AS [Project12] ) AS [Project14]
LEFT OUTER JOIN [dbo].[ContactExtended] AS [Extent7] ON [Project14].[ContactId] = [Extent7].[ContactId]
) AS [Limit6]',N'@p__linq__0 int',@p__linq__0=9566
解决方案
推荐阅读
- javascript - 如何确保正确更新和引用全局 javascript 对象？
- c - 递归任务创建导致 OpenMP 中的分段错误
- excel - Excel 是否自动应用过滤器
- sql - 如何用单词替换字母数字字符串中的数字
- c++ - C++ 中的多线程分子模拟
- reactjs - React.ForwardRef TypeScript 组件类型错误
- kotlin - 如何使用执行错误检查的自定义设置器初始化属性？
- javascript - 在 chrome 浏览器中使用 indexedDB 时有大小限制吗？
- sql - 如何在 Oracle 中获得自然数字排序顺序？
- google-apps-script - 仅将当前工作表导出到 Excel 的功能，其中包含值和格式