How to group columns by table name in LINQ Method Syntax in Table Relationships C# ASP .NET Entity Framework Core 6 Web API?

Question

I am new at C# ASP.Net Entity Framework . I am trying to build an API and I want to produce an output like this :

[
    {
        
        "userId": 1275,
        "username": "dmartin",
        "email": "[email protected]",
        "firstName": "Dan",
        "middleInitial": "",
        "lastName": "Martin",
        "isApproved": true,
        "lastActivityDate": "2012-10-05T12:23:24.253",
        "lastLoginDate": "2021-10-27T09:13:56.597",
        "lastPasswordChangedDate": "2020-07-29T16:06:41.863",
        "creationDate": "2010-04-07T22:51:14",
        "Iscinstance":[
            {
                "iscinstanceId": 236,
                "name": "ABA"
            }
        ],
        "UserProfile":[
            {
                "profileName": "",
                "address1": "",
                "directConnectPhone": "",
                "profileEmail": "",
                "profile": null
            }
        ]
    }
]

A have this classis created with table relationships :

User Table

[DataContract]
public partial class User
{
    [DataMember]
    public int UserId { get; set; }
    [DataMember]
    public string Username { get; set; }
    [DataMember]
    public string Email { get; set; }
    [DataMember]
    public bool IsApproved { get; set; }
    [DataMember]
    public DateTime? LastActivityDate { get; set; }
    [DataMember]
    public DateTime? LastLoginDate { get; set; }
    [DataMember]
    public DateTime? LastPasswordChangedDate { get; set; }
    [DataMember]
    public DateTime? CreationDate { get; set; }
    [DataMember]
    public string FirstName { get; set; }
    [DataMember]
    public string LastName { get; set; }
    [DataMember]
    public string MiddleInitial { get; set; }

    public virtual UserProfile Profile { get; set; }
    public virtual LinkUsersToIscinstance LinkUsersToIscinstances { get; set; }
    
}

UserProfile Table

[DataContract]
public partial class UserProfile
{
    public UserProfile()
    {
        LinkUsersToIscinstances = new HashSet<LinkUsersToIscinstance>();
        Users = new HashSet<User>();
    }
    [DataMember]
    public int ProfileId { get; set; }
    [DataMember]
    public string ProfileName { get; set; }
    [DataMember]
    public string Email { get; set; }
    [DataMember]
    public string DirectConnectPhone { get; set; }
    [DataMember]
    public string EmergencyContactNumber { get; set; }
    [DataMember]
    public string Address1 { get; set; }
    [DataMember]
    public string Address2 { get; set; }
    [DataMember]
    public string County { get; set; }
    [DataMember]
    public int State { get; set; }
    [DataMember]
    public string Zip { get; set; }
    [DataMember]
    public string EmergencyCell { get; set; }
    
    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

LinkUsersToIscinstance Table

[DataContract(IsReference = true)]
public partial class LinkUsersToIscinstance
{
    public LinkUsersToIscinstance()
    {
        Users = new HashSet<User>();
    }
    public int LinkId { get; set; }
    [DataMember]
    public int IscinstanceId { get; set; }
    [DataMember]
    public int UserId { get; set; }

    public virtual UserProfile Profile { get; set; }
    public virtual Iscinstance Iscinstances { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

Iscinstance Table

[DataContract]
public partial class Iscinstance
{
    public Iscinstance()
    {
        LinkUsersToIscinstances = new HashSet<LinkUsersToIscinstance>();
    }
    [DataMember]
    public int IscinstanceId { get; set; }
    [DataMember]
    public string Name { get; set; }

    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
}

I also created a custom class but I think a wont be needing it, here it is :

[DataContract]
public class CustomClass
{
    [DataMember]
    public int IscinstanceId { get; set; }
    //[DataMember(Name = "ISCInstanceName")]
    [DataMember]
    public string? Name { get; set; }
    [DataMember]
    public int UserId { get; set; }
    [DataMember]
    public string? Username { get; set; }
    [DataMember]
    public string? Email { get; set; }
    [DataMember]
    public string? FirstName { get; set; }
    [DataMember]
    public string? MiddleInitial { get; set; }
    [DataMember]
    public string? LastName { get; set; }
    [DataMember]
    public bool IsApproved { get; set; }
    [DataMember]
    public DateTime? LastActivityDate { get; set; }
    [DataMember]
    public DateTime? LastLoginDate { get; set; }
    [DataMember]
    public DateTime? LastPasswordChangedDate { get; set; }
    [DataMember]
    public DateTime? CreationDate { get; set; }
    [DataMember]
    public string? ProfileName { get; internal set; }
    [DataMember]
    public string? Address1 { get; internal set; }
    [DataMember]
    public string? DirectConnectPhone { get; internal set; }
    [DataMember]
    public string? ProfileEmail { get; internal set; }
}

And this is my query :

 var customer = await _context.Users
                            .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
                            .Include(c => c.Profile)
                            .Include(c => c.LinkUsersToIscinstances).ThenInclude(c => c.Iscinstances)
                            .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
                            .Select(c => new CustomClass
                            {
                                IscinstanceId = c.LinkUsersToIscinstances.Iscinstances.IscinstanceId,
                                Name = c.LinkUsersToIscinstances.Iscinstances.Name,

                                UserId = c.UserId,
                                Username = c.Username,
                                Email = c.Email,
                                FirstName= c.FirstName,
                                MiddleInitial= c.MiddleInitial,
                                LastName= c.LastName,
                                IsApproved= c.IsApproved,
                                LastActivityDate= c.LastActivityDate,
                                LastLoginDate= c.LastLoginDate,
                                LastPasswordChangedDate= c.LastPasswordChangedDate,
                                CreationDate= c.CreationDate,

                                ProfileName = c.Profile.ProfileName,
                                ProfileEmail = c.Profile.Email,
                                DirectConnectPhone = c.Profile.DirectConnectPhone,
                                Address1 = c.Profile.Address1

                                })
                            .ToListAsync();

This is my current output

[
    {
        "iscinstanceId": 236,
        "name": "ABA",
        "userId": 1275,
        "username": "dmartin",
        "email": "[email protected]",
        "firstName": "Dan",
        "middleInitial": "",
        "lastName": "Martin",
        "isApproved": true,
        "lastActivityDate": "2012-10-05T12:23:24.253",
        "lastLoginDate": "2021-10-27T09:13:56.597",
        "lastPasswordChangedDate": "2020-07-29T16:06:41.863",
        "creationDate": "2010-04-07T22:51:14",
        "profileName": "",
        "address1": "",
        "directConnectPhone": "",
        "profileEmail": "",
        "profile": null
    }
]

Hoping anyone could help me :(

Answer 1

First, you need some classes to represent the expected json result :

public class UserModel
{
    public int UserId { get; set; }
    public string? Username { get; set; }
    public string? Email { get; set; }
    public string? FirstName { get; set; }
    public string? MiddleInitial { get; set; }
    public string? LastName { get; set; }
    public bool IsApproved { get; set; }
    public DateTime? LastActivityDate { get; set; }
    public DateTime? LastLoginDate { get; set; }
    public DateTime? LastPasswordChangedDate { get; set; }
    public DateTime? CreationDate { get; set; }
    public IEnumerable<IscinstanceModel> Iscinstance { get; set; }
    public IEnumerable<UserProfileModel> UserProfile { get; set; }
}

public class IscinstanceModel
{
    public int IscinstanceId { get; set; }
    public string? Name { get; set; }
}

public class UserProfileModel
{
    public string? ProfileName { get; set; }
    public string? Address1 { get; set; }
    public string? DirectConnectPhone { get; set; }
    public string? ProfilEmail { get; set; }
    public string? Profile { get; set; }
}

Then you can convert the entities to the api model like :

var customers = await _context.Users
    .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
    .Include(c => c.Profile)
    .Include(c => c.LinkUsersToIscinstances).ThenInclude(c => c.Iscinstances)
    .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
    .Select(c => new UserModel
    {
        UserId = c.UserId,
        Username = c.Username,
        Email = c.Email,
        FirstName = c.FirstName,
        MiddleInitial = c.MiddleInitial,
        LastName = c.LastName,
        IsApproved = c.IsApproved,
        LastActivityDate = c.LastActivityDate,
        LastLoginDate = c.LastLoginDate,
        LastPasswordChangedDate = c.LastPasswordChangedDate,
        CreationDate = c.CreationDate,
        Iscinstance = c.LinkUsersToIscinstances
            .SelectMany(l => l.Iscinstances)
            .Select(i =>
                new IscinstanceModel {
                    IscinstanceId = l.IscinstanceId,
                    Name = l.name
                }
            ),
        UserProfile = new [] {
            new UserProfileModel{
                ProfileName = c.Profile.ProfileName,
                Address1 = c.Profile.Address1,
                DirectConnectPhone = c.Profile.DirectConnectPhone,
                ProfilEmail = c.Profile.ProfilEmail
            }
        }
    })
    .ToListAsync();

The expected json has by user a list of profile, but the entity has one profile. In this case, just encapsulate the profile in a collection. Same to iscinstance.