sql query to linq or lambda query

Mouhssine tahri

5y798Learn iOS Programming

Hello

can you help me to transform the next query to linq or lambda:

select Site,Libellé,(select count(*) from Staxe

where Sites.Site = Staxe.Site and [Date_Comm]='28/09/2016') as NbreAppelSortant ,

(select count(*) from Staxe

where Sites.Site = Staxe.Site and [Date_Comm]='28/09/2016' and [Nature_Comm]='M') as NbreAppelSortantGsm,

(select count(*) from Staxe

where Sites.Site = Staxe.Site and [Date_Comm]='28/09/2016' and [Nature_Comm]='L') as NbreAppelSortantLocaux,

(select count(*) from Staxe

where Sites.Site = Staxe.Site and [Date_Comm]='28/09/2016' and [Nature_Comm]='N') as NbreAppelSortantNationnaux,

(select count(*) from Staxe

where Sites.Site = Staxe.Site and [Date_Comm]='28/09/2016' and [Nature_Comm]='I') as NbreAppelSortantInternationnaux

from Sites

thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Accepted answer
Additionally, there are some tools to do so.

Linqer: http://www.sqltolinq.com
LINQPad: http://www.linqpad.net/
0
Mouhssine tahri5y ago
Hello
thanks for replay
i can transform my query to linq ,i tested it in LINDPad the result is good
i integrat it in my class but i have error in return result.ToList();
public List GetAllSites()
{
var result = from cr in bd.Sites
let totalAppelSortant =
(from cr1 in bd.Staxe
where cr1.Site == cr.Site
select cr1
).Count()
let totalAppelSortantLocaux =
(from cr1 in bd.Staxe
where cr1.Nature_Comm == "L"
&& cr1.Site == cr.Site
select cr1
).Count()
let totalAppelSortantNationaux =
(from cr1 in bd.Staxe
where cr1.Nature_Comm == "N"
&& cr1.Site == cr.Site
select cr1
).Count()
let totalAppelSortantInternationaux =
(from cr1 in bd.Staxe
where cr1.Nature_Comm == "I"
&& cr1.Site == cr.Site
select cr1
).Count()
let totalAppelSortantGSM =
(from cr1 in bd.Staxe
where cr1.Nature_Comm == "M"
&& cr1.Site == cr.Site
select cr1
).Count()
select new
{
CodeSite = cr.Site,
LibelleSite = cr.Libellé,
TotalAppelSortant = totalAppelSortant,
TotalAppelSortantLocaux = totalAppelSortantLocaux,
TotalAppelSortantNationaux = totalAppelSortantNationaux,
TotalAppelSortantInternationaux = totalAppelSortantInternationaux,
TotalAppelSortantGSM = totalAppelSortantGSM
};
return result.ToList();
//return bd.Sites.ToList();
}
message error is :
0
Rijwan Ansari5y ago
HI,

It is easy to convert query to linq.
You can take advantages of .net tools linq to sql classes or entity framework code first from database approach. Check below tutorial.

https://www.c-sharpcorner.com/UploadFile/de41d6/learning-linq-made-easy-linq-to-sql-tutorial-2/
0