c# - 使用 linq with join 在两个表上使用两个 where

Question

我正在使用 Csharp Linq 创建以下报告

我有如下两张表

#Usersnid     pid     name1       1       name12       1       name2#Transactionsnid     tid     location    dcode 1       T1      L1          D12       T1      L2          D12       T2      L1          D12       T2      L3          D1

The report contains

    a) columns from users table where nid != pid    b) columns from transactions where tid == T2 and nid = results from a)    c) the combination can have only one top row  in result nid     name        tid     Location2       name2       T2      L1the second record will not be present- 2     name2       T2      L3

I have tried the following, using join

var report = (from u in users where u.nid != u.pid
                      join t in transactions
                      where t.tid == "T2"
                      on u.nid equals t.nid
                      select new 
                      {
                        // the report columns
                      }).Distinct().ToList();

在第二个“哪里”显示错误

谢谢你的帮助

Answer 1

交换查询的过滤和连接部分，并将 tid 重命名为 t.tid 或其他所需的过滤子句(在您的示例中，结果表确实有与 的交易tid == "T1"，但您尝试使用 T2 进行过滤):

var report = (from u in users     
              join t in transactions 
              on u.nid equals t.tid     //<-- this line should precede 
              where t.tid == "T2"       //<-- this one
              && u.nid != u.pid
              select new 
              {
                    // the report columns
              }).Distinct().ToList();

Join 部分不能分开，所以在用 on 子句完成 join 之前不能写 where。