none
LINQ (C#) Join - Displaying duplicated data in single row (Pivot) RRS feed

  • Question

  • I am trying to join a table and view and display the result in data table.

    Consider my scenario (Database) :

    Table : Request

    RequestId              SurrogateId

    1                                  1

    2                                    2

    View : DemoView

    SurrogateId          ApproverName              ApproverType

    1                            Ram                             Finance

    1                            Sam                             Admin

    1                            Dan                               Manager

    2                            abc                               Finance

    My requirement is to display as follows on UI in DataTable as follows :

    RequestId        SurrogateId          Finance          Manager          Admin

    1                           1                     Ram              Sam               Dan

    2                            2                      abc

    I have tried the following query :

    var query = (
    
     from request in DB.Request
    
     join financeaprv in DB.DemoView on request.SurrogateID equals financeaprv.SurrogateID into financeaprvList
                                 from financeaprv in financeaprvList.DefaultIfEmpty()
                                 where financeaprv.ApproverType=="Finance"
    
      join sysadmin in DB.DemoView on request.SurrogateID equals sysadmin.SurrogateID into sysadminList
                                 from sysadmin in legalaprvList.DefaultIfEmpty()
                                 where sysadmin.ApproverType=="Admin"
    
      select new queryResult                             
      {
            RequestId = request.RequestId,
            FinanceApprv = financeaprv.ApproverName,
            Admin = sysadmin.ApproverName
    
      }
    

    But the join is not correct as not returns all rows.(Joining RequestID and SurrogateId is not much of an issue as it's straight forward.   It's joining Admin,Finance and Manager I'm unable to accomplish)

    Can anybody tell what would be the LINQ query ( in Query syntax ) to accomplish my requirement.

    Thanks in advance.

    Note :- I'm intentionally joining DemoView each time (instead of storing all it's value in a list at once) due to requirement reasons... I'm trying to accomplish the query in the same way I've mentioned above.

    Wednesday, November 30, 2016 5:54 AM

All replies

  • According to https://social.msdn.microsoft.com/Forums/en-US/e09b50af-5ab4-4cfb-9617-ef2be339c17f, try a complex ‘on’ condition without ‘where’, probably like this:

    from request in DB.Request
    join financeaprv in DB.DemoView on new { request.SurrogateID, ApproverType = "Finance" } equals new { financeaprv.SurrogateID, financeaprv.ApproverType } into financeaprvList
    from financeaprv in financeaprvList.DefaultIfEmpty() 
    . . .

    When you generate the final output values, check the data for null, like ‘select new { FinanceApprv = financeaprv?.ApproverName, …’.


    • Edited by Viorel_MVP Wednesday, November 30, 2016 7:49 AM
    Wednesday, November 30, 2016 7:46 AM
  • Hi Mr_SK,

    Based on your description, I create a simple demo as below for your reference.
    var query = db.Requests.Select(t => new {

                        RequestId = t.RequestId,
                        SurrogateId = t.SurrogateId,
                        Finance = db.DemoViews.Where(d=>d.SurrogateId == t.SurrogateId && d.ApproverType == "Finance").Select(d=>d.ApproverName).FirstOrDefault(),
                        Manager = db.DemoViews.Where(d => d.SurrogateId == t.SurrogateId && d.ApproverType == "Manager").Select(d => d.ApproverName).FirstOrDefault(),
                        Admin = db.DemoViews.Where(d => d.SurrogateId == t.SurrogateId && d.ApproverType == "Admin").Select(d => d.ApproverName).FirstOrDefault()
                    });

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 2:12 AM
    Moderator