Entity Framework 6 Add nested child object in Parent object in LINQ Query

.net-core entity-framework-6 linq

Question

I am working on .NET Core application; Entity Framework 6

I need to add child object which is collection is same parent object in LINQ query not lambda expression

User - parent entity

public class UserDataModel
{
    public UserDataModel()
    {
        this.Roles = new HashSet<UserRoleDataModel>();
    }

    public Guid Id { get; set; }

    public Guid IdentityProviderID {get; set;}  

    public ICollection<UserRoleDataModel> Roles { get; set; }
}

Child-entity

public class UserRoleDataModel
{
    public Guid UserId { get; set; }

    public UserDataModel Users { get; set; }

    public Guid RoleId { get; set; }

    public RoleDataModel Roles { get; set; }


}

LINQ

var uu = (from _user in db.Users
            join _userRole in db.UserRoles on _user.Id equals _userRole.UserId
             where _user.IdentityProviderID == AureID
             select new {
                        _user,
                        _userRole 
                        }

This is far I reached but Roles are collection where sub-query to select role separate are IQueryable hence throw conversion/ casting error

 var uu = (from _user in db.Users
                          where _user.IdentityProviderID == AureID
                          select new UserDataModel
                          {
                              Id = _user.Id,
                              IdentityProviderID = _user.IdentityProviderID,
                              Roles = (from b in db.UserRoles where b.UserId == userId select b)
                          }
                         ).ToList();
1
0
1/31/2018 9:44:47 AM

Accepted Answer

Addition after comment at the end

It seems you have a true one-to-many relationship between UserData and UserRoles: Every UserData has zero or more UserRoles, and every UserRole belongs to exactly one UserData.

Your class definitions deviate from the Entity Framework Code First conventions. If you'd had your classes configured like a standard Entity Framework One-to-many relationship, entity framework would recognize that you'd meant to design a one-to-many and know automatically when a join is needed in your queries. Reconsider rewriting your classes:

class UserDataModel
{
    public GUID Id {get; set;}   // primary key

    // every UserDataModel has zero or more UserRoleDataModels:
    public virtual ICollection<UserRoleDataModel> UserRoleDataModels {get; set;}

    ... // other properties
}
class UserRoleDataModel
{
    public GUID Id {get; set;}   // primary key

    // every UserDataRoleModel belongs to exactly one UserDataModel
    // using foreign key:
    public Guid UserDataModelId {get; set;}
    public virtual UserDataModel UserDataModel {get; set;}

    ... // other properties
}

The most important change is that I made the references between your UserDataModel and your UserDataRoleModels virtual. I changed the name of the foreign key, so Entity Framework knows without any attribut / fluent API that you meant to configure a one-to-many.

Back to your question

Given the Id of a UserDataModel, give me the UserDataModel with all its UserDataRoleModels

After you've rewritten your classes with the proper virtual ICollection, your query would be easy:

var result = myDbContext.Users
    .Where(user => user.Id == AureId)
    .Select( user => new
    {
        User = user,
        Roles = user.Roles.ToList(),
    });

This requirement has the shortcoming that you tend to retrieve to much data. One of the slower parts when querying a database is the transfer of the queried data to your local process. Therefore it is wise not to fetch any more data than you want.

You plan to query a complete UserData with its complete UserRole objects. if the UserData that you fetch has Id XYZ, and it has 50 UserRole objects, then every one of its UserRole object would have a UserDataId with a value of XYZ, thus transferring it 50 times more than needed.

Therefore I advise you to transfer only the data that you actually plan to use:

 var result = myDbContext.Users
    .Where(user => user.Id == AureId)
    .Select( user => new
    {
        // from the user take only the data you plan to use
        Id = user.Id, // probably not, you know that it is AureId
        Name = user.Name,
        ... // other user properties you plan to use

        UserRoles = user.UserRoleDataModels.Select(roleData => new
        {
             // again: only the role data you plan to use
             Name = roleData.Name,
             Type = roleData.Type,
             ... // other properties

             // certainly not this one:
             UserDataId = roleData.UserDataId,
        })
        .ToList(),
    });

This way your query is much faster, you'll only fetch the data you want, you can change the names of fields depending on your needs, and you can add new values if desired, composed from values within UserData

Because of your virtual ICollection you don't need to perform the join. Entity Framework knows your one-to-many and understands that your use of the ICollection should result in an inner join followed by a Group By

Summarized: if you think you'll need a join in entity framework, think twice, you'll probably be able to do it simpler by using the ICollection or the reference to the parent

Addition after comment

if we add role has definition table, how to I add further nested object .

I'm not sure what this means. I think you mean: what to do if a role has a definition table, or what to do if a role has zero or more definition tables.

class UserRoleDataModel
{
    public GUID Id {get; set;}   // primary key

    // every UserDataRoleModel belongs to exactly one UserDataModel
    // using foreign key:
    public Guid UserDataModelId {get; set;}
    public virtual UserDataModel UserDataModel {get; set;}

    // every role has exactly one DefinitionTable:
    public DefinitionTable DefinitionTable {get; set'}
}

 var result = myDbContext.Users
    .Where(user => user.Id == AureId)
    .Select( user => new
    {
        // from the user take only the data you plan to use
        ...

        UserRoles = user.UserRoleDataModels
           .Where(role => role... == ...)
           .Select(role => new
           {
               RoleDefinition = role.RoleDefinitiona,
               ...
           }),
    });

If your Role has zero or more RoleDefinitions, do the same as you did with Users and Roles: add the virtual ICollection and add the virtual Reference and foreign key and use the collections in a Select statement.

3
1/31/2018 1:09:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow