Linq Query Group by and Selecting First Items

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"

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();

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.

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 to Select First Group

There is no need for the ToLookup. The lookup groups by different DateDetails, but matches is already filtered to a single date, so there is already only one group to select.

You could skip the filter and just go with:

var match = recordList.ToLookup(a => a.DateDetails).First()

lookaheadList = match.ToList();

However, this is redundant for a couple of reasons:

• If you're not storing the result of ToLookup and using it to look up other groups by date, there was no point creating the lookup object -- you could have just used GroupBy.

• If you only need the first group, there is no need for any grouping at all (either by ToLookup or GroupBy).

To directly grab the items that match the first date, use:

var firstDate = recordList.First().DateDetails;

var matches = recordList.Where(d => d.DateDetails == firstDate)

lookaheadList = matches.ToList();

Take each first element of the group by

You cannot do that with grouping. SQL has a limitation - with GROUP BY you can select only grouping keys and aggregation result. This limitation for sure extended to LINQ to Entities - after GroupBy you can select only grouping keys and aggregation result.

Such result can be achieved by SQL and Window functions:

SELECT
  r.*,
FROM 
(
   SELECT 
      s.*, 
      ROW_NUMBER() OVER(PARTITION BY s.SomeField1, s.SomeField2 ORDER BY s.SomeDate) AS RN
   FROM SomeDbSet s
   WHERE ...
) r
WHERE r.RN = 1

For those who want to stay with LINQ, I propose extension (disclaimer: I'm extension creator) linq2db.EntityFrameworkCore

And you can write query above via LINQ

var rnQuery = 
   from s in context.SomeDbSet
   where ...
   select new 
   {
      Data = s,
      RN = Sql.Ext.RowNumber().Over()
              .PartitionBy(s.SomeField1, s.SomeField2)
              .OrderBy(s.SomeDate)
              .ToValue()
   }

var resultQuery = await rnQuery
   .Where(r => r.RN == 1)
   .Select(r => r.Data)
   .ToLinqToDB();

var result = resultQuery.ToList();

// async variant may need ToListAsyncLinqToDB() call 
// because of collision in async extension methods between EF Core and linq2db
var result = await resultQuery.ToListAsyncLinqToDB();

How do I order the elements in a group by linq query, and pick the first?

Use select to get the FirstOrDefault (or First - because of the grouping you won't get a null) ordered descending:

var data = mydata.GroupBy(item => item.Type)
                 .Select(group => group.OrderByDescending(x => x.Date)
                 .FirstOrDefault())
                 .ToList();

Or SelectMany together with Take(1)

var data = mydata.GroupBy(item => item.Type)
                 .SelectMany(group => group.OrderByDescending(x => x.Date)
                 .Take(1))
                 .ToList();

---

Related Topics