Today I was asked a question by a developer on my team. The question is “How do you select MAX value for a column in a table with LINQ To SQL?”. I will try to answer the question in this post with an example. Let’s say that we want to retrieve maximum unit price from Products table in Northwind database. In T-SQL such a query can be written like this.
SELECT MAX(UnitPrice) FROM products
Query above produces the correct result.
In LINQ To SQL we can write the following query
(from p in Products select (p.UnitPrice)).Max()
which will get translated into this T-SQL.
Other than MAX we can also use other aggregates such as MIN, SUM etc..
12 Responses to Select Max Value With LINQ To SQL
Leave a Reply
Connect with me
-
I a trying to write the following query in linq, t_sql
select colId,
colTaskType,
MaxID
from tblTaskType
join (
select tblCheckList.colTaskTypeID,
max(colItemNumber) MaxID
from tblCheckList
group by colTaskTypeID
) x on coltaskTypeID = tblTaskType.colID
Please help..
Hi Shirley,
Can you please post your create table statements? I’ll then be able to help you out.
Exception raised:
The method ‘Max’ is not supported.
public int GetLastPrintCheckID()
{
try
{
var printCheck = (from o in _context.PrintChecks
select o.PrintCheckID).Max();
return printCheck;
}
catch (Exception exx)
{
return 0;
}
}
With the above exception, the workaround was as follows:
var query = _context.Execute(new Uri(string.Format(“{0}/PrintChecks?$orderby=PrintCheckID desc&$top=1&$select=PrintCheckID”, _context.BaseUri), UriKind.RelativeOrAbsolute));
var printCheck = query.FirstOrDefault();
James,
Max is supported in LINQ To SQL queries. I ran the query you posted on a sample database and it worked.
Nice tip, I used not to use LINQ in my code wherever I can, but I think that there’re times when you MUST use it. Selecting max values for me without T-SQL means to iterate through all the rows to get the max value. :(
Thanks for your help! :)
Solution:
var q = (from data in db.Tickets.Where(p => (p.DataInsercao).Year == System.DateTime.Now.Year).OrderByDescending(p => p.Ticket)
select data).Take(1);
foreach (var v in q)
{
//Response.Write(v.Ticket.ToString());
LabelTicket.Text = v.Ticket.ToString();
}
Sorry:
var q = (from data in db.Tickets.Where(p => (p.DataInsercao).Year == System.DateTime.Now.Year).OrderByDescending(p => p.Ticket)
select new {data.Ticket }).Take(1);
foreach (var v in q)
{
LabelTicket.Text = (v.Ticket).ToString();
}
Thanks you so much :)
Most welcome el Khatib.
thk for sample
Thank you very much