Select one of each matching results from group last record from date

I have multiple customers that are a part of a group designated by a group id.

I would like to retrieve 1 record from a related table for each of the matching group members (last record before a certain date).

Currently I query for a list of group members then for each member i run another query to retrieve last record from a date.

I would like to do this with one query since i can pull up the associated table records using group id – however this returns all the records associated to group (bad).

If i use first or default i only get results for first group found. I want 1 record from each group member.

My Code (returns all associated records of group members):

  List<Record> rs = (from x in db.Records where (x.Customer.Group == udcg && x.CloseDate < date && x.CloseDate < earlyDate) orderby x.CloseDate descending select x).ToList();

But i just want one from each instead of all.

Code I use now:

var custs = (from x in db.Customers where (x.group == udcg) select new { x.CustomerID }).ToList();
    expected = custs.Count();
    foreach (var cust in custs)
    {
      Record br = (from x in db.Records where (x.Customer.CustomerID == cust.CustomerID && x.CloseDate < date && x.CloseDate < earlyDate)) orderby x.CloseDate descending select x).FirstOrDefault();
      if (br != null)
        {
           total = (double)br.BillTotal;
           cnt++;        
        }
    }

Answer

I think this could work

db.Customers
    .Where(c => c.group == udcg)
    .Select(c => db.Records
            .Where(r => r.Customer.CustomerID == c.CustomerID)
            .Where(r => r.CloseDate < date)
            .Where(r => r.CloseDate > date.AddMonths(-2))
            .OrderByDescending(r => r.CloseDate)
            .FirstOrDefault())
    .Where(r => r != null)

It is translated into one sql query. That means it uses one roundtrip to the server. That could be quite a big difference in performace when compared to the foreach loop. If you look at the generated sql, it would be something like

SELECT some columns
FROM Customers
OUTER APPLY (
    SELECT TOP (1) some columns
    FROM Records
    WHERE some conditions
    ORDER BY CloseData DESC
    )

In terms of performace of the query itself, I would not expect problems here, sql server should not have problems optimizing this form (compared to other ways you could write this query).