首先,我搜索了 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();