Mark Johnson

Software EngineerLucky HusbandProud FatherHopeless NerdAmateur BuilderDisney FanaticNeglectful Blogger

Exorcising Entity Framework from ASP.NET Identity

Update! This post is for ASP.NET Identity 1.0. For ASP.NET Core Identity 2.0, check out this updated post.

ASP.NET Identity is Microsoft’s fourth and by far its best iteration of an authentication/authorization system that’s built right into ASP.NET. It’s super flexible and extensible, yet easy to use and ready to go right out of the box.

That ease of use is achieved in part thanks to a default backing store provider based on Entity Framework that comes already set up in the template. That’s fine and dandy for quickly and painlessly getting from File -> New Project to a web application with a fully operational membership system. However, as you begin to develop that web application you might find yourself wanting that ASP.NET Identity goodness without the baggage that comes with Entity Framework.

Now, I’m not knocking Entity Framework. It’s a fine ORM that has plenty of good uses. More often than not however, whether building small websites or large-scale web applications, I find myself choosing a Micro ORM like Dapper or Massive over a more traditional ORM like Entity Framework or NHibernate to power my data layer.

Getting Started

Let’s start by opening up Visual Studio 2013 and creating a new project of type “ASP.NET Web Application” and choosing to use the standard MVC template with “Individual User Accounts” as the authentication type.

New ASP.NET Project

This will start us off with a fully functioning ASP.NET MVC5 web application including all of the models, views and controllers needed to manage user accounts with ASP.NET Identity. This also includes Entity Framework’s Identity providers that provide a backing store for ASP.NET Identity to store all of the data it needs to operate in an EF code-first-generated local database.

You can hit F5 right here and launch a completely functional website without writing a single line of code. But where’s the fun in that?

Database

Out of the box, Entity Framework was kind enough to provide us with a database that we don’t have to design, configure or implement. Pretty nice. So without Entity Framework, we’re going to need to create our own database the old fashioned way. Like cavemen.

For this demo, I created a very simple database with just two tables:

CREATE TABLE [dbo].[Users]
(
    [UserId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [UserName] VARCHAR(MAX) NOT NULL, 
    [PasswordHash] VARCHAR(MAX) NULL, 
    [SecurityStamp] VARCHAR(MAX) NULL 
)

CREATE TABLE [dbo].[ExternalLogins]
(
    [ExternalLoginId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [UserId] UNIQUEIDENTIFIER NOT NULL, 
    [LoginProvider] VARCHAR(MAX) NOT NULL, 
    [ProviderKey] VARCHAR(MAX) NOT NULL, 
    CONSTRAINT [FK_ExternalLogins_Users] FOREIGN KEY ([UserId]) REFERENCES [Users]([UserId])
)

These tables have the minimum set of columns required to support the ASP.NET Identity functionality included by default in the template we chose, which is user authentication that uses local usernames/passwords and/or third-party accounts like Google or Facebook.

Models

For user info, ASP.NET Identity has a very generous BYOP policy (bring your own POCO). All it asks is that our POCO implements its IUser interface. I created one that matches my Users table:

public class User : IUser
{
    public Guid UserId { get; set; }
    public string UserName { get; set; }
    public string PasswordHash { get; set; }
    public string SecurityStamp { get; set; }

    string IUser.Id
    {
        get { return UserId.ToString(); }
    }
}

The IUser interface only requires two properties. The first is “string UserName { get; set; }”, which fits my database table perfectly. It also requires a “string Id { get; }” and that doesn’t quite fit. The primary keys in my database are uniqueidentifiers that follow my own naming convention. So I made the primary key in my POCO match my database and then explicitly implemented a wrapper property to satisfy the needs of the IUser interface.

UserStore Provider

Ah, and now the fun part. ASP.NET Identity needs a way to interact with our POCOs and talk to our database. To do this, we need to provide a class that wires everything up. There are four interfaces that we’ll implement in the process.

IUserStore

This interface is used by ASP.NET Identity to persist and retrieve user accounts to and from our database. For my implementation I used Dapper, an open source micro ORM created by the smart folks over at StackOverflow. It provides some handy extensions on any IDbConnections that make interacting with your database using models or dynamic objects super easy.

IUserLoginStore

Because our demo includes support for logging in without a username and password (using a third party like Google or Facebook), we need to give Identity a way to persist external login information in addition to user accounts. We do that through the IUserLoginStore interface, which I implemented using Dapper again.

IUserPasswordStore and IUserSecurityStampStore

One of the nice things about ASP.NET Identity is that it doesn’t make any assumptions about the way in which your solution handles the storage and retrieval of any user account information. This includes the password hash and security stamp, which I chose to store right in my Users table. So the implementation of these interfaces was simple: I just access the password hash and security stamp properties on my User POCO and let Dapper do the rest.

public class UserStore : IUserStore<User>, IUserLoginStore<User>, IUserPasswordStore<User>, IUserSecurityStampStore<User>
{
    private readonly string connectionString;

    public UserStore(string connectionString)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
            throw new ArgumentNullException("connectionString");

        this.connectionString = connectionString;
    }

    public UserStore()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    }

    public void Dispose()
    {

    }

    #region IUserStore
    public virtual Task CreateAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.Factory.StartNew(() => {
            user.UserId = Guid.NewGuid();
            using (SqlConnection connection = new SqlConnection(connectionString))
                connection.Execute("insert into Users(UserId, UserName, PasswordHash, SecurityStamp) values(@userId, @userName, @passwordHash, @securityStamp)", user);
        });
    }

    public virtual Task DeleteAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                connection.Execute("delete from Users where UserId = @userId", new { user.UserId });
        });
    }

    public virtual Task<User> FindByIdAsync(string userId)
    {
        if (string.IsNullOrWhiteSpace(userId))
            throw new ArgumentNullException("userId");

        Guid parsedUserId;
        if (!Guid.TryParse(userId, out parsedUserId))
            throw new ArgumentOutOfRangeException("userId", string.Format("'{0}' is not a valid GUID.", new { userId }));

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                return connection.Query<User>("select * from Users where UserId = @userId", new { userId = parsedUserId }).SingleOrDefault();
        });
    }

    public virtual Task<User> FindByNameAsync(string userName)
    {
        if (string.IsNullOrWhiteSpace(userName))
            throw new ArgumentNullException("userName");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                return connection.Query<User>("select * from Users where lower(UserName) = lower(@userName)", new { userName }).SingleOrDefault();
        });
    }

    public virtual Task UpdateAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                connection.Execute("update Users set UserName = @userName, PasswordHash = @passwordHash, SecurityStamp = @securityStamp where UserId = @userId", user);
        });
    }
    #endregion

    #region IUserLoginStore
    public virtual Task AddLoginAsync(User user, UserLoginInfo login)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        if (login == null)
            throw new ArgumentNullException("login");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                connection.Execute("insert into ExternalLogins(ExternalLoginId, UserId, LoginProvider, ProviderKey) values(@externalLoginId, @userId, @loginProvider, @providerKey)",
                    new { externalLoginId = Guid.NewGuid(), userId = user.UserId, loginProvider = login.LoginProvider, providerKey = login.ProviderKey });
        });
    }

    public virtual Task<User> FindAsync(UserLoginInfo login)
    {
        if (login == null)
            throw new ArgumentNullException("login");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                return connection.Query<User>("select u.* from Users u inner join ExternalLogins l on l.UserId = u.UserId where l.LoginProvider = @loginProvider and l.ProviderKey = @providerKey",
                    login).SingleOrDefault();
        });
    }

    public virtual Task<IList<UserLoginInfo>> GetLoginsAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                return (IList<UserLoginInfo>)connection.Query<UserLoginInfo>("select LoginProvider, ProviderKey from ExternalLogins where UserId = @userId", new { user.UserId }).ToList();
        });
    }

    public virtual Task RemoveLoginAsync(User user, UserLoginInfo login)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        if (login == null)
            throw new ArgumentNullException("login");

        return Task.Factory.StartNew(() =>
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
                connection.Execute("delete from ExternalLogins where UserId = @userId and LoginProvider = @loginProvider and ProviderKey = @providerKey",
                    new { user.UserId, login.LoginProvider, login.ProviderKey });
        });
    }
    #endregion

    #region IUserPasswordStore
    public virtual Task<string> GetPasswordHashAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.FromResult(user.PasswordHash);
    }

    public virtual Task<bool> HasPasswordAsync(User user)
    {
        return Task.FromResult(!string.IsNullOrEmpty(user.PasswordHash));
    }

    public virtual Task SetPasswordHashAsync(User user, string passwordHash)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        user.PasswordHash = passwordHash;

        return Task.FromResult(0);
    }

    #endregion

    #region IUserSecurityStampStore
    public virtual Task<string> GetSecurityStampAsync(User user)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        return Task.FromResult(user.SecurityStamp);
    }

    public virtual Task SetSecurityStampAsync(User user, string stamp)
    {
        if (user == null)
            throw new ArgumentNullException("user");

        user.SecurityStamp = stamp;

        return Task.FromResult(0);
    }

    #endregion
}

What’s with “connection.Execute” and “connection.Query”?

If you copied and pasted the class above into your project you may have noticed that the “Execute” and “Query” extensions I call on the “connection” object in several methods are not found. These are extensions provided by Dapper, the micro ORM I mentioned earlier. It’s free, open source and very well supported. You can get it on NuGet just by typing “Dapper” in the search box. Don’t forget to include the “using Dapper;” directive at the top of your class file.

Dapper in NuGet

If you don’t want to use Dapper, this solution will work just fine. However you’ll need to replace my Dapper method calls with straight-up ADO.NET or your favorite ORM.

Goodbye Entity Framework

We’re now ready to remove Entity Framework from our project. You can start by removing the “ApplicationUser” and “ApplicationDbContext” classes. We’ve got our own user model now and have no need for an Entity Framework DbContext.

Next, we have just a few changes to make to the Account Controller. First, because we’re not using the provided “ApplicationUser” model instead using our “User” model, we need to replace all references in the Account Controller to the ApplicationUser class with references to our new User class.

We also need to modify the parameterless constructor to instantiate our newly created UserStore instead of Entity Framework’s UserStore. It should look like this:

public AccountController()
    : this(new UserManager<User>(new UserStore()))
{
}
public AccountController(UserManager<User> userManager)
{
    UserManager = userManager;
}

And finally, we can remove the references to EntityFramework and EntityFramework.Identity from our project.

If you’d like to see a working example of this technique, I’ve uploaded a solution complete with database to a GitHub project here: https://github.com/mark-j/DapperIdentityDemo


41 Comments

  • Thanks for creating this tutorial. I love Dapper and this will come in handy.

  • this very very helpful! big thanks!!

  • This is great, however have you had any attempts at replicating this using the owin2 that comes with VS2013 Update 2?

    • I haven’t, but that’s a great idea. Perhaps in a future blog post!

      • Mark, great article, but unfortunately dated. Hope you can tackle this with the new asp.net Identity 2.0 which is a significant update.

        Thanks

        • Great article but would also like to see with Identity v2. Trying to figure this out for myself now…

  • You man literally saved my *** 🙂
    Is there any chance that we can see Roles implementation?

    Thank you

    • Glad this was helpful! :-).

      Implementing this for Roles should be very similar. You just need to also implement IRoleStore and IUserRoleStore.

      Then, not unlike how we pass our custom UserStore to a new instance of UserManager in this example, you just need to spin up a RoleManager as well, passing your newly created implementation of IRoleStore and IUserRoleStore.

  • Thanks, it is a great job !!

    Can we have the same with RoleStore please?

  • Thank for this! Very nice…wanted to point out a type in user IUserStore. The DeleteAsync method has delete misspelled in the SQL query…

    • Thanks, glad you liked it. And thanks for the heads up — fixed the typo! 🙂

  • Great tutorial, thanks. I don’t suppose you have done the same with WebAPI 2 have you? Trying to do this myself with reference to your tutorial, but not getting very far.

    In particular stuck on this line in Startup.Auth.

    UserManagerFactory = () => new UserManager(new UserStore());

    Cheers

    • Thanks! I don’t have a Web API 2 version of this tutorial but plan to write one soon.

  • First, I want to thank you for your article and demo. It has really helped me out. I have an issue though that is driving me nuts and I cannot figure it out. I have posted the question on StackOverflow here:

    http://stackoverflow.com/questions/25640658/usermanager-findasync-not-getting-called-in-custom-userstore/25650245?iemail=1&noredirect=1#25650245

    The CreateAsync Method never gets called on my custom UserStore. I have even renamed all my classes to prevent ambiguity. My UserManager class looks like this:

    public class SomSiteUserManager : UserManager
    {
    public SomSiteUserManager()
    : base(new SomSiteUserStore())
    {
    this.PasswordHasher = new SQLPasswordHasher();
    }

    }

    The CreateAsync fires on the Default UserStore Base. I’m not sure what I’m missing, but if you have any idea, please let me know.

    Thanks

    • Hi Paul. Sorry it took me so long to reply. Looks like your question is no longer on Stack Overflow. Did you ever end up figuring this out?

  • Thanks,
    that helped me with owin and custom tables identity on WebApi2 and token auth.

  • Thanks a lot for this article. But I don’t understand one thing: I added breakpoint to UserStore class CreateAsync method and user instance in this method already has SecurutyStamp and PasswordHash properties. Where are they set?

  • Thank you.
    Just started out with MVC 5 and like the MVC-concept. And one thing that irritated me from the start was the magically implementation of the ASP Identity and the secret User-database.

    My goal is to eliminate the evil EF6 and the complicated way of interacting with the database. Why wrap a clearly intended T-sql statement into a bunch of complex lambdas/ linq. It has always puzzled me that people will write such instead of T-sql.

  • […] I have a custom implementation of the ASP.NET Identity base, using Dapper instead of Entity Framework largely from the tutorial here: http://blog.markjohnson.io/exorcising-entity-framework-from-asp-net-identity/. […]

  • Thanks,

    Very useful!

  • Thank you !!!

  • You just saved my day… thank you so much!!!

  • Thanks a lot for this useful post!

  • Hi there,
    stumbled upon your code as i was looking for a similar implementation. I am sorry to say that you implementation of all the async methos is plainly wrong.nothing you have done is async.

    • You’re absolutely right — I’m not doing anything async. That was done purposefully though. The ASP.NET Identity interfaces require async method signatures, but to keep things simple for this demo I did everything synchronously. 🙂

  • Can I use the same concept in Web API. I want to implement a token based authentication api bur could not find an example that does not use EF

  • Can I use the same concept in Web API.

    I want to implement a token based authentication API but could not find an example that does not use Entity Framework.

    • You could certainly decouple ASP.NET Identity from MVC and use it to authenticate API calls to Web API. But Identity is really geared toward authenticating users via passwords or third-party OAuth credentials, neither of which are preferable when working directly with an API. It very much depends on the nature of your consumers but you might want to consider building an OAuth provider yourself using something like DotNetOpenAuth.

  • You wouldn’t happen to have an updated version of this to work with ASP.NET 5, MVC 6, Identity 3.0?

  • Thanks for this helpful tutorial. I’m working on a proof of concept which for various reasons won’t be using MVC either. Any thoughts pointers to getting this to work in plain WebForms etc? Many thanks.

  • Thanks for this, I am new to Dapper and this example was spot on!

  • Thanks for the Great article. I was just wondering if you were still planning on doing this same thing with Identity 2.0 it contains an Application SignInManager, that was not in the code from this older article. I could eventually figure out what changes I would need to make for 2.0, but always nice to have a blog post to follow.

  • Hi Mark,
    Do you still have plans to update this article with ASP.NET core implementation?

    Thanks.

  • Thanks a lot. There is not much help available on web regarding DAPPER and ASP.NET Identity. Big thanks to you. I really don’t know why microsoft isn’t releasing any default tutorials of identity with ADO.NET Library or with Repository Patterns.


Leave a comment

Your email address will not be published. Required fields are marked *