Click here to Skip to main content
15,446,232 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.
 
Share this answer
 
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     CRLF
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