Serj Sagan Serj Sagan - 2 months ago 7
SQL Question

EF Map The Latest Object In A List Of Objects

I am trying to do this

Linq
query:

dataContext.Request.Where(x => x.LatestResponse.IsReviewRequired);


The problem is that
LatestResponseID
is actually not a column on the
Request
table.

To get the
LatestResponse
, I have to do this
SQL
:

SELECT ....
FROM [doe].[Request] Req
LEFT JOIN (
SELECT MAX(ResponseID)
FROM [doe].[Response]
GROUP BY [RequestID]) AS Resp ON Req.[RequestID] = Resp.[RequestID]
LEFT JOIN [doe].[Response] LatestResp ON Resp.[ResponseID] = LatestResp.[ResponseID]


Can I
map
this? If so, how?

Answer

You could do something like this (if you want both sides of the join back):

var results = dataContext.Requests.Select(request => new
        {
            Request = request,
            LatestResponse = request .Responses.FirstOrDefault(response => response .RequestID == request.Responses.Max(response2 => response2.RequestID))
        });

Also, if you are trying to just get the request object back, but have it filtered by a property on the latest response, here is an example of that:

var results = dataContext.Requests.Select(request => new
        {
            Request = request,
            LatestResponse = request.Responses.FirstOrDefault(response => response.RequestID == request.Responses.Max(response2 => response2.RequestID))
        }).Where(request => request.LatestResponse.MyProperty == "Value").Select(request => request.Request);