C# LINQ to Entities One-To-Many relationship: Problem displaying last log entry data

I have a web application with two tables to track user shares. Shares must be approved and so they go through a series of status changes. Each status change is recorded in the log and timestamped. The current status is always the last entry in the log for that particular share.

Here are the entities (with brevity):

Shares: ShareID UserName

ShareLogs: LogID ShareID CreatedDate Status

I'm trying to get a list of Shares with their current status based on the last log entry. Here is the SQL query that returns the correct result, but I'd like to do this using LINQ.

开发者_如何学运维

SELECT s.ShareID, s.UserName, sl.Code, sl.CreatedDate
FROM Shares s
INNER JOIN 
(
SELECT s.ShareID, s.Code, s.CreatedDate
    FROM ShareLogs sl
    INNER JOIN (SELECT ShareID, MAX(CreatedDate) logDate FROM ShareLogs GROUP BY ShareID) sl2
        ON sl.ShareID = sl2.ShareID AND sl.CreatedDate = sl2.logDate) psl1
ON s.ShareID = sl1.ShareID
ORDER BY s.ShareID

I have written one LINQ query that seems to work, but I feel like there should be a better way, as this query looks pretty terrible.

var query = from list in
(from s in context.Shares
join sl in context.ShareLogs on s.ShareID equals sl.ShareID
where sl.CreatedDate == s.ShareLogs.Max(e => e.CreatedDate)
select new
{
 share = s
 status = sl.Code,
 processDate = sl.CreatedDate
}).Where(e => e.status == 2 || e.status == 3)
select list;

---

You could try doing a join, then grouping on all rows except the ShareID, then just get the share with the greatest date. eg:

var shareList = context.Shares;
var shareLogList = context.ShareLogs;
var join = shareList.Join(shareLogList, o=>o.ShareID, i=>i.ShareID, s=>new{o.ShareID, o.UserName,i.LogID, i.CreatedDate, i.Status});
var query = join.GroupBy(g=>new{g.ShareID,g.UserName}, (key,lines)=>new{key.ShareID, Status = lines.Max(m=>m.CreatedDate).Status});

That should do it! You can combine it into one statement if you'd like, I just separated them for clarity.

---

var query = (from s in context.Shares
            let latestOrder = s.ShareLogs                                             
                                         .OrderByDescending(sl => sl.CreatedDate)
                                         .First()
            select new 
            {
                share = s,
                status = latestOrder.Code,
                processDate = latestOrder.CreatedDate
            }).Where(sl => sl.status == 2 || sl.status ==3);

Let me know if you see this as an improvement.

Edit: I'm not 100% sure on where you need the Where filter so I left it. I'm thinking it may be actually needed in the Let statement. Or we could move it into the Comprehension syntax to be more consistent.

---

I prefer method syntax, so here's my answer. Note I'm also doing a Group By but not a Join.

 context.ShareLogs.GroupBy(log => log.ShareID)
     .Select(g => g.Logs.Single(log => 
         log.CreatedDate == g.Logs.Max(g => g.CreatedDate)))         
     .Select(l => new { Share = l.Share, Status = l.Code, Date = l.CreatedDate });

You should be able to group the ShareLogs to determine the correct ShareLog object (which contains the Code, Date and ShareID) - and then retrieve the Share by using the navigation property log.Share.

No need for a join, as far as I can see, because as it usually does LINQ has hidden that away via navigation properties.