Code First Mapping to Database Views

Question

I have been asked to map the ASP.NET Identity classes to existing database Views for read operations, using Stored Procedures for CRUD. There are a number of StackOverflow Questions stating that is possible to map to views, also this question, this one and lastly this one.

I have mapped the classes to the Views as follows-

var applicationUser = modelBuilder.Entity<applicationUser>().HasKey(au => au.Id) //Specify our own View and Stored Procedure names instead of the default tables
    .ToTable("User", "Users").MapToStoredProcedures(sp =>
    {
        sp.Delete(d => d.HasName("spUser_Delete", "Users"));
        sp.Insert(i => i.HasName("spUser_Create", "Users"));
        sp.Delete(u => u.HasName("spUser_Update", "Users"));
    }); 

Where [Users].[User] is a SQL view retrieving data from the SQL table [Users].[tblUser].

Unfortunately I have had to leave at least one of the classes mapped to a table rather than View as Entity Framework generates the following SQL-

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
    AND (t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('Users.ApplicationRole','Users.User','Users.AuthenticationToken','Users.UserClaim','Users.UserLogin','Users.UserRole','Users.Department','Users.PasswordResetToken','Users.UserDepartment')
        OR t.TABLE_NAME = 'EdmMetadata')
go

Which returns zero as these are Views and not tables.

As a result any attempt to use the UserManager results in the exception-

Value cannot be null. Parameter name: source

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: source

Source Error:

Line 48: if (ModelState.IsValid)

Line 49: {

Line 50: var userAccount = await UserManager.FindByNameAsync(model.UserName);

Line 51:

Line 52: if (userAccount == null)

Manually changing the query to-

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE (t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('Users.ApplicationRole','Users.User','Users.AuthenticationToken','Users.UserClaim','Users.UserLogin','Users.UserRole','Users.Department','Users.PasswordResetToken','Users.UserDepartment')
        OR t.TABLE_NAME = 'EdmMetadata')
go

Returns the correct nine Views and would presumably not cause the error. Simply having one of the classes mapped to a table is sufficient to convince it the database is correct and to carry on as normal.

Is there any way I can persuade Entity Framework to remove the "Is a table" requirement, or assert that the tables do exist and therefore skip this step altogether?

Edit: Following a request, the code for the UserManager is included below-

AccountController.cs

[Authorize]
public class AccountController : Controller
{
    public AccountController()
        : this(new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationIdentityDbContext())))
    {

    }

    public AccountController(UserManager<ApplicationUser> userManager)
    {
        UserManager = userManager;
    }

    public UserManager<ApplicationUser> UserManager { get; private set; }

Answer 1

I have managed to resolve this problem by creating a custom Database Initializer which replaces the default CreateDatabaseIfNotExists initializer. The Codeguru article on Understanding Database Initializers in Entity Framework Code First was enormously helpful in helping me understand what was going on.

Code for solution-

using System.Data.Entity;

namespace NexGen.Data.Identity
{
    public class IdentityCustomInitializer : IDatabaseInitializer<ApplicationIdentityDbContext>
    {
        public void InitializeDatabase(ApplicationIdentityDbContext)
        {
            return; //Do nothing, database will already have been created using scripts
        }
    }
}

IdentityManager-

public class ApplicationIdentityDbContext: IdentityDbContext<ApplicationUser> 
{
    public ApplicationIdentityDbContext() : base("DefaultConnection")
    {
        Database.SetInitializer(new IdentityCustomInitializer());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        ...

As a result of this code there are no longer any probing queries by Entity Framework attempting to check if the database exists (and failing due to the assumption that tables, rather than views, were mapped) - instead the queries are immediately against the view attempting to retrieve the user data (and then executing a Stored Procedure in the case the initial action was a registration or otherwise updating the user).

Answer 2

please try

[Authorize]
public class AccountController : Controller
{
    public AccountController()        
    {
        InitAccountController(new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationIdentityDbContext())))
    }

    private InitAccountController(UserManager<ApplicationUser> userManager)
    {
        UserManager = userManager;
    }

    public UserManager<ApplicationUser> UserManager { get; private set; }
}

Answer 3

some more explanations:

in EF6 code we can see the following function (DatabaseTableChecker.cs):

        public bool AnyModelTableExistsInDatabase(
            ObjectContext context, DbConnection connection, List<EntitySet> modelTables, string edmMetadataContextTableName)
        {
            var modelTablesListBuilder = new StringBuilder();
            foreach (var modelTable in modelTables)
            {
                modelTablesListBuilder.Append("'");
                modelTablesListBuilder.Append((string)modelTable.MetadataProperties["Schema"].Value);
                modelTablesListBuilder.Append(".");
                modelTablesListBuilder.Append(GetTableName(modelTable));
                modelTablesListBuilder.Append("',");
            }
            modelTablesListBuilder.Remove(modelTablesListBuilder.Length - 1, 1);

            using (var command = new InterceptableDbCommand(
                connection.CreateCommand(), context.InterceptionContext))
            {
                command.CommandText = @"
SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
    AND (t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN (" + modelTablesListBuilder + @")
        OR t.TABLE_NAME = '" + edmMetadataContextTableName + "')";

                var executionStrategy = DbProviderServices.GetExecutionStrategy(connection);
                try
                {
                    return executionStrategy.Execute(
                        () =>
                            {
                                if (connection.State == ConnectionState.Broken)
                                {
                                    connection.Close();
                                }

                                if (connection.State == ConnectionState.Closed)
                                {
                                    connection.Open();
                                }

                                return (int)command.ExecuteScalar() > 0;
                            });
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
            }
        }

which corresponds to what you discover.

From this function we may says that there is a problem if, and only if, there are/is only views mapped to the model. In this case the initializer considers the database as Existing but Empty, and he tries to create the tables.

This creates problems as there are/is still views in the database with the same name as the tables the initializer wants to create.

So a work around seems to have at least one real table mapped to the context. No need for a custom initializer in this case.

I propose it as an issue : model only mapped to views

Answer 4

From my understanding and tests there is no need to implement an IDatabaseInitializer having an empty InitializeDatabase method like pwdst did.

From what I saw at Understanding Database Initializers in Entity Framework Code First, it is sufficient to call

Database.SetInitializer<ApplicationIdentityDbContext>(null);

when the application is initializing, or better say, before the first time the database will be accessed.

I would not put it inside the ctor of my DbContext class to avoid setting the initializer every time a DbContext instance is created. Instead, I would put it into the application's initialization method or as one of the first statements of the Main() method.

This worked fine for my application using Entity Framework 6.