Using SimpleMembershipProvider with my own database in a Database-First ASP.NET MVC4 project

Question

I have a database-first ASP.NET MVC 4 project and would like to use the SimpleMembershipProvider with the custom user_info table in my database. I am using Entitiy Framework 6 with SQL Server. I have scoured the internet and SO for a solution, but with no luck.

My user_info table looks like this:

[Id]            INT            IDENTITY (1, 1) NOT NULL,
[email]         NVARCHAR (MAX) NULL,
[first_name]    NVARCHAR (MAX) NULL,
[last_name]     NVARCHAR (MAX) NULL,
[internal_role] INT            NULL,
CONSTRAINT [PK_user_info] PRIMARY KEY CLUSTERED ([Id] ASC)

I have tried to follow the steps in How can I customize simple membership provider to work with my own database ASP.NET mvc 4, but I don't think this method will work for non-Code-First projects. I get the server error "Unable to find the requested .Net Framework Data Provider. It may not be installed." when I try to add the following code to the function SimpleMembershipInitializer().

WebSecurity.InitializeDatabaseConnection("ReservationSystemEntities", "user_info", "Id", "email", autoCreateTables: true);

Here is the relevant info from my Web.config file:

<configSections>
  <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=xxxxxx" requirePermission="false" />
</configSections>

<connectionStrings>
<add name="ReservationSystemEntities" connectionString="metadata=res://*/ReservationSystem.csdl|res://*/ReservationSystem.ssdl|res://*/ReservationSystem.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\v11.0;initial catalog=ReservationSystem;integrated security=True;user id=xxxxx;password=xxxxx;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>

I'm not even sure if this is possible, based on this answer: SimpleMembershipInitializer won't initialize

Based on this answer Using MVC 4 SimpleMembership with an existing database-first EF model I need to specify a connection string like this:

<add name="CONNECTION_STRING_NAME" connectionString="data source=SERVER;initial catalog=DATABASE;user id=USER;password=PASSWORD;" providerName="System.Data.SqlClient" />

I'm not sure how to convert my connection string to a connection string like this though. Isn't this type of connection string valid only when using Code-First?

Any help would be much appreciated.

Answer 1

I finally found the answer! See Using SimpleMembership with EF model-first for the original question/answer.

SimpleMembership can work with model/database first. Here is the solution:

1.InitializeSimpleMembershipAttribute.cs from MVC 4 Internet Application templete should look like this

namespace WebAndAPILayer.Filters
{
    [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
    public sealed class InitializeSimpleMembershipAttribute : ActionFilterAttribute
    {
        private static SimpleMembershipInitializer _initializer;
        private static object _initializerLock = new object();
        private static bool _isInitialized;

        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            // Ensure ASP.NET Simple Membership is initialized only once per app start
            LazyInitializer.EnsureInitialized(ref _initializer, ref _isInitialized, ref _initializerLock);
        }

        private class SimpleMembershipInitializer
        {
            public SimpleMembershipInitializer()
            {
                try
                {
                    WebSecurity.InitializeDatabaseConnection("ConnStringForWebSecurity", "UserProfile", "Id", "UserName", autoCreateTables: true);
                }
                catch (Exception ex)
                {
                    throw new InvalidOperationException("Something is wrong", ex);
                }
            }
        }
    }
}

2.Delete CodeFirst Classes from AcountModel.cs

3.Fix AccountCotroler.cs to work with your Model-first DbContext (ExternalLoginConfirmation(RegisterExternalLoginModel model, string returnUrl) method)

4.Define your "ConnStringForWebSecurity" connection string which is not same as that funky conn string for model-first db access, notice that we use provider System.Data.SqlClient not System.Data.EntityClient

 <connectionStrings>
         <add name="ModelFirstEntityFramework" connectionString="metadata=res://*/Context.csdl|res://*/Context.ssdl|res://*/Context.msl;provider=System.Data.SqlClient;provider
 connection string=&quot;data source=.\SQLEXPRESS;Initial
 Catalog=aspnet-MVC4;Integrated
 Security=SSPI;multipleactiveresultsets=True;App=EntityFramework&quot;"
 providerName="System.Data.EntityClient" />
         <add name="ConnStringForWebSecurity" connectionString="data source=.\SQLEXPRESS;Initial Catalog=aspnet-MVC4;Integrated
 Security=SSPI" providerName="System.Data.SqlClient" />
       </connectionStrings>