0

I develop a web app with C# and I want to display these SQL queries in Swagger. So I want to convert them to Linq.

First SQL query:

 SELECT TOP 3 HastalikIsmi, COUNT(*) 
 FROM Hastaliklar 
 GROUP BY HastalikIsmi 
 ORDER BY COUNT(*) DESC

Second SQL query:

SELECT TcNo, Isim, Soyisim, Hastaliklar.HastalikIsmi 
FROM Calisanlar, Hastaliklar 
WHERE Calisanlar.CalisanId = Hastaliklar.CalisanId 
  AND HastalikIsmi IN (SELECT TOP 3 HastalikIsmi 
                       FROM Hastaliklar 
                       GROUP BY HastalikIsmi  
                       ORDER BY COUNT(*) DESC)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
habudaney
  • 9
  • 5
  • Can you provide sample data and show what you have tried so far. – Peter Smith Dec 18 '21 at 10:01
  • i didin't find sample TOP 3 and Count(*) for linq. i'm sorry – habudaney Dec 18 '21 at 10:06
  • Look [here](https://learn.microsoft.com/en-us/samples/dotnet/try-samples/101-linq-samples/) for sample `linq` queries. They might help you. – Peter Smith Dec 18 '21 at 10:11
  • 1
    What you have tried so far? Show your attempts and Model. LINQ queries are built from classes. – Svyatoslav Danyliv Dec 18 '21 at 10:19
  • i didn't tried because i didn't find any sample about Count(*) and TOP 3 @SvyatoslavDanyliv – habudaney Dec 18 '21 at 10:29
  • 1
    It is really hard to do not find anything about that. `count = query.Count()`, `limited = query.Take(3)` – Svyatoslav Danyliv Dec 18 '21 at 10:49
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged – marc_s Dec 18 '21 at 10:50
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Dec 23 '21 at 00:40
  • Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (probably NOT linq-to-sql), and show your own first efforts so we see where *specifically* you need help. The best LINQ query is hardly ever a 1:1 reproduction of a SQL query. – Gert Arnold Mar 20 '22 at 09:31

1 Answers1

0

I barely work with raw SQL since I use EF most of the time (so I might have misinterpreted some of the commands) and neither did I have the time to test the second query so I appologize in advance if there are any mistakes.

First SQL query translated:

var result = hastaliklars.GroupBy(hastaliklar => hastaliklar.HastalikIsmi)
            .Select(hastaliklarGroup => 
                (HastalikIsmi: hastaliklarGroup.Key, Count: hastaliklarGroup.Count()))
            .OrderByDescending(hastalikIsmiWithCount => hastalikIsmiWithCount.Count)
            .Take(3)
            .ToList();

Second SQL query translated:

var result2 = calisanlars.Join(hastaliklars,
                calisanlar => calisanlar.CalisanId,
                hastaliklar => hastaliklar.CalisanId,
                (calisanlar, hastaliklar) =>
                    (Calisanlar: calisanlar, Hastaliklar: hastaliklar))
            .Where(calisanlarAndHastaliklar => hastaliklars
                .GroupBy(hastaliklar => hastaliklar.HastalikIsmi)
                .OrderByDescending(hastaliklarGroup => hastaliklarGroup.Count())
                .Take(3)
                .Any(hastaliklarGroup =>
                    calisanlarAndHastaliklar.Hastaliklar.HastalikIsmi == hastaliklarGroup.Key))
            .ToList();

Note that if you're working with an IQueryable<T> instead of an IEnumerable<T> you might get a runtime exception stating that the LINQ expression could not be translated and will be evaluated locally. The reason why this might happen is because an IQueryable<T> is not run locally on C# but rather just contains instructions on what you're trying to do and translates that into an SQL query string. Therefore, specific code can not be translated into SQL which causes this exception.

The easiest way to fix this would be to run the method AsEnumerable() before the method which can not be translated to SQL which forces only all of the previous instructions to be run via SQL and then reads and works with the results locally from there on out. However, it does come at a performance cost, so that it would be slower than running the entire query via SQL. Therefore the optimal solution would be to adjust the code so that all of it could be translated into SQL if possible.

I don't fully remember what code IQueryable<T> supports and what not but as a good start; Here's a link to the microsoft docs that lists all of the LinQ functions it supports: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/supported-and-unsupported-linq-methods-linq-to-entities?redirectedfrom=MSDN

Despacito 2
  • 444
  • 2
  • 10