Help with Linq to SQL Group By RRS feed

  • Question

  • I am trying to convert this sql to linq and can not figure it out...


    SELECT     dbo.tb1.RequirementId, dbo.tb2.strNumber, dbo.tb2.strCode, dbo.tb3.guidFLId
    FROM         dbo.tb2
      RIGHT OUTER JOIN dbo.tb3 ON dbo.tb2.guidAId = dbo.tb3.guidAId
      RIGHT OUTER JOIN dbo.tb1 ON dbo.tb3.guidCId = dbo.tb1.guidCd
    GROUP BY dbo.tb1.strNumber, dbo.tb2.strCode, dbo.tb1.RequirementId, dbo.tb3.guidFLId

    Please Help, c# please
    Friday, December 4, 2009 8:00 PM


  • Hello Evan,

    Welcome to MSDN Forums!

    If I understand the problem correctly, the RIGHT OUTER JOIN T-SQL can be converted to LEFT OUTER JOIN query:

    SELECT     dbo.tb1.RequirementId, dbo.tb1.strNumber, dbo.tb2.strCode, dbo.tb3.guidFLId
    FROM       dbo.tb1
    LEFT OUTER JOIN dbo.tb3 ON dbo.tb1.guidCd = dbo.tb3.guidCId
    LEFT OUTER JOIN dbo.tb2 ON dbo.tb3.guidAId = dbo.tb2.guidAId
    GROUP BY dbo.tb1.strNumber, dbo.tb2.strCode, dbo.tb1.RequirementId, dbo.tb3.guidFLId

    Then we can use the DefaultIfEmpty method to make the LEFT OUTER JOINs:

    var query = (from i1 in

                    (from t1 in db.tb1s

                     join t3 in db.tb3s on t1.guidCd equals t3.guidCId into list1

                     from l1 in list1.DefaultIfEmpty()

                     select new { t1.RequirementId, t1.strNumber, guidFLid = l1.guidFLid == null ? 0 : l1.guidFlid, guidAId = l1.guidAId == null ? 0 : l1.guidAId })

                 join t2 in db.tb2s on i1.guidAId equals t2.guidAId into list2

                 from l2 in list2.DefaultIfEmpty()

                 select new { i1.RequirementId, i1.strNumber, t2.strCode, i1.guidFLid }).GroupBy(i => new { i.strNumber, t.strCode, i.RequirementId, i.guidFLid });

    You can refer to this related threads, http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/fb8070d6-cc51-4adc-bde7-8cc9906654c9.  Although it is for EF, but the LEFT OUTER JOIN query is similar. 

    NOTE:  We need to set a few properties to some default values if they are not nullable, e.g. (guidFLid == null ? 0 : l1.guidFlid)

    Best Regards,
    Lingzhi Sun

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 7, 2009 1:21 PM