Adjusted Entity Framework Subquery

Question

I would like to create this using the Entity Framework. A list of new objects containing fields from the parent and fields from the current child record. I would write SQL as a correlated subquery:

SELECT p.PolicyNumber, p.HomeState, pt.RevisionDate, pt.TranStatus FROM dbo.Policy p JOIN dbo.PolicyTran pt ON p.Id = pt.Policy_Id AND pt.RevisionDate = ( SELECT MAX(mpt.RevisionDate) FROM dbo.PolicyTran mpt WHERE p.Id = pt.Policy_Id ) WHERE p.HomeState = 'NY' 

The policy context has navigation to the list of transactions (PolicyTran).

 var query = context.Policies.Include(t => t.PolicyTransactions); 

No matter what I try to do, Linq is incorrect or SQL is incorrect. Time to call specialists.

Answer 1

This is not very, but I believe that it gives you what you wanted.

 var query = db.Policies .Where(p => p.HomeState == "NY") .Join(db.PolicyTrans, p => p.Id, t => t.Policy_Id, (p, t) => new { P = p, T = t.OrderBy(tr => tr.RevisionDate).First() }) .Select(g => new { PolicyNumber = gPPolicyNumber, HomeState = gPHomeState, TransStatus = gPTransStatus, RevisionDate = gTRevisionDate }); 

Answer 2

This is one way, although there are 2 separate backups. You could try embedding the first statement in the second, but this is harder for me right now, since I don't have a compiler.

 var maxRevisionDate = context.PolicyTran.Max(x => x.RevisionDate); var results = context.Policies.Where(x => x.PolicyTran.RevisionDate == maxRevisionDate && x.HomeState == "NY").Include("PolicyTran") 

I made assumptions that your entity names are the same as your database tables. The result is IEnumerable of Policies and their corresponding PolicyTrans.