LINQ to SQL：多列左连接

Question

首先，我已经在 Google / SO 上搜索过，并检查了一些示例，但是我没有成功编写正确的 Linq 表达式：

这是我的工作 SQL 查询的样子：

select *
from Places p
left join VoteLog v
on p.Id = v.PlaceId
and v.UserId = '076a11b9-6b14-4230-99fe-28aab078cefb' --demo userid

这是我使用linq的尝试：

public IQueryable<Place> GetAllPublic(string userId)
{
    var result = (from p in _db.Places
                 join v in _db.VoteLogs
                 on p.Id equals v.PlaceId // This works but doesn't fully reproduce my SQL query
                 // on new { p.Id, userId} equals new {v.PlaceId, v.UserId} -> Not ok
                 where p.Public == 1
                 select new
                 {
                    Id = p.Id,
                    UserId = p.UserId,
                    X = p.X,
                    Y = p.Y,
                    Titlu = p.Titlu,
                    Descriere = p.Descriere,
                    Public = p.Public,
                    Votes = p.Votes,
                    DateCreated = p.DateCreated,
                    DateOccured = p.DateOccured,
                    UserVoted = v.Vote
                 })
        .ToList()
        .Select(x => new Place()
        {
            Id = x.Id,
            UserId = x.UserId,
            X = x.X,
            Y = x.Y,
            Titlu = x.Titlu,
            Descriere = x.Descriere,
            Public = x.Public,
            Votes = x.Votes,
            DateCreated = x.DateCreated,
            DateOccured = x.DateOccured,
            UserVoted = x.UserVoted
        }).AsQueryable();

- Gerald Hughes

---

尝试访问 https://dev59.com/o3RC5IYBdhLWcg3wOOP1。 - MichaelMao

你为什么要选择两次？ - Zein Makki

Answer 1

您的查询中没有使用任何左连接（left join）。请尝试以下方式：

from p in _db.places
join v in _db.VoteLogs

//This is how you join by multiple values
on new { Id = p.Id, UserID = userId } equals new { Id = v.PlaceId, UserID = v.UserID } 
into jointData

//This is how you actually turn the join into a left-join
from jointRecord in jointData.DefaultIfEmpty()

where p.Public == 1
select new
{
    Id = p.Id,
    UserId = p.UserId,
    X = p.X,
    Y = p.Y,
    Titlu = p.Titlu,
    Descriere = p.Descriere,
    Public = p.Public,
    Votes = p.Votes,
    DateCreated = p.DateCreated,
    DateOccured = p.DateOccured,
    UserVoted = jointRecord.Vote 
    /* The row above will fail with a null reference if there is no record due to the left join. Do one of these:
       UserVoted = jointRecord ?.Vote - will give the default behavior for the type of Uservoted
       UserVoted = jointRecord == null ? string.Empty : jointRecord.Vote */
}

- Gilad Green

---

错误 5：连接子句中的一个表达式类型不正确。在调用“GroupJoin”时，类型推断失败。 - Gerald Hughes

@Stefan - иҝҷжҳҜеӣ дёәonзҡ„еҢҝеҗҚзұ»еһӢзҡ„зұ»еһӢ/еҗҚз§°дёҚеҢ№й…ҚгҖӮзҺ°еңЁеҶҚиҜ•иҜ•гҖӮ - Gilad Green

在 UserVoted = v.Vote 处：错误 5，'v' 的名称在当前上下文中不存在。 - Gerald Hughes

@Stefan - 确实是我的错 - 应该是 jointRecord.Vote。已修复。 - Gilad Green

非常感谢Gilad Green :) - Gerald Hughes

如何添加第二组字段关联连接以模拟 Or SQL 条件。 - Golden Lion

Answer 2

你需要使用 .DefaultIfEmpty() 来执行左连接。然后你需要决定在右表产生 null 的情况下该怎么办。你可以使用三元运算符 ( ? : ) 来处理。

var result = 
    (from p in _db.Places
    join v in _db.VoteLogs
    on new { p.Id, userId } equals new { v.PlaceId, v.UserId } into LEFTJOIN
    from result in LEFTJOIN.DefaultIfEmpty()
    where p.Public == 1
    select new
    {
        Id = p.Id,
        UserId = p.UserId,
        X = p.X,
        Y = p.Y,
        Titlu = p.Titlu,
        Descriere = p.Descriere,
        Public = p.Public,
        Votes = p.Votes,
        DateCreated = p.DateCreated,
        DateOccured = p.DateOccured,
        UserVoted = result == null ? null /* replace with your value */ : x.Vote
    }).AsQueryable();

return result;

- Zein Makki

Answer 3

如果您的问题是将关键字分配给那些多个连接条件：

// on new { p.Id, userId} equals new {v.PlaceId, v.UserId}

尝试使用。

on new { a = p.Id, b = userId} equals new { a = v.PlaceId, b = v.UserId}

- user6996876