LINQ to SQL:在多列上左联接

本文介绍了LINQ to SQL:在多列上左联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

首先，我搜索了google/SO，检查了一些示例，但是我没有设法编写正确的linq表达式:

First of all, I have searched google/SO, checked some examples, but I didn't manage to write the proper linq expression:

这是我的有效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();

推荐答案

在您的查询中，您没有执行任何left join. 试试这个:

In your query you didn't do any left join. Try this:

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 */ }