locked
How to create a sublist from the list of records using linq? RRS feed

  • Question

  • I might have failed to explain the question properly in the title, below explanation would suffice.

    class Person { public string Name { get; set; } public int ID { get; set; } public string Pet {get;set;} public int PetID {get;set;} } //Consider below object as record set coming from database. List<Person> categories = new List<Person>() { new Person {Name="John Doe", ID=001, Pet="Dog", PetID=001}, new Person {Name="John Doe", ID=001, Pet="Cat", PetID=002}, new Person {Name="John Doe", ID=001, Pet="Parrot", PetID=003} };

    //currently I wrote the linq query as below

    var obj = (from p in Person select new Person { Name= p.Name, ID= p.ID, Pet= p.Pet PetID=p.PetID }).ToList(); // I want to change the linq query to return the data with a sublist in the object, as below.

    I'm not sure if the below linq query would work properly.

    public class NewPerson { public string Name{get;set;} public int Id{get;set;} public List<Pets> pet{get;set;} } public class Pets { public string PetName{get;set;} public int PetID {get;set;} } var obj = (from p in Person select new NewPerson { Name= p.Name, ID= p.ID, select new List<Pets> { PetName= p.Pet, PetID= p.PetID } });

    Kindly help me in getting the return object with couple of properties, one of the property as list.

    Help is highly appreciated. Thanks.


    Email address update for alerts

    Friday, September 11, 2020 1:52 PM

Answers

  • Check this code:

    var query = from p in categories
    			group p by new { p.ID, p.Name } into g
    			select new NewPerson
    			{
    				Name = g.Key.Name,
    				Id = g.Key.ID,
    				pet = g.Select( i => new Pets { PetName = i.Pet, PetID = i.PetID } ).ToList( )
    			};
    
    List<NewPerson> new_list = query.ToList( );
    

    Friday, September 11, 2020 6:20 PM
  • Hi Daniel,

    Thanks for the further clarification on the group by clause, between now and then, I was literally pulling my hair and wrote a linq query which gave me expected results. Below is the one I used.

    public PersonalizedMaterialUserBusinessListingInfoViewModel GetUserBusinessListingTemplateInformation(Guid userGuid)
            {
                using (var ctxAdmin = new RMCRMTotalDataContext(ConfigurationManager.ConnectionStrings[Constants.CONFIG_KEY_RMCRM_TOTAL_CONNECTION_STRING].ToString()))
                {
                    var businessListingInfo = (from mf in ctxAdmin.MCMemberFlyers
                                               join bl in ctxAdmin.MCBusinessListings
                                               on mf.businessListingGuid equals bl.businessListingGuid
                                               join mbl in ctxAdmin.MCMemberBusinessListings
                                               on bl.businessListingGuid equals mbl.businessListingGuid
                                               join c in ctxAdmin.Contacts
                                               on mbl.ContactGuid equals c.ContactGuid
                                               join cbi in ctxAdmin.ContactBasicInfos
                                               on c.ContactId equals cbi.ContactId
                                               join cb in ctxAdmin.ContactBusinesses
                                               on cbi.ContactId equals cb.ContactId
                                               where c.ContactType == businessContactType &&
                                                     mf.UserId == userGuid
                                               group new { mf, bl, mbl,c,cbi, cb} by new
                                               {
                                                   UserId= mf.UserId,
                                                   Month=mf.month,
                                                   Year=mf.year,
                                                   BusinessTitle=bl.businessTitle,
                                                   ShowLogos=bl.showLogos,
                                                   BusinessListingGuid=bl.businessListingGuid,
                                                   BusinessListingContactGuid=mbl.ContactGuid,
                                                   ContactGuid=c.ContactGuid,
                                                   ContactId=c.ContactId
    
                                               } into g
                                               select new PersonalizedMaterialUserBusinessListingInfoViewModel
                                               {
                                                   UserGuid = g.Key.UserId,
                                                   Month = g.Key.Month,
                                                   Year = g.Key.Year,
                                                   BusinessTitle = g.Key.BusinessTitle,
                                                   ShowLogos = g.Key.ShowLogos,
                                                   BusinessListingGuid = g.Key.BusinessListingGuid,
                                                   BusinessListingContactGuid = g.Key.BusinessListingContactGuid,
                                                   ContactGuid = g.Key.ContactGuid,
                                                   ContactId = g.Key.ContactId,
                                                   Businesses = g.Select(i=> new BusinessInfo
                                                   {
                                                       LogoBlobUrl=i.c.ImageLink,
                                                       BusinessFullName=i.c.FullName,
                                                       BusinessIndustryType=i.cb.Industry,
                                                       BusinessEmail=i.cbi.Email,
                                                       BusinessOfficePhone=i.cbi.OfficePhone,
                                                       BusinessMobilePhone=i.cbi.MobilePhone,
                                                       BusinessWebsite=i.cbi.Website
                                                   }).ToList()
                                               }).FirstOrDefault();
                    return businessListingInfo;
                }
            }

    I really thank Viorel and you for helping me out on this issue.


    Email address update for alerts

    Monday, September 14, 2020 3:05 PM

All replies

  • Check this code:

    var query = from p in categories
    			group p by new { p.ID, p.Name } into g
    			select new NewPerson
    			{
    				Name = g.Key.Name,
    				Id = g.Key.ID,
    				pet = g.Select( i => new Pets { PetName = i.Pet, PetID = i.PetID } ).ToList( )
    			};
    
    List<NewPerson> new_list = query.ToList( );
    

    Friday, September 11, 2020 6:20 PM
  • Hi Viorel

    Thanks for replying to my post, I appreciate your help.

    I was trying to use the mentioned group by clause as below and was able to access only one property, I believe I'm missing something here.

    public PersonalizedMaterialUserBusinessListingInfoViewModel GetUserBusinessListingTemplateInformation(Guid userGuid)
            {
                using (var ctxAdmin = new RMCRMTotalDataContext(ConfigurationManager.ConnectionStrings[Constants.CONFIG_KEY_RMCRM_TOTAL_CONNECTION_STRING].ToString()))
                {
                    var businessListingInfo = (from mf in ctxAdmin.MCMemberFlyers
                                               join bl in ctxAdmin.MCBusinessListings
                                               on mf.businessListingGuid equals bl.businessListingGuid
                                               join mbl in ctxAdmin.MCMemberBusinessListings
                                               on bl.businessListingGuid equals mbl.businessListingGuid
                                               join c in ctxAdmin.Contacts
                                               on mbl.ContactGuid equals c.ContactGuid
                                               join cbi in ctxAdmin.ContactBasicInfos
                                               on c.ContactId equals cbi.ContactId
                                               join cb in ctxAdmin.ContactBusinesses
                                               on cbi.ContactId equals cb.ContactId
                                               where c.ContactType == businessContactType &&
                                                     mf.UserId == userGuid
                                               group new { mf, bl, mbl,c,cbi, cb} by new { mf.UserId} into g
                                               select new PersonalizedMaterialUserBusinessListingInfoViewModel
                                               {
                                                   UserGuid = g.Key.UserId,
                                                   Month = g. mf.month,
                                                   Year = mf.year,
                                                   BusinessTitle = bl.businessTitle,
                                                   ShowLogos = bl.showLogos,
                                                   BusinessListingGuid = bl.businessListingGuid,
                                                   BusinessListingContactGuid = mbl.ContactGuid,
                                                   ContactGuid = c.ContactGuid,
                                                   ContactId = c.ContactId,
                                                   Businesses = new List<BusinessInfo>()
                                                   {
                                                         new BusinessInfo()
                                                         {
                                                             LogoBlobUrl=c.ImageLink,
                                                             BusinessFullName=c.FullName,
                                                             BusinessIndustryType=cb.Industry,
                                                             BusinessEmail=cbi.Email,
                                                             BusinessOfficePhone=cbi.OfficePhone,
                                                             BusinessMobilePhone=cbi.MobilePhone,
                                                             BusinessWebsite=cbi.Website
                                                         }
                                                   }
                                               }).ToList();
                    return businessListingInfo;
                }
            }


    Email address update for alerts

    Saturday, September 12, 2020 1:40 AM
  • Hi Anantha Asamani,
    >>group new { mf, bl, mbl,c,cbi, cb} by new { mf.UserId} into g
    When this statement is executed,  you can't access to the range variables used in the initial from. That is, you can no longer use mf,bl,mbl or c...
    And you can only "g".
    To achieve your requirement, you can try the following code

    let firstproductgroup = g.FirstOrDefault()
    let MCMemberflyers= firstproductgroup.mf
    let MCBusinesslistings= firstproductgroup.bl
    select new PersonalizedMaterialUserBusinessListingInfoViewModel
       {
           Month=MCMemberflyers.month,
           ShowLogos = MCBusinesslistings.showLogos,
           ......
       }

    More soultions in this thread you can refer to.
    Best Regards,
    Daniel Zhang


    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.

    Monday, September 14, 2020 6:59 AM
  • Hi Daniel,

    Thanks for the further clarification on the group by clause, between now and then, I was literally pulling my hair and wrote a linq query which gave me expected results. Below is the one I used.

    public PersonalizedMaterialUserBusinessListingInfoViewModel GetUserBusinessListingTemplateInformation(Guid userGuid)
            {
                using (var ctxAdmin = new RMCRMTotalDataContext(ConfigurationManager.ConnectionStrings[Constants.CONFIG_KEY_RMCRM_TOTAL_CONNECTION_STRING].ToString()))
                {
                    var businessListingInfo = (from mf in ctxAdmin.MCMemberFlyers
                                               join bl in ctxAdmin.MCBusinessListings
                                               on mf.businessListingGuid equals bl.businessListingGuid
                                               join mbl in ctxAdmin.MCMemberBusinessListings
                                               on bl.businessListingGuid equals mbl.businessListingGuid
                                               join c in ctxAdmin.Contacts
                                               on mbl.ContactGuid equals c.ContactGuid
                                               join cbi in ctxAdmin.ContactBasicInfos
                                               on c.ContactId equals cbi.ContactId
                                               join cb in ctxAdmin.ContactBusinesses
                                               on cbi.ContactId equals cb.ContactId
                                               where c.ContactType == businessContactType &&
                                                     mf.UserId == userGuid
                                               group new { mf, bl, mbl,c,cbi, cb} by new
                                               {
                                                   UserId= mf.UserId,
                                                   Month=mf.month,
                                                   Year=mf.year,
                                                   BusinessTitle=bl.businessTitle,
                                                   ShowLogos=bl.showLogos,
                                                   BusinessListingGuid=bl.businessListingGuid,
                                                   BusinessListingContactGuid=mbl.ContactGuid,
                                                   ContactGuid=c.ContactGuid,
                                                   ContactId=c.ContactId
    
                                               } into g
                                               select new PersonalizedMaterialUserBusinessListingInfoViewModel
                                               {
                                                   UserGuid = g.Key.UserId,
                                                   Month = g.Key.Month,
                                                   Year = g.Key.Year,
                                                   BusinessTitle = g.Key.BusinessTitle,
                                                   ShowLogos = g.Key.ShowLogos,
                                                   BusinessListingGuid = g.Key.BusinessListingGuid,
                                                   BusinessListingContactGuid = g.Key.BusinessListingContactGuid,
                                                   ContactGuid = g.Key.ContactGuid,
                                                   ContactId = g.Key.ContactId,
                                                   Businesses = g.Select(i=> new BusinessInfo
                                                   {
                                                       LogoBlobUrl=i.c.ImageLink,
                                                       BusinessFullName=i.c.FullName,
                                                       BusinessIndustryType=i.cb.Industry,
                                                       BusinessEmail=i.cbi.Email,
                                                       BusinessOfficePhone=i.cbi.OfficePhone,
                                                       BusinessMobilePhone=i.cbi.MobilePhone,
                                                       BusinessWebsite=i.cbi.Website
                                                   }).ToList()
                                               }).FirstOrDefault();
                    return businessListingInfo;
                }
            }

    I really thank Viorel and you for helping me out on this issue.


    Email address update for alerts

    Monday, September 14, 2020 3:05 PM
  • Hi Anantha Asamani,
    I am glad you have solved your problem,we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly.
    Best Regards,
    Daniel Zhang


    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.

    Tuesday, September 15, 2020 6:08 AM