select c.Name, d.First_Name, COUNT(c.Name) as qty
from order_product_s a
inner join Order_s b on a.Order_Id = b.Id
inner join Product_s c on a.Product_Id = c.Id
inner join Customer_s d on b.Customer_Id = d.Id
where b.Customer_Id = 4869
group by c.Name, d.First_Name
-
2Sorry, but SE is not a code writing service. (We are here to help you writing your code, not to write the code for you.)jarlh– jarlh2015-09-03 09:44:33 +00:00Commented Sep 3, 2015 at 9:44
-
Welcome to Stack Overflow! Please never just dump SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts. They clarify more to us than you might think.Gert Arnold– Gert Arnold2015-09-03 09:46:52 +00:00Commented Sep 3, 2015 at 9:46
Add a comment
|
1 Answer
Something like this:
int __UserId = 4869;
var results =
(
from t in
(
from a in Repo.order_product_s
from b in Repo.Order_s
.Where(bb=> bb.id == a.Order_Id)
from c in Repo.Product_s
.Where(cc => cc.Id == a.Product_Id)
from d in Repo.Customer_s
.Where(dd => dd.Id == b.Customer_Id)
where b.Customer_Id == __UserId
select new
{
Name = c.Name
,First_Name = d.First_Name
}
)
group t by new { t.Name , t.First_Name } into g
select new
{
Name = g.Key.Name
,First_Name=g.Key.First_Name
,qty = g.Count( x => x.Name != null)
}
).ToList();
or more compact:
var results =
(
from a in Repo.order_product_s
from b in Repo.Order_s
.Where(bb=> bb.id == a.Order_Id)
// .DefaultIfEmpty() // <== makes join left join
from c in Repo.Product_s
.Where(cc => cc.Id == a.Product_Id)
// .DefaultIfEmpty() // <== makes join left join
from d in Repo.Customer_s
.Where(dd => dd.Id == b.Customer_Id)
// .DefaultIfEmpty() // <== makes join left join
where b.Customer_Id == __UserId
select new
{
Name = c.Name
,First_Name = d.First_Name
}
into t group t by new { t.Name , t.First_Name } into g
select new
{
Name = g.Key.Name
,First_Name=g.Key.First_Name
,qty = g.Count( x => x.Name != null)
// Or like this
// ,qty = g.Select(x => x.Name).Where(x => x != null).Count()
// and if you ever need count(distinct fieldname)
//,qty = g.Select(x => x.GroupName).Where(x => x != null).Distinct().Count()
}
)
// .OrderBy(t => t.Name).ThenBy(t => t.First_Name).ThenBy(t => t.qty) // Order in SQL
.ToList()
// .OrderBy(t => t.Name).ThenBy(t => t.First_Name).ThenBy(t => t.qty) // Order in .NET
;
