Convert this longest sql query in linq using C#?

Question

1.00/5 (1 vote)

See more:

Is there someone help me convert it into LINQ?

SQL

SELECT DISTINCT TBCAR1.MAKE_ID, TBCAR1.Species, TBCAR1.ModelName, TBCAR1.ModelNum, TBCAR1.Body, TBCAR1.ModelFrom, TBCAR1.ModelTo, TBCAR1.Doors,TBCAR1.VersionName, TBCAR1.CodeList, TBCAR1.BodyName, TBCAR1.ModelYear, TBCAR1.Fuel, TBCAR1.Kw, TBCAR1.Cyli, TBCAR1.contOE,TBCAR1.Body_ID, TBCAR1.TYPE_ID, TBCAR1.vehicleFileName, MIN(TBCAR3.ClearEngine) AS CodMotore, ISNULL(TBCAR1.codeType, TBCAR1.codeRange) AS CodVersione, TBCAR1.ccmTech, TBCAR1.nOE, TBCAR1.Engine, TBCAR1.codeType, TBCAR1.Volume, LEFT(TBCAR1.Build_From, 4) AS AnnoDal,RIGHT(TBCAR1.Build_From, 2) AS MeseDal, LEFT(TBCAR1.Build_To, 4) AS AnnoAl, RIGHT(TBCAR1.Build_To, 2) AS MeseAl,TBCAR3.ClearEngine
 
FROM TBCAR1 LEFT OUTER JOIN TBCAR2 ON TBCAR1.TYPE_ID = TBCAR2.TypeID AND TBCAR1.Body_ID = TBCAR2.BodyID LEFT OUTER JOIN TBCAR3 INNER JOIN TBCAR4 ON TBCAR3.CTYPE_ID = TBCAR4.ComponentTypeID ON TBCAR1.TYPE_ID = TBCAR4.TypeID
  
GROUP BY TBCAR1.Build_From, TBCAR1.Build_To, TBCAR1.MAKE_ID, TBCAR1.Species, TBCAR1.ModelName, TBCAR1.ModelNum, TBCAR1.Body,TBCAR1.ModelFrom, TBCAR1.ModelTo, TBCAR1.Doors, TBCAR1.VersionName, TBCAR1.CodeList, TBCAR1.BodyName, TBCAR1.ModelYear,TBCAR1.Fuel, TBCAR1.Kw, TBCAR1.Cyli, TBCAR1.contOE, TBCAR1.Body_ID, TBCAR1.TYPE_ID, TBCAR1.vehicleFileName, TBCAR1.ccmTech,TBCAR1.nOE, TBCAR1.Engine, ISNULL(TBCAR1.codeType,TBCAR1.codeRange), TBCAR1.codeType, TBCAR1.Volume, TBCAR3.ClearEngine
 
HAVING (TBCAR1.MAKE_ID = 3) AND (TBCAR1.ModelNum = '8PA') AND (TBCAR1.ModelName = 'A3') AND (TBCAR1.Body = 'BER') AND (TBCAR1.Doors = 5) AND (TBCAR1.BodyName = 'Sportback')  AND (TBCAR1. Species = 'CAR')
  
ORDER BY TBCAR1.ccmTech, TBCAR1.Kw

Or is there an alternative without converting SQL but using datacontext objects?
Thanks
Cris

Posted 14-Jan-16 3:40am

Cristo1985

Add a Solution

Comments

johannesnestler 14-Jan-16 10:19am

Before this question can be answered, just tell us what QueryProvider you want to use. (e.g. OR-Mappers are queryproviders for LINQ). You mentioned DataContext - so I'd assume you mean EntityFramework - but .NET 3.5? Wich version of EF are you using? My opinion? EF + LINQ is great, I don't miss such queries in my code, no SQL anymore...

Cristo1985 14-Jan-16 11:17am

Yes i'm using this DataBaseContext:

using (DataBaseContext contestoDB = new DataBaseContext()) { }

Ehm sorry for late answering!

I'm very busy....and

Thanks if you can answer may question.

Cris

ZurdoDev 14-Jan-16 13:48pm

1. Reply to the comment so that the user is notified.
2. There may be someone who takes the time to do this for you, but don't you think it's pretty rude to ask someone to convert all of this sql to LINQ for you? Shouldn't you at least get started and then ask if you get stuck on something specific?

1 solution

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)

Maciej Los · Answer 1 · 2016-01-14T08:22:00

Solution 2

The easiest way is to use specific tool, such as Linqer, LinqPad, etc. See: SQL to LINQ Tool - Stack Overflow[^]

On the other side, you should do - at least - minimum effort to find solution by studying 101 LINQ Samples in C#[^]

Posted 14-Jan-16 8:22am

Maciej Los

Comments

Jörgen Andersson 14-Jan-16 15:44pm

I prefer the 101 Linq Samples Lambda style[^] :-)

Maciej Los 14-Jan-16 15:51pm

Me too, but when someone is a beginner, lambda might be too hard to understand.

Jörgen Andersson 14-Jan-16 15:58pm

One reason I prefer method syntax to query syntax is because it doesn't look like SQL.
As a beginner you might get confused into expecting it to work like SQL, which it certainly does not. (Well, mostly)

Maciej Los 14-Jan-16 16:07pm

A master said: even if something look like the other thing, it's not this thing.