select e.firstname, e.lastname, e.title, e.hiredate, count(od.quantity) as totorders
from employees e join orders o on e.employeeid = o.employeeid
join [Order Details] od on o.orderid = od.orderid
group by e.firstname, e.lastname, e.title, e.hiredate
var orders = from e in client.Employees
join o in client.Orders on e.EmployeeID equals o.EmployeeID //into result
join id in client.Order_Details on o.OrderID equals id.OrderID
//group e by new {e.FirstName, e.LastName, e.Title, e.HireDate, e.Address} into g
select new EmployeeViewModel()
{ FirstName = e.FirstName,
LasttName = e.LastName,
Title = e.Title,
HireDate = e.HireDate,
Address = e.Address,
TotOrders = id.Quantity };
var countOrder = from or in orders
group or by new { or.FirstName, or.LasttName, or.Title, or.HireDate, or.Address } into g
select new EmployeeViewModel()
{
FirstName = g.Key.FirstName,
LasttName = g.Key.LasttName,
Title = g.Key.Title,
HireDate = g.Key.HireDate,
Address = g.Key.Address,
TotOrders = g.Sum(x => x.TotOrders)
};
DivakarGanta
Member
39 Points
140 Posts
Convert Sql Query to Linq Query
Nov 04, 2012 02:48 PM|LINK
Pls give me the linq query for the same above?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Convert Sql Query to Linq Query
Nov 05, 2012 02:07 AM|LINK
Hello;)
Not every SQL can be converted to LINQ successfully, So I recommand you to have a try with the tool:
http://sqltolinq.com/
DivakarGanta
Member
39 Points
140 Posts
Re: Convert Sql Query to Linq Query
Nov 05, 2012 07:38 AM|LINK
var orders = from e in client.Employees join o in client.Orders on e.EmployeeID equals o.EmployeeID //into result join id in client.Order_Details on o.OrderID equals id.OrderID //group e by new {e.FirstName, e.LastName, e.Title, e.HireDate, e.Address} into g select new EmployeeViewModel() { FirstName = e.FirstName, LasttName = e.LastName, Title = e.Title, HireDate = e.HireDate, Address = e.Address, TotOrders = id.Quantity }; var countOrder = from or in orders group or by new { or.FirstName, or.LasttName, or.Title, or.HireDate, or.Address } into g select new EmployeeViewModel() { FirstName = g.Key.FirstName, LasttName = g.Key.LasttName, Title = g.Key.Title, HireDate = g.Key.HireDate, Address = g.Key.Address, TotOrders = g.Sum(x => x.TotOrders) };DivakarGanta
Member
39 Points
140 Posts
Re: Convert Sql Query to Linq Query
Nov 05, 2012 02:35 PM|LINK
But this is not working properly
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Convert Sql Query to Linq Query
Nov 06, 2012 12:11 AM|LINK
Any error in syntax or exception?
DivakarGanta
Member
39 Points
140 Posts
Re: Convert Sql Query to Linq Query
Nov 06, 2012 12:44 AM|LINK
I am not getting the proper count.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Convert Sql Query to Linq Query
Nov 06, 2012 12:54 AM|LINK
Pay attention to your standard SQL statement, and yours isn't matching your LINQ's. Please check fields.
And why your LINQ has so many columns as properties?
group or by new { or.FirstName, or.LasttName, or.Title, or.HireDate, or.Address } into