none
Linq to SQL 3 way join with a sum RRS feed

  • Question

  • I have a query as below with some issues on converting that to LINQ.

    Query is:

    SELECT P.ProjectID, MIN(ProjectName) As PrjName, SUM(Hours * Rate) As Price

    FROM Projects P 
            INNER JOIN Tasks T ON P.ID = T.ProjectID
            INNER JOIN Timesheets Time ON T.TaskID = Time.TaskID

    GROUP BY P.ProjectID

    My best attempt on Linq is (not quite working yet) is:

    Problems are at the ??

                var q = from p in db.Projects
                        join t in db.Tasks on p.ProjectID equals t.ProjectID
                        join time in db.Timesheets on t.TaskID equals time.TaskID
                        group p by p.ProjectID into g
                        select new { ID = g.Key, Name = ??, Price = g .Sum(x => x??)};

    Any help?

    Friday, November 28, 2014 8:20 AM

Answers

  • Hello Jowalski,

    >>My best attempt on Linq is (not quite working yet) is:

    Not sure if you get the workaround, from your provided query, it should be close. As far as I know the Group By syntax in LINQ would only perform with columns after the Group(in your case, it should be columns in Projects), from your provided sql statement, there are columns coming from other tables, so you could create an anonymous and temporary “Table” as below:

    var q = from p in db.Projects
    
                         join t in db.Tasks on p.ProjectID equals t.ProjectID
    
                         join time in db.Timesheets on t.TaskID equals time.TaskID
    
                         group new { ProjectID = p. ProjectID, ProjectName= p. ProjectName, Hours = time. Hours, Rate=time.Rate } by p.ProjectID into g
    
                         select new { ID = g.Key, Name = g .Min( x => x. ProjectName), Price = g .Sum(x => x Hours*x. Rate)};
    

    If you want to know more about Group By in LINQ, you could check the link below:

    http://msdn.microsoft.com/en-us/library/bb545971.aspx

    Please have a try and if it does not work for you, please feel free let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jowalski Monday, December 1, 2014 6:53 AM
    Monday, December 1, 2014 1:53 AM
    Moderator

All replies

  • Hello Jowalski,

    >>My best attempt on Linq is (not quite working yet) is:

    Not sure if you get the workaround, from your provided query, it should be close. As far as I know the Group By syntax in LINQ would only perform with columns after the Group(in your case, it should be columns in Projects), from your provided sql statement, there are columns coming from other tables, so you could create an anonymous and temporary “Table” as below:

    var q = from p in db.Projects
    
                         join t in db.Tasks on p.ProjectID equals t.ProjectID
    
                         join time in db.Timesheets on t.TaskID equals time.TaskID
    
                         group new { ProjectID = p. ProjectID, ProjectName= p. ProjectName, Hours = time. Hours, Rate=time.Rate } by p.ProjectID into g
    
                         select new { ID = g.Key, Name = g .Min( x => x. ProjectName), Price = g .Sum(x => x Hours*x. Rate)};
    

    If you want to know more about Group By in LINQ, you could check the link below:

    http://msdn.microsoft.com/en-us/library/bb545971.aspx

    Please have a try and if it does not work for you, please feel free let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jowalski Monday, December 1, 2014 6:53 AM
    Monday, December 1, 2014 1:53 AM
    Moderator
  • Works 100%.  Thanks also for the link!
    Monday, December 1, 2014 6:54 AM