SQL query in linq pseudocode

Question

I need to output only the last record from the table, but only those users who are in the group. I can only display those who are in the group. But I can't get the last record. I wrote 2 requests, the first one gives an error, the second one outputs all the records...

foreach (var item1 in brigades.Persons)
            {
                var persontrack =
                from s in new DataContext().GetTable<PersonTrack>()
                join t in a on s.PersonId equals t.Id
                where t.Id == item1.Id
                group s by s.Id into grp
                from last in (from custRec in grp where custRec.Id == grp.Max(cr => cr.Id) select custRec)
                select last;
                ps.AddRange(persontrack);
            }

foreach (var item in brigades.Persons)
        {
            var persontrack = from s in new DataContext().GetTable<PersonTrack>()
                              join t in brigades.Persons on s.PersonId equals t.Id
                              where t.Id == item.Id
                              orderby s.Id
                              descending
                              select s;
            ps.AddRange(persontrack);
        }

Answer 1

As far as I know this is only possible with the C# way of writing linq.

Quick fix

Your 2nd query will make it if you add FirstOrDefault (the query gets translated to sql, even written this way, and only last element is returned).

using (var db = new DataContext())    // create once to gain performance
{
    foreach (var item in brigades.Persons)
    {
        var persontrack = (from s in db.GetTable<PersonTrack>()
                           join t in brigades.Persons on s.PersonId equals t.Id
                           where t.Id == item.Id
                           orderby s.Id
                           descending
                           select s).FirstOrDefault();
        if (persontrack != null)
            ps.Add(persontrack);
    }
}   // DataContext is disposed here

or this would also work, but would be odd to add nulls:

ps.Add(persontrack.FirstOrDefault());

---

Alternative

However, using foreach causes several calls to the db. You could rewrite it into a single query like this:

using (var db = new DataContext())
{
    var eachLastTrack = from s in db.GetTable<PersonTrack>()
                        join t in brigades.Persons on s.PersonId equals t.Id
                        // group by PersonId instead, to avoid foreach!
                        group s by s.PersonId into grp 
                        select grp.OrderByDescending(s => s.Id).FirstOrDefault();
                        // note: FirstOrDefault() applied for each "t.Id"!
    ps.AddRange(eachLastTrack);
}