如何将SQL查询转换为linq C＃？ [英] How to convert SQL query to linq 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 ?

推荐答案

我会用一个数字写这个步骤（我会很高兴看到有人回答并在一个Linq声明中做到这一切，我很乐意扩展我的知识）



第1步



第一个Linq查询

a）返回一个匿名对象的明确列表

b）（如果你真的是一个类想要），包含Sirket.SirketAdi，Miktar和Ay

c）的值，你已经指定

d）其中一个'where'子句在哪里Ay> = 1 && Ay< = 12



应该给你12个'对象'与{Sirket.SirketAdi，Miktar，Ay} - '崩溃'根据'd'查询一点使其更易于管理（imho）



步骤2



来自那时我的第一个Linq查询？项目Ay - > Ocak/Şubat/Mart/Nisan/ ...并且可能会返回一个元组字典，以便你有



[Ocak - > tuple< sirket.sirketadi，Miktar对Ocak的价值>]

[Şubat - > tuple< sirket.sirketadi，Miktar对于Subat的价值>]

...



（可能）第3步



我不会说土耳其语，但我猜Ocak，Şubat，...，Aralık是月份名称，因此按月名称索引可能有意义。



因为我不知道Sirket.SirketAdi是什么，我不知道它的值变化是否是静态的 - 因此元组 - 如果它是静态的，你实际上可以使用第三个linq查询从第一个结果中获取值而不用担心元组

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

/ *我尝试了这个linq查询但是没有工作区别* /

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

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;

这篇关于如何将SQL查询转换为linq C＃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！