우선 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();
귀하의 쿼리에서 귀하는 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 */
}
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다