Filter on Where the longest date with LINQ

Question

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

Answer 1

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.

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