CODEWITHSUNDEEP
c#sql-serverentity-frameworkasp.net-identity
Patrick
Patrick

Reputation: 2781

Unable to access entity data from a Role Id with Microsoft Identity

I am trying to access the information regarding schedule requests of a workshop based on a Role Id.

The Schedule can be made by 2 Roles (Public and Internal) and I need to get the count in a timeframe based on a Role Id.

The problem is that the query generated is searching in the table "FROM [dbo].[IdentityUserRoles]" and I have tables called "AspNetUserRoles" so I get an error of "invalid object name [dbo].[IdentityUserRoles]".

My Schedule Request Model:

[Table("ScheduleRequests")]
public class ScheduleRequest
{
    [Key]
    public int Id { get; set; }
    public DateTime DateCreated { get; set; }
    public string User_Id { get; set; }
    [ForeignKey("User_Id")]
    public virtual ApplicationUser User { get; set; }
}

My AplicationUser Model:

public class ApplicationUser : IdentityUser
{
    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);

        // Add custom user claims here
        return userIdentity;
    }
}

My Method to get the count:

public int GetScheduleRequestsByUserRoleByTimeframe(string roleId, DateTime dateFrom, DateTime dateTo)
{
    dateTo = dateTo.AddDays(1);

    var dbContext = new DbContext();

    var query = dbContext.ScheduleRequests.Where(s => s.DateCreated >= dateFrom && s.DateCreated < dateTo);

    if(!string.IsNullOrEmpty(roleId))
    {
        query = query.Where(s => s.User.Roles.Select(r => r.RoleId).Contains(roleId));
    }
    else
    {
        query = query.Where(s => s.User_Id == null);
    }

    return query.Count();
}

My DbContext:

public DbContext() : base("DB") 
{
    // Tells Entity Framework that we will handle the creation of the database manually for all the projects in the solution
    Database.SetInitializer<DbContext>(null);
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // I have inserted this code base on a solution I found when I started to get Id Errors
    modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
    modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
    modelBuilder.Entity<IdentityUserRole>().HasKey(r => new { r.RoleId, r.UserId });
}

    public DbSet<ScheduleRequest> ScheduleRequests { get; set; }
}

I also have another Context create by the Microsoft Identity but I don't know for what it's used:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext() : base("DB", throwIfV1Schema: false) { }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}

Upvotes: 0

Views: 216

Answers (1)

Sam FarajpourGhamari
Sam FarajpourGhamari

Reputation: 14741

Simply remove DbContext class from your codebase and use only ApplicationDbContext class as the context. Since you can add extra entities in ApplicationDbContext, you don't need another one in your application. Consider this:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext() : base("DB", throwIfV1Schema: false) { }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    // extra entities added here 
    public DbSet<ScheduleRequest> ScheduleRequests { get; set; }
}

Upvotes: 2

Related Questions