CODEWITHSUNDEEP
c#sqllinqmax
Asif
Asif

Reputation: 393

Converting SQL to LINQ using MAX

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

Upvotes: 0

Views: 50

Answers (2)

Loetn
Loetn

Reputation: 4040

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();

Upvotes: 2

Magnus
Magnus

Reputation: 46919

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();

Upvotes: 1

Related Questions