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": "Dan.Martin@i-s-consulting.com",
        "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": "Dan.Martin@i-s-consulting.com",
        "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 :(

Also, you should include your code as a Code Block rather than an image. — Troy Turley, Apr 04 '22 at 21:27
can you make a query for me for me on this? please I really need help — Ken, Apr 04 '22 at 21:29
Which Database system are using for this? SQL, MongoDB or other? — Dawood Awan, Apr 04 '22 at 23:22
Are you sure your output contains `"profile": null`? Your CustomClass does not contains such property named `profile`. And did you want to output like your first json instead of your current output? — Rena, Apr 05 '22 at 03:55
yes @Rena I want to the output like the first json format not like my current output, I removed the profile actually that's why is says null because i was trying something but fail, hoping you guys can help me — Ken, Apr 05 '22 at 14:29
It's difficult for me to understand the relationship between your Entities, So can you explain the relationship between your entities? Your `User` entity seems to have 1 `UserProfile`, and the `UserProfile` seems to have Many users. How does that work? Where are the FK Properties? — Dawood Awan, Apr 05 '22 at 15:40
@DawoodAwan actually it has only one User for every UserProfile — Ken, Apr 05 '22 at 17:49
Can you show how the query's result is transformed/returned by the controller's action? — vernou, Apr 05 '22 at 22:40
It's at the bottom @vernou that's my current output, I want to achieve the output from the top — Ken, Apr 06 '22 at 00:12
Do it return the variable `customer` like `return Ok(customer)`? — vernou, Apr 06 '22 at 06:58
@TroyTurley that's a *very* bad idea. There's absolutely no reason to use JOINs with an ORM, these are entities, not tables. If you use `JOIN` it means the model is wrong, and the ORM is treated as a database connection or model. `Include` controls eager loading. The collection properties are already .... collections. In fact, even `Include` isn't needed here because `Select` directly references the related entities — Panagiotis Kanavos, Apr 06 '22 at 07:45

vernou · Accepted Answer · 2022-04-06T09:58:26.727

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.

I think all `Include`s can be removed because the related entities are referenced in the `Select` method — Panagiotis Kanavos, Apr 06 '22 at 07:47
@PanagiotisKanavos, not sure about the lambda in SelectMany. — vernou, Apr 06 '22 at 09:45
That would fail with or without `Include`. EF Core will generate a SQL query from the entire LINQ query, not just parts of it. Since `Iscinstances` is used in the LINQ query it has to appear in the SQL query as well. If EF Core is unable to generate a SQL query it will throw an exception even with `Include` — Panagiotis Kanavos, Apr 06 '22 at 09:49
@PanagiotisKanavos, EF Core don't fail when it can't translate in the top-level project. See :[Client evaluation in the top-level projection](https://learn.microsoft.com/en-us/ef/core/querying/client-eval#client-evaluation-in-the-top-level-projection). — vernou, Apr 06 '22 at 10:00
I removed the SelectMany, because `User.LinkUsersToIscinstances` isn't collection... mislead by `UserProfil.LinkUsersToIscinstances` that is a collection. — vernou, Apr 06 '22 at 10:02
@vernou you controller code actually work, thank you so much, `SelectMany` was an error for me so I just change it to `Select` but now I updated my table relationship between `LinkUsersToIscinstance` and `Iscinstance`, their relationship goes like this: `LinkUsersToIscinstance` has many `Iscinstance` and Iscinstance has one `LinkUsersToIscinstance`, I want to put `Iscinstance ` inside `LinkUsersToIscinstance` the output, the output looks something like this `"LinkUsersToIscinstance ": [ { ... "iscinstance": [ { ... } ] } ]` — Ken, Apr 06 '22 at 15:51
@vernou here is the class update for the `LinkUsersToIscinstance` and `Iscinstance`, in `LinkUsersToIscinstance` `public virtual Iscinstance Iscinstances { get; set; }` and in `Iscinstance` `public virtual ICollection LinkUsersToIscinstances { get; set; }` — Ken, Apr 06 '22 at 15:54
@vernou thank you so much for helping me, thanks to your code I actually get the output that I want , I just modify a little bit on the controller and custom class side, I really appreciate your help — Ken, Apr 06 '22 at 16:15

Rena · Answer 2 · 2022-04-06T09:02:58.107

From your backend code, it seems one User have one UserProfile and Iscinstance, but your expected json here is a List object:

"Iscinstance":[
        {
            "iscinstanceId": 236,
            "name": "ABA"
        }
    ],
    "UserProfile":[
        {
            "profileName": "",
            "address1": "",
            "directConnectPhone": "",
            "profileEmail": "",
            "profile": null
        }
    ]

If you actually want this type of json, you need change your CustomClass to:

public class CustomClass
{
    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 dynamic? Iscinstance { get; set; }
    public dynamic? UserProfile { get; set; }
}

Controller:

    var customer = await _context.User
                .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
                
                .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
                .Select(c => new TestModel
                {
                    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 = new [] {
                        new {
                            IscinstanceId= c.LinkUsersToIscinstances.Iscinstances.IscinstanceId,
                            Name = c.LinkUsersToIscinstances.Iscinstances.Name
                        }
                    },
                    UserProfile = new []{
                        new {
                            ProfileName = c.Profile.ProfileName,
                            ProfileEmail = c.Profile.Email,
                            DirectConnectPhone = c.Profile.DirectConnectPhone,
                            Address1 = c.Profile.Address1
                        }
                    }
                })
                .ToListAsync();

Does it have to be `new List`? Wouldn't just `new []{ ..}` do the same? The `Include`s can be removed too — Panagiotis Kanavos, Apr 06 '22 at 07:46
1. Of course you can use `new[]{}` but remember you need change the model property type to `dynamic`. 2.Yes, Include can be removed. — Rena, Apr 06 '22 at 09:02
@Rena your query actually work, thank you, but now the problem is that `Iscinstance = new [] { .. }` section like `c.LinkUsersToIscinstances.Iscinstances.IscinstanceId` will not work any more I have update the relationship between `LinkUsersToIscinstances` and `Iscinstances`, the relationship now is `LinkUsersToIscinstances` has many `Iscinstances` and `Iscinstances` has one `LinkUsersToIscinstances` and error is `ICollection does not contain a definition for Iscinstances and no extention method Iscinstances acception the first argument` — Ken, Apr 06 '22 at 15:12
@Rena here is my classis relationship between the two `LinkUsersToIscinstance` `public virtual Iscinstance Iscinstances { get; set; }` and in `Iscinstance` `public virtual ICollection LinkUsersToIscinstances { get; set; }` — Ken, Apr 06 '22 at 15:57
@Rena thank you for helping me out, your code actually help me in different way, I can actually use your code on my other queries, I really appreciate your time and effort solving my problem — Ken, Apr 06 '22 at 22:33

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

2 Answers2

Linked