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
How to Format a Number in C# With Commas and Decimals
Sending Array of Bytes from Client to Server
How to Ignore Convert to Nullable in Lambda Expressions
How to Set a Cookie on Httpclient'S Httprequestmessage
C# Download Big File from Server With Less Memory Consumption
How to Redirect to Login Page If Session Is Not Available in MVC
C# ASP.NET MVC Download a File Via Ajax
Response to Preflight Request Doesn't Pass Access Control Check (Angular2)
Generate All Combinations from Multiple (N) Lists
How to Set Value of Wpf Combobox Item from C# Code
Regex to Remove All Special Characters from String
Using Linq to Remove Elements from a List<T>
How to Disable Uwp App Suspension
Getting the Latest File Modified from Azure Blob
How to Refresh an Entity Framework Core Dbcontext
Generate Xml for <Govtalkmessage Xmlns="Http://Www.Govtalk.Gov.Uk/Cm/Envelope">
How to Print the Elements With Text Value That Contains in a List Selenium C#
How to Download File With ASP.NET on Buttton'S Onclick Event