I'm working with a .NET 6 API project. I'm using Entity Framework Core with SQL Server.
I have 4 tables
User, Role, UserCredentialMapping, UserRoleMapping
as shown below as classes) for user management.
[Table("User")]
public class User
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long UserId { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
[Required]
[StringLength(150)]
public string Email { get; set; }
[StringLength(500)]
public string Address { get; set; }
[Required]
[StringLength(10)]
public string MobileNo { get; set; }
[Required]
public bool IsActive { get; set; } = false;
[Required]
public long CreatedBy { get; set; }
[Required]
public DateTime CreatedDate { get; set; } = DateTime.Now;
public virtual ICollection<UserCredentialMapping> UserCredentialMappings { get; set; } = new List<UserCredentialMapping>();
public virtual ICollection<UserRoleMapping> UserRoleMappings { get; set; } = new List<UserRoleMapping>();
}
[Table("Role")]
public class Role
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long RoleId { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
[Required]
public bool IsActive { get; set; } = false;
[Required]
public long CreatedBy { get; set; }
[Required]
public DateTime CreatedDate { get; set; } = DateTime.Now;
public virtual ICollection<UserRoleMapping> UserRoleMappings { get; set; }
}
[Table("UserCredentialMapping")]
public class UserCredentialMapping
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long UserCredentialId { get; set; }
[Required]
public long UserId { get; set; }
[Required]
[StringLength(50)]
public string UserName { get; set; }
[Required]
[StringLength(50)]
public string Password { get; set; }
[Required]
public bool IsActive { get; set; }
[Required]
public long CreatedBy { get; set; }
[Required]
public DateTime CreatedDate { get; set; } = DateTime.Now;
public long UpdatedBy { get; set;}
[ForeignKey("UserId")]
public virtual User Users { get; set; } = new User();
}
[Table("UserRoleMapping")]
public class UserRoleMapping
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long UserRoleMappingId { get; set; }
[Required]
public long UserId { get; set; }
[Required]
public long RoleId { get; set; }
[Required]
public bool IsActive { get; set; } = false;
[Required]
public long CreatedBy { get; set; }
[Required]
public DateTime CreatedDate { get; set; } = DateTime.Now;
[ForeignKey("UserId")]
public virtual User Users { get; set; } = new User();
[ForeignKey("RoleId")]
public virtual Role Roles { get; set; } = new Role();
}
And My DbContext class like below for the Code first approach.
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
{
}
public DbSet<User> Users { get; set; }
public DbSet<Role> Roles { get; set; }
public DbSet<UserCredentialMapping> UserCredentialMappings { get; set; }
public DbSet<UserRoleMapping> UserRoleMappings { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>().HasData(new List<User>
{
new User()
{
UserId = 1,
Name = "Mano",
Email = "mano@gmail.com",
Address = "Mano address",
MobileNo = "1234567890",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
},
new User()
{
UserId = 2,
Name = "Hari",
Email = "Hari@gmail.com",
Address = "Hari address",
MobileNo = "5432167890",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
}
});
modelBuilder.Entity<UserCredentialMapping>().HasData(new List<UserCredentialMapping>
{
new UserCredentialMapping()
{
UserCredentialId = 1,
UserId = 1,
UserName = "Mano",
Password = "Mano",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
},
new UserCredentialMapping()
{
UserCredentialId = 2,
UserId = 2,
UserName = "Hari",
Password = "Hari",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
}
});
modelBuilder.Entity<Role>().HasData(new List<Role>
{
new Role()
{
RoleId = 1,
Name = "Admin",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
},
new Role()
{
RoleId = 2,
Name = "User",
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
}
});
modelBuilder.Entity<UserRoleMapping>().HasData(new List<UserRoleMapping>
{
new UserRoleMapping()
{
UserRoleMappingId = 1,
UserId = 1,
RoleId = 1,
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
},
new UserRoleMapping()
{
UserRoleMappingId = 2,
UserId = 2,
RoleId = 2,
IsActive = true,
CreatedBy = 1,
CreatedDate = DateTime.Now,
}
});
base.OnModelCreating(modelBuilder);
}
}
While I'm trying to execute below EF query, getting 2 records.
_context.UserCredentialMappings
.Include(x => x.Users)
.ThenInclude(x => x.UserRoleMappings)
.SingleOrDefaultAsync(x => x.UserName == UserName && x.Password == Password);
But I need role details too, in the same object, so I have included Select(s => s.Roles) like this:
_context.UserCredentialMappings
.Include(x => x.Users)
.ThenInclude(x => x.UserRoleMappings)
.ThenInclude(x => x.Select(s => s.Roles))
.SingleOrDefaultAsync(x => x.UserName == UserName && x.Password == Password);
While executing the above, the EF query is throwing an error:
System.InvalidOperationException: 'The expression 'x.AsQueryable().Select(d => d.Roles)' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.'
I can't seem to understand want went wrong with this code.
I tried:
_context.UserCredentialMappings
.Include(x => x.Users)
.ThenInclude(x => x.UserRoleMappings)
.ThenInclude(x => x.Select(s => s.Roles))
.SingleOrDefaultAsync(x => x.UserName == UserName && x.Password == Password);
I'm expecting to get 2 rows with role details.
You should chain ThenInclude like this: var result = await ctx.UserCredentialMappings.Include(x => x.Users).ThenInclude(x => x.UserRoleMappings).ThenInclude(x => x.Roles).ToListAsync(); Each ThenInclude works on the result of the previous one.
The syntax you initially tried with .ThenInclude(x => x.Select(s => s.Roles)) is not valid, although similar syntax was previously used in full EF Include method to load multiple levels when ThenInclude was not available, e.g. Include(c => c.Users.Select(cc => cc.UserRoleMappings)). This is no longer valid in EF Core.