Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would like to know what is the correct way to convert this query to LINQ.
SQL
SELECT  DISTINCT ( CONVERT(date,FechaCreacion)) FROM Tabla
    where ID = 4
    order by CONVERT(date,FechaCreacion) desc 
    OFFSET     (0 * 20) ROWS    
    FETCH NEXT 20 ROWS ONLY  ;


With this query it brings me this data:

A1. 2021-03-16
 2. 2021-03-15
 3. 2021-03-14
 4. 2021-03-13
 5. 2021-03-11
 6. 2021-03-09
 7. 2021-03-02
 8. 2021-02-28
 9. 2021-02-25
 10. 2021-02-24
 11. 2021-02-23
 12. 2021-02-22
 13. 2021-02-21
 14. 2021-02-19
 15. 2021-02-10
 16. 2020-11-30
 17. 2020-10-05
 18. 2020-02-18


What I have tried:

LINQ:

C#
var query = (from sp in esquema.Tabla
                                 where sp.ID== 4
                                 orderby DbFunctions.TruncateTime(sp.FechaCreacion) descending
                                 select new
                                 {
                                    fechacreacion = DbFunctions.TruncateTime(sp.FechaCreacion)
                                 }
                                 ).Skip(0* 20).Take(20).ToList().Distinct();

With linq it brings me less data than the query made directly in sqlserver
1. 2021-03-16
2. 2021-03-15
3. 2021-03-14
4. 2021-03-13
5. 2021-03-11
6. 2021-03-09
7. 2021-03-02
8. 2021-02-28
9. 2021-02-25
10. 2021-02-24
11. 2021-02-23
Posted
Updated 15-Mar-21 10:47am
v2

1 solution

Replace this:
C#
).Skip(0* 20).Take(20).ToList().Distinct();

with:
C#
).Distinct().Take(20).ToList();


Why?
The first statement gets exactly 20 "rows", but some of them are duplicated. So, Distinct() method at the end of statement removes that duplicates.
   
v4
Comments
Richard Deeming 16-Mar-21 5:44am
   
Surely it would be better to move the Distinct before the Skip / Take calls? By calling ToList, you're loading the entire table into memory.
Maciej Los 16-Mar-21 5:49am
   
Ooops... It wasn't my intention to load entire table into memory, but only those 20 records.
Thanks for pointing me that out.
Jhon Daniel Guzman 25-Mar-21 1:21am
   
Thank you very much, that was the error I had
Maciej Los 25-Mar-21 3:12am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900