Converting SQL to LINQ using MAX

Question

I have two tables

1) T_EJV_CREDIT_DS_INDEX
2) T_EJV_CREDIT_DS_INDEX_CONTRACT

enter image description here

I would like a SQL query like below as a LINQ expression

SELECT MAX(INDEX_FAMILY_VERSION) FROM T_EJV_CREDIT_DS_INDEX cdi
INNER JOIN T_EJV_CREDIT_DS_INDEX_CONTRACT cdic
ON cdic.INDEX_ID = cdi.INDEX_ID
WHERE cdi.INDEX_SHORT_NAME LIKE '%@VARIABLE1%'
AND cdic.TENOR = @VARIABLE2

This is what I have attempted so far

var maxFamilyVersion = (from ic in dsIndexContract
                       join i in dsIndex on i.INDEX_ID equals ic.INDEX_ID
                       where i.INDEX_SHORT_NAME.CONTAINS(strindex) && ic.TENOR equals d.TERM 
                       select new
                       {
                           ic.INDEX_FAMILY_VERSION.Max()     
                       }).Take(1).ToList();

But the above mentioned starts showing compile issues with the syantax as shown below

enter image description here

the nice thing about visual studio is it puts a red squiggle where the problem is. — Hogan
– Hogan, Commented Mar 3, 2015 at 15:06

Loetn · Accepted Answer · 2015-03-03 17:16:15Z

2

Checking for equality in your where condition can be done with ==. The keyword equals is only used in a join condition.

  var result = (from ic in dsIndexContract
                join i in dsIndex on i.INDEX_ID equals ic.INDEX_ID
                where i.INDEX_SHORT_NAME.CONTAINS(strindex) && ic.TENOR == d.TERM 
                select new
                {
                    ic.INDEX_FAMILY_VERSION.Max()     
                }).FirstOrDefault();

And instead of .Take(1).ToList(), you can use .FirstOrDefault() to retrieve the first item.

Or a more efficient way is to use .Max() directly instead of .FirstOrDefault():

  var result = (from ic in dsIndexContract
                    join i in dsIndex on i.INDEX_ID equals ic.INDEX_ID
                    where i.INDEX_SHORT_NAME.CONTAINS(strindex) && ic.TENOR == d.TERM 
                    select ic.INDEX_FAMILY_VERSION).Max();

edited Mar 3, 2015 at 17:16

answered Mar 3, 2015 at 15:04

Loetn

4,0901 gold badge27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Asif Over a year ago

var result = (from ic in dsIndexContract join i in dsIndex on ic.INDEX_ID equals i.INDEX_ID where i.INDEX_SHORT_NAME.Contains(strindex) && ic.TENOR == d.TERM select i.INDEX_FAMILY_VERSION).Max();

Magnus Over a year ago

@Loetn You can do Max on a column like ic.INDEX_FAMILY_VERSION.Max() since it is not a collection.

Loetn Over a year ago

@Magnus Yeah, I know, my bad. I will change it.

Magnus · Accepted Answer · 2015-03-03 15:14:35Z

1

This should do it:

var maxFamilyVersion = 
                 (from ic in dsIndexContract
                 join i in dsIndex on ic.INDEX_ID equals i.INDEX_ID
                 where i.INDEX_SHORT_NAME.CONTAINS(strindex) && ic.TENOR == d.TERM 
                 select ic.INDEX_FAMILY_VERSION).Max();

answered Mar 3, 2015 at 15:14

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

1 Comment

Asif Over a year ago

var maxFamilyVersion = (from ic in dsIndexContract join i in dsIndex on ic.INDEX_ID equals i.INDEX_ID where i.INDEX_SHORT_NAME.Contains(strindex) && ic.TENOR == d.TERM select i.INDEX_FAMILY_VERSION).Max(); Did a tweak and it started working thanks

Collectives™ on Stack Overflow

Converting SQL to LINQ using MAX

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related