How to convert SQL query to linq C# ?

Question

0.00/5 (No votes)

See more:

C#

select distinct Sirket.SirketAdi,
(Select Miktar from KasaIslem where Ay=1 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015) as 'Ocak',
(Select Miktar from KasaIslem where Ay=2 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Şubat',
(Select Miktar from KasaIslem where Ay=3 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Mart',
(Select Miktar from KasaIslem where Ay=4 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Nisan',
(Select Miktar from KasaIslem where Ay=5 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Mayıs',
(Select Miktar from KasaIslem where Ay=6 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Haziran',
(Select Miktar from KasaIslem where Ay=7 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Temmuz',
(Select Miktar from KasaIslem where Ay=8 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Ağustos',
(Select Miktar from KasaIslem where Ay=9 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Eylül',
(Select Miktar from KasaIslem where Ay=10 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Ekim',
(Select Miktar from KasaIslem where Ay=11 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Kasım',
(Select Miktar from KasaIslem where Ay=12 and KasaIslem.SirketID=Sirket.ID and KasaIslem.Yil=2015)as 'Aralık' from KasaIslem 
inner join Sirket on KasaIslem.SirketID=Sirket.ID
inner join KasaTur on KasaIslem.KasaTurID=KasaTur.ID
where KasaTur.ID=1 and yil=2015<pre lang="C#">

What I have tried:

HI All, 
i have one sql query which i need to rewrite in LINQ?


Can someone please help me ?

Posted 29-Apr-16 13:11pm

Member 11946716

Updated 5-May-16 21:30pm

Santosh Kokatnur

v2

Add a Solution

Comments

Beginner Luck 29-Apr-16 22:03pm

you want sql query as linq or you want sql to linq convert

Member 11946716 29-Apr-16 23:15pm

I want to sql query as lnq

2 solutions

Solution 2

/*i tried to this linq query but dont work distinct */

C#

var sonuc = (from kasaIslem in _db.KasaIslemleri
                        join sirket in _db.Sirketler on kasaIslem.SirketID equals sirket.ID
                        join kasaTur in _db.KasaTurleri on kasaIslem.KasaTurID equals kasaTur.ID
                        where kasaTur.ID == 1 && kasaIslem.Yil == Year

                        select new
                        {
                            sirket.SirketAdi,
                            Ocak = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 1 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Şubat = _db.KasaIslemleri.Where(x =>x.SirketID == sirket.ID && x.Ay == 2 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Mart = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 3 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Nisan = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 4 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Mayıs = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 5 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Haziran = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 6 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Temmuz = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 7 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Ağustos = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 8 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Eylül = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 9 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Ekim = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 10 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Kasım = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 11 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault(),
                            Aralık = _db.KasaIslemleri.Where(x => x.SirketID == sirket.ID && x.Ay == 12 && x.Yil == Year).Select(x => x.Miktar).FirstOrDefault()
                        }
                    );

           return sonuc;

Posted 30-Apr-16 2:29am

Member 11946716

Comments

Garth J Lancaster 30-Apr-16 21:42pm

what if you remove all the .FirstOrDefault() from within the select new {} section, and instead of

var sonuc

you put

var tempDB

... then, do a 2nd LINQ Query to select distinct on the tempDB ?

Great work by the way

Add a Solution

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

Garth J Lancaster · Accepted Answer · 2016-04-29T17:54:00

I would have written this in a number of steps (I'll be delighted to see if someone answers and does it all on one Linq statement, Im quite happy to extend my knowledge)

Step 1

First Linq Query
a) return a distinct list
b) of an anonymous object (or a class if you really want), containing values for Sirket.SirketAdi, Miktar, and Ay
c) with the joins etc as you've specified
d) one of your 'where' clauses is where Ay >= 1 && Ay <= 12

that should give you 12 'objects' with {Sirket.SirketAdi,Miktar,Ay} - ‘collapsing’ the query a little as per ‘d’ makes it more manageable (imho)

Step 2

From the First Linq Query I'd then ? project Ay -> "Ocak"/"Şubat"/"Mart"/"Nisan"/... and maybe return a dictionary of tuples so that you have

["Ocak" -> tuple<sirket.sirketadi,Value of Miktar for "Ocak">]
["Şubat" -> tuple<sirket.sirketadi,Value of Miktar for "Subat">]
…

(Possible) Step 3

I don’t speak Turkish, but Im guessing Ocak, Şubat, … , Aralık are month names, so indexing by Month name may make sense.

Since I don’t know what Sirket.SirketAdi is, I don’t know if its value changes of is static - hence the tuple - if its static, you could in effect use a 3rd linq query to get the value from the first result and not worry about the tuple

How to convert SQL query to linq C# ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to convert SQL query to linq C# ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us