none
Group into LINQ query not working RRS feed

  • Question

  • Hi,

    I have a stored proc that retuns companies and users.

    select c.companyid, c.companyname, u.firstname, u.lastname from tblcompany c
    inner join tbluser u on c.companyid = u.companysubscriberid

    As you can see the inner joind returns a flat structure.

    companyid companyname firstname lastname
    7 Cleve                                 Joe Dennehy
    7 Cleve                                 Kathy Dunasky
    17 Media, Inc.                       Roslyn Liberti
    17 Media, Inc.                       Mark Bugni
    17 Media, Inc.                       Alison McCauley
    17 Media, Inc.                       Tom Hennessey
    43 Med Society                     NEJM Shared

    I would like to convert this result set into a hierarchical object uing LINQ  containing 1 company many users:

    7 Cleve
          user a
          user b
    17 Media
          user c
          user d

    I have tried the LINQ code below but I am not able to get the other properties apart from the Key. Could you please shed some light?

    var cps = from comp in Core.Data.GetAllCompanies(clientID, posterSiteID, null)
                          group comp by comp.companyId into g
                          select new
                          {
                              CompanyID = g.Key,
                              name = comp.companyName //ERROR HERE I have also tried g.companyName
                          };

    Cheers,

    C

     

     

    Friday, April 20, 2012 9:04 PM

Answers

  • Hi Claudio,

    In order to access the companyName property in that context you'll want to group by comp instead of comp.companyId like so: 

    var cps = from comp in Core.Data.GetAllCompanies(clientID, posterSiteID, null) 
              group comp by comp into g
              select new
              {
                  CompanyID = g.Key,
                  name = g.Key.companyName,
              };

    This query really doesn't make much sense however since you're grouping a collection of companies by themselves, so you'll end up with a collection of collections, each having a single element. I'm not sure where your users come into play in your query. Assuming you have a collection of Users named Users and a collection of Companies named Companies you could use a group join like so to get the data in the structure you want:

    var cps = from comp in Companies
                join user in Users on comp.companyId equals user.companysubscriberid into companyUsers
                select new
                {
                    CompanyID = comp.companyId,
                    name = comp.companyName,
                    Users = companyUsers.Select(u => new
                                                        {
                                                            u.firstname,
                                                            u.lastname,
                                                        }),
                };

    Notice the use of the "into" keyword which turns the normal join into a Group Join, which gives precisely the hierarchal data structure you want. A single element from the left side of the query is joined to a collection of matching objects from the right side. For more info see the link: http://msdn.microsoft.com/en-us/library/bb311040.aspx

    Regards,

    Tyler

    Friday, April 20, 2012 11:49 PM

All replies

  • Hi Claudio,

    In order to access the companyName property in that context you'll want to group by comp instead of comp.companyId like so: 

    var cps = from comp in Core.Data.GetAllCompanies(clientID, posterSiteID, null) 
              group comp by comp into g
              select new
              {
                  CompanyID = g.Key,
                  name = g.Key.companyName,
              };

    This query really doesn't make much sense however since you're grouping a collection of companies by themselves, so you'll end up with a collection of collections, each having a single element. I'm not sure where your users come into play in your query. Assuming you have a collection of Users named Users and a collection of Companies named Companies you could use a group join like so to get the data in the structure you want:

    var cps = from comp in Companies
                join user in Users on comp.companyId equals user.companysubscriberid into companyUsers
                select new
                {
                    CompanyID = comp.companyId,
                    name = comp.companyName,
                    Users = companyUsers.Select(u => new
                                                        {
                                                            u.firstname,
                                                            u.lastname,
                                                        }),
                };

    Notice the use of the "into" keyword which turns the normal join into a Group Join, which gives precisely the hierarchal data structure you want. A single element from the left side of the query is joined to a collection of matching objects from the right side. For more info see the link: http://msdn.microsoft.com/en-us/library/bb311040.aspx

    Regards,

    Tyler

    Friday, April 20, 2012 11:49 PM
  • Hi Claudio

    yes.

    the group join just like group by when only group with once column.

    maybe the group join would be your want.

    have a nice day


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.



    Saturday, April 21, 2012 1:52 AM
  • Hi Tyler,

    Thanks very much for your reply and sample code.

    Please note that I am not using the Entity framework to fetch the data. I will get a SqlDataReader back with the flat structure.

    I will try your sample on Monday.

    Cheers

    C

    Saturday, April 21, 2012 3:35 PM
  • Hi Tyler,

    I still have a problem with the query.

    This is the data I have in a sql Data Reader. Please note that the company is repeated for each user that belongs to the same company. 

    companyid companyname       firstname lastname
    7 Cleve                                 Joe Dennehy
    7 Cleve                                 Kathy Dunasky
    17 Media, Inc.                       Roslyn Liberti
    17 Media, Inc.                       Mark Bugni
    17 Media, Inc.                       Alison McCauley
    17 Media, Inc.                       Tom Hennessey
    43 Med Society                     NEJM Shared

    Therefore, I think I need a query similar to your firts example.

    However, how do I get the collection of users for each company?

    Should the code below be working?

    var cps = from comp in Core.Data.GetAllCompanies(clientID, posterSiteID, null)
             
    group comp by comp into g
             
    select new
             
    {
                 
    CompanyID = g.Key,
                  name
    = g.Key.companyName,
                  Users =
     comp.Key.Select(u => new
                                                       
    {
                                                            u
    .firstname,
                                                            u
    .lastname,
                                                       
    }),
              };

    Cheers

    C

    Tuesday, May 8, 2012 10:05 AM