How to Get Group of Records With Latest Date Using Linq to Entities

How to get group of records with latest date using Linq to Entities

I've finally found the way to include also the PriceValue in the results.

Starting from the last query I've posted, I've to simply add the

g.OrderByDescending(t => ((t.Year* 100) + t.Month)).FirstOrDefault().PriceValue

So it will be:

var lastValues = (from a in Analysis
group a by new {a.MarketID, a.CommodityID, a.CurrencyID } into g
select new
{
g.Key.MarketID,
g.Key.CommodityID,
g.Key.CurrencyID,
date = g.Max(t => ((t.Year* 100) + t.Month)),
g.OrderByDescending(t => ((t.Year* 100) + t.Month)).FirstOrDefault().PriceValue
});

I prefer this solution as it's based on the current query I've developed.

How to select only the records with the highest date in LINQ

If you just want the last date for each account, you'd use this:

var q = from n in table
group n by n.AccountId into g
select new {AccountId = g.Key, Date = g.Max(t=>t.Date)};

If you want the whole record:

var q = from n in table
group n by n.AccountId into g
select g.OrderByDescending(t=>t.Date).FirstOrDefault();

Get the id of the latest record in group by with linq

GroupBy cannot help here because of SQL limitation. But you can write workaround

var latestPositions = from bs in Scan
where !Scan.Any(s => s.Asset == bs.Asset && s.LastSeen > bs.LastSeen)
select bs;

But I have to mention that fastest way is using window functions which are not available in EF Core:

SELET 
sc.Id
FROM (
SELECT
s.Id,
ROW_NUMBER() OVER (PARTITION BY s.Asset ORDER BY s.LastSeen DESC) AS RN
FROM Scan s
) sc
WHERE sc.RN == 1

But there is exists EF Core extension linq2db.EntityFrameworkCore which makes it possible via LINQ (I assume that Asset is just ID, not a navigation property)

var queryRn = from bs in Scan
select new
{
Entity = bs,
RN = Sql.Ext.RowNumber().Over()
.PartitionBy(bs.Asset).OrderByDesc(bs.LastSeen).ToValue()
};

// switch to alternative translator
queryRn = queryRn.ToLinqToDB();

var latestPositions = from q in queryRn
where q.RN == 1
select q.Entity;

LINQ to select a column with max date from a group

var result = from d in data
group d by d.Name into g
select new { Name = g.Key, MaxDate = g.Max(s => s.StartTime) }

Or if you do not like query syntax then:

data.GroupBy(i => i.Name).Select(g => new
{
Name = g.Key,
MaxDate = g.Max(row => row.StartTime)
});

Linq to Entities Selecting Data with Max Date?

You could group the in-memory results after you have selected the records from the database, e.g.:

var maxDateQuery = ctx.MyTables
.Where(m => m.InsertDate >= fromDate && m.InsertDate <= toDate)
.ToArray()
.GroupBy(x => x.Id)
.Select(g => new { Id = g.Key, MaxInsertDate = g.Max(y => y.InsertDate)) }};

This should give you an IEnumerable<a'> where a' is an anonymous type with an Id and a MaxInsertDate property if that's what you want.

Trying to convert fairly complex SQL queries to LINQ-To-Entities is pointless. You should either group the in-memory results like above or use raw SQL queries



Related Topics



Leave a reply



Submit