MAX in LINQ to EF

Question

Post reply

MAX in LINQ to EF

Ray K

SSC-Dedicated

Points: 31163
More actions
November 23, 2010 at 1:28 pm

#388293

Does anyone know the LINQ to Entity Framework syntax equivalent of a MAX in SQL (e.g. "select MAX([some INT]) from [some table]")?
I tried to look this up, and, strangely, couldn't find a good answer.
Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Does this link answer your question:

http://msdn.microsoft.com/en-us/library/cc716738.aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Jack Corbett (11/23/2010)
Does this link answer your question:
http://msdn.microsoft.com/en-us/library/cc716738.aspx

Yes and no. I was hoping there'd be an easy way to do this (something like "(m => max(m.GetID))", for example), but I guess that was wishful thinking on my part. Guess I'll have to mess around with this for a bit.

Thanks for your help, Jack. (Glad to hear you're feeling better, BTW.)

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Wish I could have done better for you. I haven't really looked at EF that much, more Linq to SQL. Aggregation is definitely one of the areas the ORM tools (at least EF and L2S) seem to fall short.

I know I've faced some frustrations with L2S where I could have had it done in an SP and called using ADO.NET in 20 minutes and took a full day to get working with L2S.

Do you have LINQPad[/url]? You should if you are doing Linq or EF.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

It's really not that difficult..here is an example of getting a maximum ArticleNumber for each groupName in my articles entity...

var groups = from a in Articles

group a by a.GroupName into g

select new { GroupName = g.Key, _

MaximumArticle = g.Max(a => a.ArticleNumber) };

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

And here is how to just get the max from an entity

var mm = Articles.Max(w => w.ArticleNumber );

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

mister.magoo (11/23/2010)
It's really not that difficult..here is an example of getting a maximum ArticleNumber for each groupName in my articles entity...
var groups = from a in Articles
group a by a.GroupName into g
select new { GroupName = g.Key, _
MaximumArticle = g.Max(a => a.ArticleNumber) };

Just to play devil's advocate here, but now you are using an anonymous type which partially defeats the purpose of an ORM tool, strong typing.

Also, for a person with a T-SQL background writing:

Select

ColA,

Max(ColB) as maxColB

From

Table

Group By

ColA

in an SP and calling the SP is easier.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Jack Corbett (11/23/2010)
mister.magoo (11/23/2010)
It's really not that difficult..here is an example of getting a maximum ArticleNumber for each groupName in my articles entity...
var groups = from a in Articles
group a by a.GroupName into g
select new { GroupName = g.Key, _
MaximumArticle = g.Max(a => a.ArticleNumber) };
Just to play devil's advocate here, but now you are using an anonymous type which partially defeats the purpose of an ORM tool, strong typing.

Yep. Just showing how it can be done, not saying I do it.(I never have)

Also, for a person with a T-SQL background writing:
Select
ColA,
Max(ColB) as maxColB
From
Table
Group By
ColA
in an SP and calling the SP is easier.

Agreed - but - just being Devil's Advocate's Advocate here - that only applies if the data source behind all the nice strongly typed entities is SQL...;-)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ray K SSC-Dedicated Points: 31163 More actions · Answer 8

mister.magoo (11/23/2010)
And here is how to just get the max from an entity
var mm = Articles.Max(w => w.ArticleNumber );

This looks closer to the syntax I was hoping to find. Let me mess around with this a bit and see what happens.

Thanks for the assist, guys!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

mister.magoo (11/23/2010)
Agreed - but - just being Devil's Advocate's Advocate here - that only applies if the data source behind all the nice strongly typed entities is SQL...;-)

True, but since this is a SQLServerCentral I felt safe in assuming that the backend is some type of RDBMS...:-P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

Jack Corbett (11/29/2010)
mister.magoo (11/23/2010)
Agreed - but - just being Devil's Advocate's Advocate here - that only applies if the data source behind all the nice strongly typed entities is SQL...;-)
True, but since this is a SQLServerCentral I felt safe in assuming that the backend is some type of RDBMS...:-P

Fair point! 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);