[C#]我怎么能与另一个表 linq 的联盟

标签: Linq SQLServer C#
发布时间: 2017/2/27 3:04:23
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有我无休止地一直工作在此 linq 查询︰

var complaintHistory = from histories in DbContext.Histories
                                       where (histories.Action_Date > Complaint.Date_Created_On && (from c in taskIds
                                               select "<Task_ID=" + c + ">").Contains(histories.Primary_Key) ||
                                              (from c in notificationListIds
                                               select "<Notification_List_ID=" + c + ">").Contains(histories.Primary_Key) ||
                                              (from c in emailSentIds
                                               select "<Email_Sent_ID=" + c + ">").Contains(histories.Primary_Key) ||
                                                  histories.Primary_Key == "<Complaint_ID=" + Complaint.Complaint_ID + ">") &&
                                                  histories.Field_Name != "rowversion"
                                       select histories;

我试图找出如何使该 linq 查询更喜欢此 sql 语句︰

select * from etc.History as c where 
primary_key in 
(
select '<Task_ID=' + cast(Task_ID AS nvarchar) +  '>'
from  etc.Task
where Complaint_ID = 1209

UNION

select '<Notification_List_ID=' + cast(Notification_List_ID AS nvarchar) +  '>' 
from [etc].[Notification_List] 
where Complaint_ID = 1209                --where Notification_List_ID = 4479
UNION

select '<Email_Sent_ID=' + cast(Email_ID AS nvarchar) +  '>'
from [etc].[Email_Sent]
where Complaint_ID = 1209
UNION
SELECT '<Complaint_ID=' + cast(1209 AS nvarchar) +  '>')
  AND
   Field_Name!='rowversion'

我想提高 linq 查询的性能,所以我要向联盟"taskid"到任务表所以我可以使用 where 子句"投诉 ID"来缩小查询范围。

解决方法 1:

LINQ 并提供 Union 方法。这能帮助您入门。我也建议了一点,所以代码的可读性更好清理的东西︰

var tasks = // LINQ Query for Task_ID
var notifications = // LINQ Query for Notification_List_ID
var emails = // LINQ Query for Email_Send_ID and Complaint_ID

var union = tasks.Union(notifications).Union(emails);

DbContext.Histories.Where(h => 
    h.Action_Date > Complaint.Date_Created_On &&
    (union.Contains(h.Primary_Key) ||
        h.Primary_Key == "<Complaint_ID=" + h.Complaint_ID + ">") &&
    h.Field_Name != "rowversion");
赞助商