LINQ to SQL:在多列上左联接 [英] LINQ to SQL: Left join on multiple columns
本文介绍了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 */
}
这篇关于LINQ to SQL:在多列上左联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文