Join tables and select grouped data into list-CodePudding

I have two simple database model classes:

application_user.cs:

public partial class application_user
{
    public int Id { get; set; }
    public string Name { get; set; }
}

application_user_card.cs:

public partial class application_user_card
{
    public int Id { get; set; }
    public int User_Id { get; set; }
    public string Card_Id { get; set; }
}

I want to select a list of all users, with each user containing a list of Card IDs. This is the data model for the business logic:

User.cs:

public class User
{
    public int Id { get; set; }

    public string Name { get; set; }

    public List<string> CardIds { get; set; }
}

This is my current code. If I'm being honest I don't really understand the grouping syntax, or to be more precise, how to include the data from cardIds in my query.

List<UserGroup> userGroups = from user in pigDbAccountEntities.application_user
                             join cardIds in pigDbAccountEntities.application_user_card on user.Id equals cardIds.User_Id
                             where user.Delete_Time == DateTime.MinValue
                             group user by user.Id into gUser
                             select new UserGroup
                             {
                                 Id = gUser.Key,
                                 Name = gUser.FirstOrDefault().Name,
                                 CardIds = new List<string>() { cardIds.Card_Id.ToList() }
                             };

Is there any way to do what I want to do in a single query?

CodePudding user response：

I don't see Delete_Time defined in your question, so i removed it in my query. But looks like you need to change your "group" line to have both user & cardIds, which will create your anonymous object that you select from.

var userGroups = from user in pigDbAccountEntities.application_user
                          join cardIds in pigDbAccountEntities.application_user_card on user.Id equals cardIds.User_Id
                          group new { user, cardIds } by user.Id into gUser
                          select new UserGroup
                          {
                              Id = gUser.Key,
                              Name = gUser.FirstOrDefault().user.Name,
                              CardIds = gUser.Select(x => x.cardIds.Card_Id).ToList()
                          };

This returns an IEnumerable but if you want a list just call userGroups.ToList() or finally:

List<UserGroup> userGroups = (from user in pigDbAccountEntities.application_user
                          join cardIds in pigDbAccountEntities.application_user_card on user.Id equals cardIds.User_Id
                          group new { user, cardIds } by user.Id into gUser
                          select new UserGroup
                          {
                              Id = gUser.Key,
                              Name = gUser.FirstOrDefault().user.Name,
                              CardIds = gUser.Select(x => x.cardIds.Card_Id).ToList()
                          }).ToList();