how i can write this SQL Expression in LINQ or Lambda?
SELECT a.PhoneId, COUNT(a.PhoneId), c.Name, cp.DDD, cp.Number
FROM (
SELECT rp.PhoneId, rp.DateSend FROM ReportSMS_Phones rp WHERE rp.CodReturned IN ('016', '015', '012', '005', 'IB:5')
AND NOT EXISTS (
SELECT rp2.PhoneId FROM ReportSMS_Phones rp2 WHERE rp2.PhoneId = rp.PhoneId AND rp2.DateSend > rp.DateSend AND rp2.CodReturned NOT IN ('016', '015', '012', '005', 'IB:5')
)
) a
INNER JOIN Clients c, Clients_Phones cp
WHERE cp.ClientId = c.Id AND cp.Id = a.PhoneId
GROUP BY a.PhoneId
var query = from rp in ( from rpAux in db.ReportSMS_Phones where rpAux.CodReturned == "016" || rpAux.CodReturned == "015" || rpAux.CodReturned == "012" || rpAux.CodReturned == "005" || rpAux.CodReturned == "IB:5" && ( from rpAux2 in db.ReportSMS_Phones where rpAux.PhoneId == rpAux2.PhoneId && rpAux2.DateSend > rpAux.DateSend && rpAux2.CodReturned != "016" && rpAux2.CodReturned != "015" && rpAux2.CodReturned != "012" && rpAux2.CodReturned != "005" && rpAux2.CodReturned != "IB:5" select new { rpAux2 } ).Count() == 0 select new { rpAux } ) group rp by rp.rpAux into g select new ReportSMS_PhonesViewModel { Phone = g.Key.Clients_Phones, Count = g.Count(r => r.rpAux.PhoneId == g.Key.PhoneId) }; return View(query.ToList());
The query is returning the correct data collection, but the grouping is not working, so I'm getting duplicate data and all Count with the value 1. Any suggestions?
I would suggest breaking your query apart using IQueryable. This would allow easier maintenance on your object query. The reason I suggest IQueryable is that you can use deferred execution to get the correct result set instead.
hyagosan
0 Points
3 Posts
Convert SQL Expression to LINQ or Lambda
Jan 08, 2013 04:40 PM|LINK
Hi,
how i can write this SQL Expression in LINQ or Lambda?
SELECT a.PhoneId, COUNT(a.PhoneId), c.Name, cp.DDD, cp.Number
FROM (
SELECT rp.PhoneId, rp.DateSend FROM ReportSMS_Phones rp WHERE rp.CodReturned IN ('016', '015', '012', '005', 'IB:5')
AND NOT EXISTS (
SELECT rp2.PhoneId FROM ReportSMS_Phones rp2 WHERE rp2.PhoneId = rp.PhoneId AND rp2.DateSend > rp.DateSend AND rp2.CodReturned NOT IN ('016', '015', '012', '005', 'IB:5')
)
) a
INNER JOIN Clients c, Clients_Phones cp
WHERE cp.ClientId = c.Id AND cp.Id = a.PhoneId
GROUP BY a.PhoneId
hyagosan
0 Points
3 Posts
Re: Convert SQL Expression to LINQ or Lambda
Jan 08, 2013 06:23 PM|LINK
I managed to do the following:
var query = from rp in
(
from rpAux in db.ReportSMS_Phones
where
rpAux.CodReturned == "016" ||
rpAux.CodReturned == "015" ||
rpAux.CodReturned == "012" ||
rpAux.CodReturned == "005" ||
rpAux.CodReturned == "IB:5"
&& (
from rpAux2 in db.ReportSMS_Phones
where
rpAux.PhoneId == rpAux2.PhoneId &&
rpAux2.DateSend > rpAux.DateSend &&
rpAux2.CodReturned != "016" &&
rpAux2.CodReturned != "015" &&
rpAux2.CodReturned != "012" &&
rpAux2.CodReturned != "005" &&
rpAux2.CodReturned != "IB:5"
select new { rpAux2 }
).Count() == 0
select new { rpAux }
)
group rp by rp.rpAux into g
select new ReportSMS_PhonesViewModel { Phone = g.Key.Clients_Phones, Count = g.Count(r => r.rpAux.PhoneId == g.Key.PhoneId) };
return View(query.ToList());
The query is returning the correct data collection, but the grouping is not working, so I'm getting duplicate data and all Count with the value 1. Any suggestions?
msmk
Participant
776 Points
158 Posts
Re: Convert SQL Expression to LINQ or Lambda
Jan 08, 2013 07:24 PM|LINK
Try 'group rp by rp.PhoneID into g'
Xequence
Contributor
4313 Points
1528 Posts
Re: Convert SQL Expression to LINQ or Lambda
Jan 08, 2013 08:19 PM|LINK
I would suggest breaking your query apart using IQueryable. This would allow easier maintenance on your object query. The reason I suggest IQueryable is that you can use deferred execution to get the correct result set instead.
http://stackoverflow.com/questions/1578778/using-iqueryable-with-linq/1578809#1578809
Credentials
hyagosan
0 Points
3 Posts
Re: Convert SQL Expression to LINQ or Lambda
Jan 09, 2013 12:19 PM|LINK
As I could not go further than this in code, did the "adaptation" below:
var query = from rp in
(
from rpAux in db.ReportSMS_Phones
where
rpAux.CodReturned == "016" ||
rpAux.CodReturned == "015" ||
rpAux.CodReturned == "012" ||
rpAux.CodReturned == "005" ||
rpAux.CodReturned == "IB:5"
&& (
from rpAux2 in db.ReportSMS_Phones
where
rpAux.PhoneId == rpAux2.PhoneId &&
rpAux2.DateSend > rpAux.DateSend &&
rpAux2.CodReturned != "016" &&
rpAux2.CodReturned != "015" &&
rpAux2.CodReturned != "012" &&
rpAux2.CodReturned != "005" &&
rpAux2.CodReturned != "IB:5"
select new { rpAux2 }
).Count() == 0
select new { rpAux }
)
group rp by rp.rpAux into g
select new ReportSMS_PhonesViewModel { Phone = g.Key.Clients_Phones, Count = g.Count(r => r.rpAux.PhoneId == g.Key.PhoneId) };
List<ReportSMS_PhonesViewModel> reports = new List<ReportSMS_PhonesViewModel>();
foreach (var rp in query.ToList())
{
ReportSMS_PhonesViewModel report = reports.FirstOrDefault(r => r.Phone.Id == rp.Phone.Id);
if (report == null)
{
reports.Add(rp);
}
else
{
rp.Count = report.Count + 1;
reports.Remove(report);
reports.Add(rp);
}
}
return View(reports);
Xequence
Contributor
4313 Points
1528 Posts
Re: Convert SQL Expression to LINQ or Lambda
Jan 11, 2013 04:55 PM|LINK
you could use .contains instead of the conditional or because it will not evaluate all conditions if first one is true...
http://msdn.microsoft.com/en-us/library/6373h346.aspx
http://stackoverflow.com/questions/11194/conditional-linq-queries
Credentials