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; }

by curisosity what is the value of model.UserName, and which line exactly in which file throws the exception ? — tschmit
– tschmit, Commented Dec 3, 2013 at 17:27
@tschmit007 Non-null, this is not the issue, but thank you for the thought. The error is reproducable with the username "admin", "foo", "peter" and occurs whether or not the user exists - it never progresses to query the View. The error is on line fifty. — pwdst
– pwdst, Commented Dec 3, 2013 at 17:42
For reference it queries sys.databases for the database name, checks the tables (which are actually Views) using the SQL above, queries sys.databases again, attempts to select from [dbo].[__MigrationHistory] and [Users].[__MigrationHistory] (neither of which exist), queries sys.databases again twice, attempts both the __MigrationHistory tables again, and then gives up..... — pwdst
– pwdst, Commented Dec 3, 2013 at 17:48
@tschmit007 I have added the code for the UserManager to the question. The class names are slightly different as they reflect the project in question, but were simply renamed from ApplicationUser in the template MVC 5 project and as such the code should be functionally identical. — pwdst
– pwdst, Commented Dec 3, 2013 at 18:00

pwdst · Accepted Answer · 2013-12-04 09:50:54Z

2

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).

answered Dec 4, 2013 at 9:50

pwdst

13.9k4 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tschmit Over a year ago

congratulations... I was coming to it... nearly the same problem solved by setting initializer to null. But really weird, because I have a project with EF 5 view, an initializer, and all runs just fine !

tschmit · Accepted Answer · 2013-12-03 18:33:50Z

0

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; }
}

answered Dec 3, 2013 at 18:33

tschmit

7,8402 gold badges38 silver badges45 bronze badges

2 Comments

pwdst Over a year ago

Sorry, there was no change using this code. I can't see how it is different in any case as the constructor without parameters simply calls the constructor with the UserManager parameter passing a new instance of the UserManager class doesn't it?

pwdst Over a year ago

I have managed to resolve this thank you - see my answer. I wanted to thank you for your efforts in helping to solve the problem, especially as you were tired.

tschmit · Accepted Answer · 2013-12-04 14:12:32Z

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

Stefan · Accepted Answer · 2014-05-13 08:32:56Z

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.

Collectives™ on Stack Overflow

Code First Mapping to Database Views

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related