I am trying to convert a SQL query to a LINQ to entities query, but am having some problems with the LINQ select block.
Here is the SQL query which performs as expected:
SELECT distinct( p.PendingID,
p.Description,
p.Date,
f.Status,
u.UserName,
m.MapID
FROM Pending p
JOIN Users u
ON p.UserID = u.UserID
LEFT JOIN Forks f
ON p.PendingID = f.PendingID
LEFT JOIN Maps m
ON f.ForkID = m.ForkID
ORDER BY p.Date DESC
Here is the LINQ to entities query as I have it thus far:
var pList = (from pending in pendingItems
// JOIN
from user in userList.Where(u => pending.UserID == u.UserID)
// LEFT OUTER JOIN
from fork in forkList.Where(f => pending.ID == f.PendingID)
.DefaultIfEmpty()
// LEFT OUTER JOIN
from map in mapList.Where(m => fork.ID == m.ForkID)
.DefaultIfEmpty()
orderby pending.Date descending
select ne开发者_运维问答w
{
ItemID = pending.ID, // Guid
Description = pending.Description, // String
Date = pending.Date, // DateTime
Status = fork.Status, // Int32 (*ERROR HERE*)
UserName = user.UserName, // String
MapID = map.ID // Guid (*ERROR HERE*)
})
.Distinct()
.ToList();
The LINQ query fails on either of the following 2 lines, which attempt to assign values retrieved from left outer join results. If the following lines are omitted, the LINQ query completes without errors:
Status = fork.Status,
MapID = map.ID
Why are those 2 property assignments failing within the LINQ query's select block?
The problem is that due to your outer joins, fork
and map
may be null. Of course when they're null, you can't access their properties. You may need something like this:
Status = (fork == null) ? null : fork.Status,
MapID = (map == null) ? null : map.ID
精彩评论