LINQ GroupBy, whilst keeping all object fields

.net, c#, lambda, linq

Solution

Group by composite key

_context.Policies.GroupBy(a => new {a.intPolicyId, *other fields*}).Select(
    group=> new {
        PolicyId = group.Key.intPolicyId,
        HistoryId = group.Max(intHistoryId),
        *other fields*
    }
);

Another way - grab histories, than join back with the rest of the data, something like this (won't work out of the box, will require some refining)

var historyIDs = _context.Policies.GroupBy(a=>a.intPolicyId).Select(group => new {
                                            PolicyID = group.Key,
                                            HistoryID = group.Max(a => a.intHistoryID)
                                        });

var finalData = from h in historyIDs
                join p in _context.Policies on h.intPolicyId equals p.intPolicyId
                select new {h.HistoryId, *all other policy fields*}

And yet another way, even simpler and not require a lot of typing :):

var historyIDs = _context.Policies.GroupBy(a=>a.intPolicyId).Select(group => new {
                                            PolicyID = group.Key,
                                            HistoryID = group.Max(a => a.intHistoryID)
                                        });

var finalData = from h in historyIDs
                join p in _context.Policies on h.PolicyId equals p.intPolicyId && h.HistoryId equals p.HistoryId
                select p

Basically it's somewhat equivalent to the following SQL query:

select p.*
from Policy p
inner join (
    select pi.policyId, max(pi.historyId)
    from Policy pi
    group by pi.policyId
) pp on pp.policyId = p.policyId and pp.historyId = p.historyId

Problem

I've currently got this sample table of data: ``` ID | Policy ID | History ID | Policy name 1 | 1 | 0 | Test 2 | 1 | 1 | Test 3 | 2 | 0 | Test1 4 | 2 | 1 | Test1 ``` Out of this, I want to group by the Policy ID and History ID (MAX), so the records I want to be kept are ID's 2 and 4: ``` ID | Policy ID | History ID | Policy name 2 | 1 | 1 | Test 4 | 2 | 1 | Test1 ``` I've tried to do this in LINQ and stumbling on the same issue every time. I can group my entities, but always into a group where I have to re-define the properties, rather than have them kept from my Policy objects. Such as: ``` var policies = _context.Policies.GroupBy(a => a.intPolicyId) .Select(group => new { PolicyID = group.Key, HistoryID = group.Max(a => a.intHistoryID) }); ``` This simply just brings out a list of objects which have "Policy ID" and "History ID" within them. I want all the properties returned from the Policies object, without having to redefine them all, as there are around 50+ properties in this object. I tried: ``` var policies = _context.Policies.GroupBy(a => a.intPolicyId) .Select(group => new { PolicyID = group.Key, HistoryID = group.Max(a => a.intHistoryID) PolicyObject = group; }); ``` But this errors out. Any ideas?

Original source