Filter on Where the longest date with LINQ

Asked

Viewed 954 times

3

As I pass the Where of this max datatransaction, ie I wish to bring the result but by the highest date.

var resultado = (from ci in webDB.T_Controle_Importacao
                             where ci.CNPJ == cnpj
                             let dd = EntityFunctions.DiffDays(DateTime.Now, ci.DataTransacao)
                             select dd >= 45 ? 3
                                  : dd >= 15 ? 2
                                  : 1).Take(1);
asp.net-mvc-5 linq lambda-expressions

2 answers

2


You can sort the query so that the object with the highest date appears in front, and then take the first record:

var resultado = (from ci in webDB.T_Controle_Importacao
                                 .OrderByDescending(ci => ci.DataTransacao)
                 where ci.CNPJ == cnpj
                 let dd = EntityFunctions.DiffDays(DateTime.Now, ci.DataTransacao)
                 select dd >= 45 ? 3
                      : dd >= 15 ? 2
                      :            1
                )
                .Take(1);

Note that when doing this, it is recommended that you have an index in the column that stores the date in the database, so that the ordering doesn’t get too slow.

2

Order to bring the highest date with orderby field descending

var resultado = (from ci in webDB.T_Controle_Importacao
                    where ci.CNPJ == cnpj
                    let dd = EntityFunctions.DiffDays(DateTime.Now, ci.DataTransacao)
                    orderby ci.DataTransacao descending
                    select dd >= 45 ? 3 : dd >= 15 ? 2 : 1).Take(1);

Browser other questions tagged asp.net-mvc-5 linq lambda-expressions

You are not signed in. Login or sign up in order to post.