c# - 使用 linq 连接查询得到错误的输出

Question

我有这个数据:用户

UserId   Name
 42      Abc  
 43      Pqr
 44      lmn
 45      xyz

映射:

MappingId  User1  User2  
1           42    43
2           42    44 
3           43    44

我想获取所有 user2 不在 user 1 中的用户，因此考虑到上述输入，输出将低于:

预期输出:

UserId   Name
 44       lmn

这是我的查询:

var data = (from user in context.Users
                            join mappng in context.Mappings on user.UserId equals mappng.User2
                            where mappng.User1 != mappng.User2
                            select new 
                            {
                                Name = user.FirstName + " " + user.LastName,
                                UserId = user.UserId,
                            }).ToList();

但是输出错误:

UserId   Name
43       Pqr
44       lmn
44       lmn

注意:没有外键关系，因此没有导航属性。

Answer 1

这是您在控制台应用程序中遇到的问题的解决方案。我使用 where !(..) 来查找不在映射 User1 中的那些。我不确定这种方法的其他替代方法。但希望它有所帮助。

class Program
{
    static void Main(string[] args)
    {
        List<User> users = new List<User>()
        {
            new User() { UserId = 42, Name = "Abc" },
            new User() { UserId = 43, Name = "Pqr" },
            new User() { UserId = 44, Name = "lmn" },
            new User() { UserId = 45, Name = "xyz" },
        };

        List<UserMapping> userMappings = new List<UserMapping>()
        {
            new UserMapping() { MappingId = 1, User1 = 42, User2 = 43},
            new UserMapping() { MappingId = 2, User1 = 42, User2 = 44},
            new UserMapping() { MappingId = 3, User1 = 43, User2 = 44},
        };

        var data = (from u in users
                        join m in userMappings on u.UserId equals m.User2
                        where !(from mm in userMappings
                               select mm.User1).Contains(u.UserId)
                        select u).Distinct();

        foreach(var entry in data)
        {
            Console.WriteLine(entry.UserId + " " + entry.Name);
        }

        Console.ReadLine();
    }
}

class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
}

class UserMapping
{
    public int MappingId { get; set; }
    public int User1 { get; set; }
    public int User2 { get; set; }
}

输出:

44 lmn