How to Get First Record in Each Group Using Linq

How to get first record in each group using Linq

var res = from element in list
          group element by element.F1
              into groups
              select groups.OrderBy(p => p.F2).First();

C# Linq return the first element of each group

The code should return the first element in each group, not the content of the first group.

public IEnumerable<ProjektStatus> GetCurrentProjektStatus(Func<ProjektStatus, bool> where)
{
    return this.db.ProjektStatus
                  .Where(where)
                  .GroupBy(x => x.ProjektId)
                  .Select(x => x.OrderByDescending(y => y.StatusMonatJahr).First());
}

Some remarks :

• Language keyword should be avoided as variable name. Here it's about the where parameter. whereFunc is a good name.
• The GroupBy, Select, OrderByDescending operations can be done remotely (server side), for that they should be called first. An other option is to do everything remotely, for that, the type of the whereFunc should be Expression<Func<ProjectStatus, bool>>.
• Personal opinion: you should prefer to code in English, if your company doesn't do it, I feel bad for you.

Here is the result :

public IEnumerable<ProjectStatus> GetCurrentProjectStatuses(Func<ProjectStatus, bool> whereFunc)
{
    return ProjectStatuses
        .GroupBy(s => s.ProjectId)
        .Select(g => g.OrderByDescending(s => s.MonthAndYear).First())
        .AsEnumerable()   // From now on the execution is done locally 
        .Where(whereFunc);
}

Linq Query Group By and Selecting First Items

See LINQ: How to get the latest/last record with a group by clause

var firstItemsInGroup = from b in mainButtons
                 group b by b.category into g
select g.First();

I assume that mainButtons are already sorted correctly.

If you need to specify custom sort order, use OrderBy override with Comparer.

var firstsByCompareInGroups = from p in rows
        group p by p.ID into grp
        select grp.OrderBy(a => a, new CompareRows()).First();

See an example in my post "Select First Row In Group using Custom Comparer"

LINQ, first record in each group only if condition is met

Linq methods, returning IQueryable<T>, are lazily evaluated and not materialized.

That means that you actually can add a where condition and the whole expression tree will be updated accordingly before the translation to SQL happens (by a DB provider).

You're missing an important aspect of the execution flow: the generated db command will be retrieved from the db only when you enumerate the collection.

Chaining a Where after a Group in Linq does not result in in-memory filtering but in a HAVING clause, exactly what you're asking.

The fluent version could be simplified as

repo.GroupBy(x => x.Revision)
.Select(x => x
   .OrderBy(y => y.CreationDate)
   .First())
.Where( x => !x.Terminated)

Or the query version should be

var query = from rp in repo 
group rp by rp.Revision into grouped 
where !grouped.OrderBy(y => y.CreationDate).First().Terminated 
select grouped.OrderBy(y => y.CreationDate).First();

Of course it depends on the DB provider if it is able to translate the above query into SQL through the class mapping: it may be difficult indeed.

Get the first record of a group in LINQ?

data
    .GroupBy(
        x => x.CardId, 
        (x, y) => new { 
            Key = x, 
            Value = y.OrderByDescending(z => z.DateTimeStamp).FirstOrDefault() 
        }
    );

This will group all the elements by CardId, then order the elements of each group by DateTimeStamp (descending), then pare down each group to only contain the first element. Finally, it returns an enumerable of "groups" (with the scare quotes since they're actually an anonymous type instead of an IGrouping) where each group has the one item you're seeking.

How to select last record in a LINQ GroupBy clause

You can order you items

Mains.GroupBy(l => l.ContactID)
.Select(g=>g.OrderByDescending(c=>c.ID).FirstOrDefault()) 
.ToList()

How can i do Group by an property and get first record from any groups with lambda linq?

photos.GroupBy(p => p.Name).Select(p => p.First()).ToList();

Linq Group By - select a single record in each group into typed result

I guess this will solve your issue

var query =
from customeraddress in db.CustomerAddresses
join customers in db.Customers on new { CustId = customeraddress.CustId } equals new { CustId = customers.Id }
join LatestVersion in
    (
        (from customeraddress_1 in db.CustomerAddresses
            group customeraddress_1 by new
            {
                customeraddress_1.CustId
            } into g
            select new
            {
                CustId = (System.Int32)g.Key.CustId,
                MaxVersion = (System.Int32)g.Max(p => p.Version)
            }))
        on new { customeraddress.CustId, customeraddress.Version }
    equals new { CustId = Convert.ToInt32(LatestVersion.CustId), Version = Convert.ToInt32(LatestVersion.MaxVersion) }
select new
{
    Id = (System.Int32)customers.Id,
    customers.Field1,
    customers.Field2,
    customeraddress.Field3,
    customeraddress.Field4,
    Version = (System.Int32)customeraddress.Version
};
var results = query.ToList();

Linq: Select Most Recent Record of Each Group

 var query = from p in db.Payments
             where p.Status == false
             group p by p.CompanyID into op
             select new { 
                 CompanyID = op.Key,
                 NextPaymentDate = op.Max(x => x.NextPaymentDate), 
                 Status = false
             };

The reason your query is not being ordered correctly is that your query does not do proper grouping. You did correctly grouping by CompanyID, but then you have to retrieve the maximum NextPaymentDate by calling aggregate function.

Status can be assigned false because it is already filtered by Where clause in the early clauses.

---

Related Topics