如何在EF Core中关联多表并仅查询指定列的数据集？

看你的需求是要把**悲伤调查（sadSurveys）和趣味调查（funSurveys）**的提交记录统一展示对吧？因为这两个表结构类似，都关联了审计表和用户表，咱们可以用UNION ALL把两个查询的结果合并在一起，就能一次性输出所有提交的调查数据了。

基础版SQL语句（MySQL/MSSQL通用） #

SELECT 
    s.Id AS SurveyId,
    u.Name AS UserName,
    a.SubmittedOn AS SubmitDate
FROM sadSurveys s
LEFT JOIN audits a ON s.AuditId = a.Id
LEFT JOIN users u ON s.UserId = u.Id

UNION ALL

SELECT 
    f.Id AS SurveyId,
    u.Name AS UserName,
    a.SubmittedOn AS SubmitDate
FROM funSurveys f
LEFT JOIN audits a ON f.AuditId = a.Id
LEFT JOIN users u ON f.UserId = u.Id

进阶版：区分调查类型 #

如果需要在结果里明确区分是悲伤调查还是趣味调查，可以额外加一个常量字段：

SELECT 
    s.Id AS SurveyId,
    u.Name AS UserName,
    a.SubmittedOn AS SubmitDate,
    'Sad Survey' AS SurveyType
FROM sadSurveys s
LEFT JOIN audits a ON s.AuditId = a.Id
LEFT JOIN users u ON s.UserId = u.Id

UNION ALL

SELECT 
    f.Id AS SurveyId,
    u.Name AS UserName,
    a.SubmittedOn AS SubmitDate,
    'Fun Survey' AS SurveyType
FROM funSurveys f
LEFT JOIN audits a ON f.AuditId = a.Id
LEFT JOIN users u ON f.UserId = u.Id

小提醒 #

• 优先用UNION ALL而非UNION：两类调查记录是完全独立的，不会出现重复，UNION ALL不需要做去重操作，性能更高效；如果确实存在重复风险（比如两个表出现相同的SurveyId），再换成UNION。
• 要保证两个SELECT语句返回的字段数量、顺序和数据类型完全匹配，否则会触发语法错误。

内容的提问来源于stack exchange，提问作者egmfrs