我怎样才能与另一个表连接呢?

Question

我有这个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"来缩小查询的范围。

Answer 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");

Answer 2

@Justin Niessner是对的。但是，您可以调用多个选择并将它们与Enumerable.Concat<T>方法连接。