How Do I Convert This Sql Query Into Linq?

Question

0.00/5 (No votes)

See more:

Hello to all,

I am learning to convert LINQ all the SQL queries . But would I convert this complex SQL how can I? I arrived at one point and I do not know how to do with the group by having ... could you please give me a hand ?

Here is the SQL query ORIGINAL :

SQL

SELECT DISTINCT MIN(nomeFileVeicolo) AS nomeFileVeicolo, porte, body, nomeBody, numModello, modelloDa,modelloA,specie 
FROM DataBaseCar 
GROUP BY Class_ID,idMarca, nomeModello, porte, body, nomeBody, numModello, modelloDa,modelloA,specie 
HAVING (Class_ID = 1) AND (idMarca = '6') AND (nomeModello = '2-Serie') AND (specie = 'CAR') 
ORDER BY modelloDa DESC

My LINQ is this but I'm stuck :

C#

var veicoli = from db in contestoDB.DataBaseCar
                                  group db by new {db.Class_ID,  db.idMarca,  db.nomeModello,  db.porte,  db.body,  db.nomeBody,  db.numeroModello,  db.modelloDa,  db.modelloA,  db.specie} into g
                                  where g.Single().Class_ID == 1
                                  where g.Single().idmarca== marca
                                  where g.Single().nomeModello== modello
                                  where g.Single().specie== tipo
                                  orderby db.

/* marca , tipo, modello input parameters!! */

How is it done?

I await news .

Thank you

Cris

Posted 12-Jan-16 21:42pm

Cristian Capannini

Add a Solution

2 solutions

Solution 2

use tool linqpad[^] which is free.
you can also use Linqer which is a SQL to LINQ conversion tool. It helps learning LINQ and convert existing SQL statements.

Posted 12-Jan-16 22:46pm

suhel_khan

Comments

Cristian Capannini 13-Jan-16 4:54am

Tried linqpad not convert SQL to LINQ. And Linqer trial not working!!! Time is always expired!!!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

*dan!sh* · Accepted Answer · 2016-01-12T22:31:00

Does this do what you need:

C#

contestoDB.DataBaseCar.Where(x =>    x.Class_ID == 1 &&
                                     x.idMarca == "6" &&
                                     x.nomeModello == "2-Serie" &&
                                     x.specie == "CAR") 
                     .GroupBy(x => new { x.Class_ID, 
                                         x.idMarca, 
                                         x.numModello, 
                                         x.porte, 
                                         x.body, 
                                         x.nomeBody, 
                                         x.modelloDa, 
                                         x.modelloA, 
                                         x.specie }) 
                     .Select(x => x.OrderBy(y => y.nomeFileVeicolo))
                     .Select(x => x.First())
                     .OrderByDescending(x => x.modelloDa).ToList();

There is no need to perform a distinct since you have already used group by for all the columns in select.