LINQ 左连接和右连接

Question

我需要帮助，

我有两个名为A和B的dataTable，我需要A表中的所有行和匹配B表中相应行的数据。

例如：

A:                                           B:

User | age| Data                            ID  | age|Growth                                
1    |2   |43.5                             1   |2   |46.5
2    |3   |44.5                             1   |5   |49.5
3    |4   |45.6                             1   |6   |48.5

我需要输出：

User | age| Data |Growth
------------------------                           
1    |2   |43.5  |46.5                           
2    |3   |44.5  |                          
3    |4   |45.6  |

- raja

Answer 1

你提供的示例数据和输出并未展示左连接。如果使用了左连接，输出结果应该如下所示（请注意，我们对于用户1有三个结果，即用户1拥有的每个增长记录都会出现一次）：

User | age| Data |Growth
------------------------                           
1    |2   |43.5  |46.5                           
1    |2   |43.5  |49.5     
1    |2   |43.5  |48.5     
2    |3   |44.5  |                          
3    |4   |45.6  |

假设你仍然需要进行左连接；以下是在Linq中执行左连接的方法：

var results = from data in userData
              join growth in userGrowth
              on data.User equals growth.User into joined
              from j in joined.DefaultIfEmpty()
              select new 
              {
                  UserData = data,
                  UserGrowth = j
              };

如果你想进行右连接，只需交换所选表的顺序，像这样：

var results = from growth in userGrowth
              join data in userData
              on growth.User equals data.User into joined
              from j in joined.DefaultIfEmpty()
              select new 
              {
                  UserData = j,
                  UserGrowth = growth
              };

代码中重要的部分是into语句，后面跟随着DefaultIfEmpty。这告诉Linq，如果在另一个表中没有匹配的结果，我们想要使用默认值（即null）。

- Doctor Jones

Answer 2

琼斯医生展示了左外连接，但正确的答案略有不同——因为在原问题中，两个表通过年龄字段链接，所以为了得到与要求完全相同的结果，应该使用以下代码。

....
//ctx = dataContext class - not shown here.
var user1 = new UserData() { User = 1, Age = 2, Data = 43.5 };
var user2 = new UserData() { User = 2, Age = 3, Data = 44.5 };
var user3 = new UserData() { User = 3, Age = 4, Data = 45.6 };

ctx.UserData.AddRange(new List<UserData> { user1, user2, user3 });

var growth1 = new UserGrowth() { Id = 1, Age = 2, Growth = 46.5 };
var growth2 = new UserGrowth() { Id = 1, Age = 5, Growth = 49.5 };
var growth3 = new UserGrowth() { Id = 1, Age = 6, Growth = 48.5 };

ctx.UserGrowth.AddRange(new List<UserGrowth> { growth1, growth2, growth3 });

var query = from userData in ctx.UserData
                        join userGrowth in ctx.UserGrowth on userData.Age equals userGrowth.Age
                            into joinGroup
                        from gr in joinGroup.DefaultIfEmpty()
                        select new
                        {
                            User = userData.User,
                            age = userData.Age,
                            Data = (double?)userData.Data,
                            Growth = (double?)gr.Growth
                        };

Console.WriteLine("{0} | {1} | {2} | {3}", "User", "age", "Data", "Growth");
            foreach (var x in query)
            {
                Console.WriteLine("{0} | {1} | {2} | {3}", x.User, x.age, x.Data, x.Growth);
            }


.... with following entity classes:

public class UserData
    {
        [Key]
        public int User { get; set; }
        public int Age { get; set; }
        public double Data { get; set; }
    }

    public class UserGrowth
    {
        public int Id { get; set; }
        public int Age { get; set; }
        public double Growth { get; set; }
    }

- Vitaliy Markitanov

---

你和Jones医生的答案都是正确的。由于OP没有定义“匹配”是什么，所以我们必须推断他是否提出了他想要的问题或者给出了他想要的样本输出。根据问题，应该使用ID，但根据样本输出，年龄可能是更合适的假设。你的解决方案同样有效。所以谢谢，你的贡献使得答案更加完整。 - Suncat2000

Answer 3

使用 Let 关键字是一种简单的方法。这对我很有效。

from AItem in Db.A
Let BItem = Db.B.FirstOrDefault(x => x.id == AItem.id ) 
Where SomeCondition
Select new YourViewModel
{
    X1 = AItem.a,
    X2 = AItem.b,
    X3 = BItem.c
}

这是一个左连接模拟。如果B表中的每个项目都没有与A表中的项目匹配，则B项目返回null。

- mahdi moghimi

---

这实际上是对我有效的解决方案。 - Dan Cundy

Answer 4

这里有一个简单的例子。
模型：

class Employee
{
    public string Name { get; set; }
    public int ID { get; set; }
    public int ProjectID { get; set; }
}

class Project
{
    public int ProjectID { get; set; }
    public string ProjectName { get; set; }
}

方法：

public void LeftRightJoin()
{
    // Example Projects
    List<Project> ListOfProjects = new()
    {
        new(){ ProjectID = 1, ProjectName = "UID" },
        new(){ ProjectID = 2, ProjectName = "RBS" },
        new(){ ProjectID = 3, ProjectName = "XYZ" },
    };
    // Example Employees
    List<Employee> ListOfEmployees = new(){
        new(){ ID = 1, Name = "Sunil",  ProjectID = 1 },
        new(){ ID = 1, Name = "Anil", ProjectID = 1 },
        new(){ ID = 1, Name = "Suman", ProjectID = 2 },
        new(){ ID = 1, Name = "Ajay", ProjectID = 3 },
        new(){ ID = 1, Name = "Jimmy", ProjectID = 4 }
    };

    //Left join
    var Ljoin = from emp in ListOfEmployees
                join proj in ListOfProjects
                    on emp.ProjectID equals proj.ProjectID into JoinedEmpDept
                from proj in JoinedEmpDept.DefaultIfEmpty()
                select new
                {
                    EmployeeName = emp.Name,
                    ProjectName = proj?.ProjectName
                };

    //Right outer join
    var RJoin = from proj in ListOfProjects
                join employee in ListOfEmployees
                on proj.ProjectID equals employee.ProjectID into joinDeptEmp
                from employee in joinDeptEmp.DefaultIfEmpty()
                select new
                {
                    EmployeeName = employee?.Name,
                    ProjectName = proj.ProjectName
                };

    //Printing result of left join
    Console.WriteLine(string.Join("\n", Ljoin.Select(emp => $" Employee Name = {emp.EmployeeName}, Project Name = {emp.ProjectName}")));

    //printing result of right outer join
    Console.WriteLine(string.Join("\n", RJoin.Select(emp => $" Employee Name = {emp.EmployeeName}, Project Name = {emp.ProjectName}")));
}

- Udara Kasun