Convert SQL - Linq with SQL IN

Question

我在下面有这个sql查询工作正常。我想将其转换为LINQ

SELECT DISTINCT TOP (100) PERCENT PersonId, FamilyName, GivenName, MiddleName, CompleteName
 FROM         dbo.MainTable AS a
 WHERE     (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table1
                        WHERE      (PersonId = a.PersonId))) OR
                  (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table2
                        WHERE      (PersonId = a.PersonId))) OR
                  (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table3
                        WHERE      (PersonId = a.PersonId)))
ORDER BY FamilyName

已尝试的代码

 var Sigids = (from a in db.Table1 select a.PersonId);
 var RecIds = (from a in db.Table2 select a.PersonId);
 var AppIds = (from a in db.Table3 select a.PersonId);
 var signatories = (from p in db.MainTable
                   where (Sigids.Contains(p.PersonId)
                   && RecIds.Contains(p.PersonId)
                   && AppIds.Contains(p.PersonId))
                   select new { CompleteName = p.CompleteName, p.PersonId });

但仅当 personId 是这 3 个表时，才会产生这种情况。我在这里要实现的是我想显示主表中表 1 - 3 的所有名称。

Answer 1

基于下面sql查询中的where条件

 WHERE     (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table1
                        WHERE      (PersonId = a.PersonId))) OR
                  (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table2
                        WHERE      (PersonId = a.PersonId))) OR
                  (PersonId IN
                      (SELECT     PersonId
                        FROM          dbo.Table3
                        WHERE      (PersonId = a.PersonId)))

linq 中OR运算符的等效项是 | 或 || 而不是 && 。您的 linq 代码应使用以下 | 更改为以下内容：

 var Sigids = (from a in db.Table1 select a.PersonId);
 var RecIds = (from a in db.Table2 select a.PersonId);
 var AppIds = (from a in db.Table3 select a.PersonId);
 var signatories = (from p in db.MainTable
                   where (Sigids.Contains(p.PersonId)
                   | RecIds.Contains(p.PersonId)
                   | AppIds.Contains(p.PersonId))
                   select new { CompleteName = p.CompleteName, p.PersonId });

或者像下面这样使用||：

 var Sigids = (from a in db.Table1 select a.PersonId);
 var RecIds = (from a in db.Table2 select a.PersonId);
 var AppIds = (from a in db.Table3 select a.PersonId);
 var signatories = (from p in db.MainTable
                   where (Sigids.Contains(p.PersonId)
                   || RecIds.Contains(p.PersonId)
                   || AppIds.Contains(p.PersonId))
                   select new { CompleteName = p.CompleteName, p.PersonId });