I have a SQL query and I want to convert it to linq-CodePudding

I have a SQL query which I want to convert to Linq.

This is my SQL query:

SELECT
    Calisanlar.CalisanId,
    CovidYakalanmaTarih, 
    CovidBitisTarih 
FROM
    Calisanlar 
INNER JOIN 
    Covids ON Calisanlar.CalisanId = Covids.CalisanId 
WHERE
    Calisanlar.CalisanId IN (SELECT TOP 10 CalisanId  
                             FROM Hastaliklar 
                             GROUP BY CalisanId 
                             ORDER BY COUNT(*) DESC)
    AND DATEDIFF(DAY, CovidYakalanmaTarih, GETDATE()) BETWEEN 0 AND 30

I wrote this C# code, but it doesn't work as expected because i didn't write "DATEDIFF(DAY, CovidYakalanmaTarih, GETDATE()) BETWEEN 0 AND 30" linq version:

var query = context.Hastaliklar
                   .GroupBy(x => x.CalisanId)
                   .OrderByDescending(grp => grp.Count())
                   .Select(grp => grp.Key)
                   .Take(10)
                   .ToList();

var result = from hastalik in context.Hastaliklar
             join covid in context.Covids
                  on hastalik.CalisanId equals covid.CalisanId
             where query.Contains(hastalik.CalisanId) 
                   && EF.Functions.DateDiffDay(covid.CovidYakalanmaTarih, covid.CovidBitisTarih)
             select new SonBirAyCovidDto
                        {
                            CalisanId = covid.CalisanId,
                            CovidYakalanmaTarih = covid.CovidYakalanmaTarih,
                            CovidBitisTarih = covid.CovidBitisTarih
                        };

CodePudding user response：

There is not direct translation to BETWEEN in EF Core, but you can make other condition. Also it is better to remove ToList() from first query, in this case you will have only one roundtrip to database.

var query = context.Hastaliklar
    .GroupBy(x => x.CalisanId)
    .OrderByDescending(grp => grp.Count())
    .Select(grp => grp.Key)
    .Take(10);

var result = 
    from hastalik in context.Hastaliklar
    join covid in context.Covids
        on hastalik.CalisanId equals covid.CalisanId
    where query.Contains(hastalik.CalisanId) 
        && covid.CovidYakalanmaTarih <= covid.CovidBitisTarih
        && EF.Functions.DateDiffDay(covid.CovidYakalanmaTarih, covid.CovidBitisTarih) <= 30
    select new SonBirAyCovidDto
    {
        CalisanId = covid.CalisanId,
        CovidYakalanmaTarih = covid.CovidYakalanmaTarih,
        CovidBitisTarih = covid.CovidBitisTarih
    };