Wednesday, 9 April 2014

Altering the schema name of ASP.NET Identity tables

I like keeping my database schema as clean and readable as possible, and one of the things I do is use a schema name to group related functionality.

I’ve been doing a fair bit of work with ASP.NET Identity recently, and one of the things was to add the generated tables for identity into their own schema. Moreover, I also wanted to change the name of the tables. This is a snap using Code First – all you need to do (in your IdentityDbContext derived class) is the following…

    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<DbUser>().ToTable("Users", "AUTH");
    modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles", "AUTH");
    modelBuilder.Entity<IdentityUserLogin>().ToTable("ExternalLogins", "AUTH");
    modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims", "AUTH");
    modelBuilder.Entity<IdentityRole>().ToTable("Roles", "AUTH");

Make sure you call the base class OnModelCreating method before including these changes. With that you’ll get a nice set of tables inside SQL server…

image

I realise that this will mean that I need to be careful when installing a new version of ASP.NET Identity (to ensure that the override still works), but I’d rather pay that price and have a ‘clean’ schema.

Monday, 7 April 2014

Optimizing ASP.Net Identity Database Access

I have to say I’m a bit obsessive about issuing SQL requests as they take time, and so the fewer requests I can make the better.

Recently I’ve installed the latest version of ASP.NET Identity (2.0.0.0) and whilst running SQL Profiler I noticed something odd being executed for every instance of my DbContext class. What I was expecting was one SQL statement (that being the one I was explicitly issuing), however what I actually saw in the profiler was three…


(1) SELECT Count(*) FROM sys.databases WHERE [name]=N'VPC'


(2) exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@Table',N'@Table nvarchar(11)',@Table=N'AspNetUsers'


(3) SELECT
[Extent1].[PublisherId] AS [PublisherId],
[Extent1].[ApiKey] AS [ApiKey],
[Extent1].[Name] AS [Name],
[Extent1].[Description] AS [Description]
FROM [VPC].[Publisher] AS [Extent1]
ORDER BY [Extent1].[Name] ASC

The one I was expecting was #3, the other two were unexpected and also unwanted. So I had to find out where they were coming from.


When I start hunting I always think “it’s my fault, I’ve caused this somehow” – and to be honest it almost always is caused by my code. But I was pretty sure that this wasn’t my code – or at least, it wasn’t cause by my code directly. What followed was an hour or so of fruitless searching in my project – I began with an EF profiler, no change, then I added some extra debug, no change, wired up some logging for Owin to see if it was one of the bits of middleware added into the pipeline, still no change, none was telling me what I needed to know.


I then started hunting on the web, and happened to find just what I needed on www.symbolsource.org, specifically the code for the IdentityDbContext class (which was where I’d hazarded a guess the problem was). I was (as most people will do) deriving from IdentityDbContext<TUser> which I’d assumed was benign but it wasn’t. It contains the following code in the default constructor…

  public IdentityDbContext(string nameOrConnectionString)
: this(nameOrConnectionString, true)
{
}

And looking at the next constuctor...

  public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
: base(nameOrConnectionString)
{
if (throwIfV1Schema && IsIdentityV1Schema(this))
{
throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
}
}

Not much going on there - or is there?. If you look at the code for the IsIdentityV1Schema function it reveals all - it's checking if the database exists, and it's also doing the column name lookup.


So, to remove the two additional SQL statements from your code, you need to pass false as the second parameter to the IdentityDbContext constructor…

  public class DbApplicationRepository 
: IdentityDbContext, IDbApplicationRepository
{
public DbApplicationRepository(string connectionString)
: base(connectionString, false)
{
}

...
}

Hopefully this will help someone!

Friday, 7 March 2014

Your hosting provider does not yet support ASP.NET 4.5…

Oh yes it does, I’ve been using .NET 4.5 for *ages* on Azure. Well, it feels like it anyway, but today I got this error after adding an extra couple of dependencies in from NuGet.

It was a simple fix – the stuff I’ve added (Cross Origin Requests) has updated my project but not set it to .NET 4.5.1 which is necessary for it to work. A quick trip to settings land…

image

And now it’s working again. Phew!

Sunday, 2 March 2014

Configuring ASP.NET Identity

[Note: It looks like email is now a first class entity in the database tables as of version 2.0.0.0 of the EF Identity provider]
Recently I’ve been tinkering around with ASP.NET Identity and, like many, have stumbled around in the dark due to lack of documentation – or lack of me being able to find the documentation. Rather than add to the problem I’ll add to the solution, by adding some much-needed documentation as I go.
I have a new project that I want to add ASP.NET Identity to, and after having let the default code whirr around for a while I need to extend it a little. What I want to do is as follows…
  • Add some extra columns to the ‘user’ table
  • Allow email addresses as usernames
I’m sure I may find more things I need to do as I go, but this will do for the moment. I like clear separation of concerns, so am splitting out the data context and associated classes into another assembly for now – that way I can get to grips with it better and also swap it out for something else if necessary. The shell of the solution contains the following classes…
    public abstract class ApplicationUser : IdentityUser
    {
        public string Email { get; set; }
    }



That’s my extended “user” class for now, I’ve added an email property as I need to record that in the database. In my scenario I have two types of user in the system – parents and children. A parent has a username and an email (which may both be the email address), whereas a child may not have an email address.
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext() : base("DefaultConnection")
        {
        }
    }



This DbContext class derives from the provided IdentityDbContext<TUser> class (that resides in the Microsoft.AspNet.Identity.EntityFramework assembly). You’ll notice that the constructor passes the database connection string “DefaultConnection” to the base class – this is the name used by ASP.NET Identity so I thought I’d use it too. Now to create a user and test what I’ve done so far. I created a simple Console app, created a database in SQL Express, and then fired up the following code…
    using (var ctx = new ApplicationDbContext())
    {
        var store = new UserStore<ApplicationUser>(ctx);
        var manager = new UserManager<ApplicationUser>(store);

        // Now create a user...
        ApplicationUser user = new ApplicationUser
        {
            UserName = "Test",
            Email = "me@here.com"
        };

        var result = manager.Create(user, "password");
    }



The UserManager class is provided by Microsoft.AspNet.Identity, and is a corollary to the Membership class of old – with a few new bits added for good measure, most notably a load of Task<> methods so you can do all of this stuff asynchronously.

The UserStore class provides an EF based implementation of the IUserStore<TUser> interface. So, if you want to store your users somewhere else, just cruft up an IUserStore<> derived class and you’re away - it’s a very small interface…
    public interface IUserStore<TUser> : IDisposable where TUser : Microsoft.AspNet.Identity.IUser
    {
        Task CreateAsync(TUser user);
        Task DeleteAsync(TUser user);
        Task<TUser> FindByIdAsync(string userId);
        Task<TUser> FindByNameAsync(string userName);
        Task UpdateAsync(TUser user);
    }



Not much to override there should you wish to store and retrieve users from somewhere other than the standard store. However, the user store will probably need to do a lot more than just the above, so there are a bunch of additional interfaces that a user store should consider implementing. These are as follows…
    IUserClaimStore<TUser>
    IUserLoginStore<TUser>
    IUserPasswordStore<TUser>
    IUserRoleStore<TUser>
    IUserSecurityStampStore<TUser>

All of these derive from IUserStore<TUser> and the EF provided UserStore class implements the lot. Then there’s a bunch of properties on UserManager that check if the specified user store implements these interfaces and acts accordingly. As an example, you might not want a ‘security stamp’ on your user record – you could create your own class and implement just the bits you need.

Now, UserManager has a bunch of other properties you can set which alter how it does it’s job too. And there’s a bit of strangeness to UserManager which caught me out for a while. I’ve been programming in .NET since 2000 so I’m not a stranger to the odd exception – but when I tried this code I didn’t get any…
    ApplicationUser user = new ApplicationUser
    {
        UserName = "me@here.com",
        Email = "me@here.com"
    };

    var result = manager.Create(user, "password");



OK, so that’s created a user – right? Not in my database it hasn’t. This caught me out for a while – there was no user in the database, but also no exception, so what’s up?

Well, after some head scratching I decided to look at the result from this call and saw this…
    "User name me@here.com is invalid, can only contain letters or digits."



Aha, so that’s my problem. But no exception – hmmm, this library is going to take some getting used to. I cannot remember the last time I explicitly went looking for a “success” code before. What is returned is an IdentityResult instance which includes a Succeeded property and also a bunch of errors in a string collection. Feels like I’m back in the good old days of COM, I’m sure there’s a reason for this design – but that reason was in a meeting probably 3 years ago and hasn’t dribbled down to us. I don’t have an issue with returning multiple errors, that’s fine, but I prefer the way that the Task<> classes do their errors by returning an AggregateException at the end, rather than needing me to plumb the depths of a return code. Oh well.

Permitting emails as usernames


OK, so now to the reason you’re probably here – how do I permit a username to hold an email?

The UserManager class has a UserValidator property which implements IIdentityValidator<TUser>, and the default one doesn’t permit any non-alpha characters in a username. So, you can replace the default, or just alter a property on it as follows…
    var manager = new UserManager<ApplicationUser>(store);
    var val = manager.UserValidator as UserValidator<ApplicationUser>;

    if (null != val)
        val.AllowOnlyAlphanumericUserNames = false;



That’s it – you can now insert usernames that are email addresses. And you could optionally replace the validator with one of your own as you might want to use a regular expression to validate a username, or ensure that the username is not the same as the password and so on. One other thing that the validator does is ensure that no user exists in the database with the same username, which of course your derived class would need to do too.

Adding extra columns to the AspNetUsers table


Due to the magic that is EF 4 (and now 6), by creating my ApplicationUser class above and then adding the Email property (and running against a new database), EF will create the database structure for me so I get a new table with an Email column. Sweet.

By default you get a schema that contains tables prefixed with AspNet, such as AspNetUsers, AspNetRoles and so on. If you want to alter these you can override OnModelCreating in your DbContext class to do something like the following…
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<IdentityUser>().ToTable("Users", "auth");
    }



As an example, the above will store users in a table called Users within the auth schema. Note – if you go down this route I would suggest you don’t call the base class OnModelCreating, and instead copy the code from the base class and alter as necessary (as there’s a few intricacies in there that could bite you).

Wrap Up


With all that in place, I can now store additional information against a user, and I can also use an email address as the username.

Friday, 17 January 2014

Painfully Slow Debugging in Visual Studio 2013 and Internet Explorer

I’ve been researching an issue today with debugging on VS2013 and IE. It’s a really weird one as the symptoms don’t make a great deal of sense, however I have found a cause and a temporary cure.

I have a simple website that I am debugging. If I startup the application and put a breakpoint within some code that gets called before any web requests are made all is fine.

I then click something on a page which drops me into the debugger. I was debugging some code as shown below…

Code Snippet
public ActionResult Login(string id)
{
    var ctx = Request.GetOwinContext();

    var props = new AuthenticationProperties
    {
        RedirectUri = Url.Action("Callback")
    };

    ctx.Authentication.Challenge(props, id);

    return new HttpUnauthorizedResult();
}

My breakpoint was on the first line, I was then stepping through the code and here’s the problem.

Stepping through took *ages*. The first click of F10 took about 9 seconds. Subsequent keypresses were 5 seconds or so. I had to figure this out.

As usual I turned to the internet and found a bunch of useless stuff, and then I found this which led me to this and so I download xPerf, ran some tests and came up with a graph that showed me it was Internet Explorer that was waiting which was strange – as I was inside Visual Studio, admittedly debugging something in IE. I then tried debugging with Chrome & Firefox, both were OK.

Whilst I debug I mostly use the keyboard, however during one of my sessions I used the mouse and clicked on the step commands on the toolbar, and strangely enough these worked as usual, it was single stepping with the keyboard that was causing the problem.

Unlike Bruce (the two links above) I couldn’t get any further with analysing the data (the graphs he drilled down to don’t seem to be available in the version I just downloaded), so I went back to the Net and asked again and this article popped up. LastPass – yep, I’m using it and yep, disabling it did the trick.

Now don’t get me wrong – I love LastPass and I wouldn’t be without it, but for the moment it’s off limits to Internet Explorer on my machine.

Hope this helps someone else!

Tuesday, 23 April 2013

Extending ServiceStack

I’ve been using ServiceStack on a couple of projects recently and I have to say I’m well impressed, it’s a superbly written set of assemblies, really well thought out, extremely fast and extensible too.

One of the things I’ve been struggling with however is how to inject an implementation of an interface into the pipeline before everything else. The API I’m exposing requires a couple of Http headers to be present, and not only do these need to be verified as present, but I want to use them to initialise one of the services (we’ll call it IMerchantTokenService) that other parts of the application need.

The general process I’d like to go through is as follows…

  • When an HTTP Request comes in, check for the headers
    • If they exist, create an implementation of IMerchantTokenService that contains these headers and stuff it into the IOC container. If not, throw an exception.
    • As IMerchantTokenService is now registered in the IOC container, when I resolve any other services that have this as a requirement it will be injected into all of these other services too.

Like most things, until you “get” the API you’re left wondering how to implement a feature, and once you know the API it’s second nature. This was one of those days.

After some fruitless tinkering I took a step back and thought – my headers requirement sounds a lot like an HTTP session (even though it’s used for something different). So I went and had a look at how ServiceStack deals with sessions and, sure enough, that was also the place where I could plug in my stuff. It’s easy when you know how.

The feature I was searching for is called a “Plugin”. Simply put, a plugin allows you to attach some extra processing to the request pipeline in ServiceStack, and you can get in before your service is instantiated and therefore affect which service(s) are available to import into your dependencies.

Writing a plugin

Creating a plugin is trivially easy – create a class that derives from IPlugin, override the Register method and bingo, you’re in business…

Code Snippet
/// <summary>
/// This class provides a plug-in that's called prior to any services being
/// executed. It verifies that the required header values are in place,
/// and registers a service so that other objects can access these
/// values later in the cycle
/// </summary>
public class MerchantTokenFeature : IPlugin
{
    /// <summary>
    /// Add a callback so that we get called when a request comes in
    /// </summary>
    /// <param name="appHost">The hosting provider</param>
    public void Register(IAppHost appHost)
    {
        if (null == appHost)
            throw new ArgumentNullException("appHost");

        _appHost = appHost;
        appHost.PreRequestFilters.Add(this.PreFilter);
    }

    /// <summary>
    /// Process the request headers
    /// </summary>
    /// <param name="request">The Http request</param>
    /// <param name="response">The Http response</param>
    private void PreFilter(IHttpRequest request, IHttpResponse response)
    {
        string apiKey = request.Headers[Headers.APIKey];
        string merchantIdentifier = request.Headers[Headers.MerchantIdentifier];

        if ((null == apiKey) || (null == merchantIdentifier))
            throw HttpError.Unauthorized(string.Format("You must send the {0} and {1} headers", Headers.APIKey, Headers.MerchantIdentifier));

        _appHost.Register<IMerchantTokenService>(new MerchantTokenService(apiKey, merchantIdentifier));
    }

    private IAppHost _appHost;
}

This plug-in hooks an additional entry into the PreRequestFilters collection, and so when a request comes in my PreFilter method is called and I can check the Http headers and then manually construct the additional service.

Registering your PlugIn

The only other requirement is to register the plug-in. In the Configure method of your AppHostBase derived class just add the plugin…

Code Snippet
public override void Configure(Funq.Container container)
{
    SetConfig(new EndpointHostConfig { ServiceStackHandlerFactoryPath = "api" });
    Plugins.Add(new MerchantTokenFeature());
}

That’s it – up and working. I can now import IMerchantTokenService wherever I need, and know that this dependency will be available by the time my service gets instantiated and it starts looking for it’s own dependencies.

Monday, 10 September 2012

Selecting Hierarchical data with WCF Data Services

In this post I’ll describe how you can use hierarchical data with WCF Data Services as it’s not immediately obvious how to do this, and there’s also an issue (which I believe to be a bug) that needs to be addressed in order for it to work.

In this example I have a simple table shown below, with a hierarchical link between employees. The keys are integers, defined as IDENTITY columns in SQL server. All the code for this example is attached to this post if you should wish to look into it further.

image

I’ve also created a stored procedure that selects people in a hierarchical manner using a CTE. This is as follows…

-- Create an SP that selects a hierarchical set of data
CREATE PROCEDURE SelectPersonHierarchy(@personId int) AS
SET NOCOUNT ON
;
WITH PersonHierarchy AS
(
SELECT PersonId, Name, ManagerId
FROM Person
WHERE PersonId = @personId
UNION ALL
SELECT p.PersonId, p.Name, p.ManagerId
FROM Person p
INNER JOIN PersonHierarchy ph
ON p.ManagerId = ph.PersonId
)
SELECT PersonId, Name, ManagerId
FROM PersonHierarchy
GO

So far so good. Next I created an EF model that includes the table above and the stored procedure too. Then I created a simple WCF data service as shown below – the extra method is used to expose the hierarchical stored procedure, so that clients can call it directly.


public class PersonService : DataService<PersonEntities>
{
public static void InitializeService(DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

// And set the protocol version used
config.DataServiceBehavior.MaxProtocolVersion =
DataServiceProtocolVersion
.V2;
}

[WebGet]
public IQueryable<Person> GetFullPerson(int personId)
{
return this.CurrentDataSource.
SelectPersonHierarchy(personId).AsQueryable();
}
}

Next I created the client for this service using Add Service Reference. Then I created a method that would call the GetFullPerson method on the server, the outline of which is shown below…


public Person GetFullPerson(int personId, bool makeItWork)
{
Person root = null;

this.CreateQuery<Person>("GetFullPerson")
.AddQueryOption("personId", personId).ToList().ForEach(
t =>
{
if (null == t.ManagerId)
root = t;
else
{
Person parent = this.FindManager(root, t.ManagerId.Value);
parent.Subordinates.Add(t);
}
});

return root;
}

This method converts the data returned from the WCF operation to a list, iterates through that list and builds the client Person hierarchy (using a small helper function FindManager which, given a tree of objects, looks for the manager of the current person).


So, with all that in place we can write some unit tests to test the service. In my tests I’ve just used Boss and Employee as my two people. First off we’ll insert a Boss and an Employee & link them together…


Uri geller = new Uri("http://localhost:11341/PersonService.svc");
ServerService.PersonEntities context =
new ServerService.PersonEntities(geller);

// Insert hierarchical data to test with
var boss = new ServerService.Person { Name = "Boss" };
context.AddToPeople(boss);

var employee = new ServerService.Person { Name = "Employee", Manager = boss };
context.AddToPeople(employee);

// Important to add this - otherwise EF won't record the parent/child
// link in the database
context.AddLink(boss, "Subordinates", employee);

// Now save this to the server
context.SaveChanges();

Apologies for the awful pun in the Uri variable, I just can’t help myself. Here I create a boss, add an employee and then save the changes to the database. The important thing to mention is that you must also call context.AddLink() to ensure that there is a link between the parent and child objects. Here “Subordinates” is the name of the EF navigation property that is mapped from the parent of the relationship to the child. After saving these changes in the database you’ll see the following in the DB (Id’s will differ)…


image


OK, so far so good, we can create and save data to the server.


The Problem


The next thing I wanted to do was to verify that I could reload a person (in a hierarchical manner). So, my unit test was basically as follows…



  • Create a Boss and an Employee

  • Save these to the database

  • Reload these and test that all data was correct, including the link between employee and boss. This is where it all started to go wrong.

The reload code wasn’t working properly, and I tracked it down to the ManagerId field (after a fair amount of time). First I used Fiddler to log what was coming down from the server. My request looked OK…


image


And the data coming back from the server also looked OK…


image


However in my code that was looping through the data that came from the server the ManagerId was always null, for each and every row returned from the server.


In another test however it was fine – what gives?


The Solution


My two unit tests were identical – well, nearly. One verified what it got inline, another called a method to do that verification and the crucial difference was that the one that worked used a new service context. So, if you create a hierarchy of Boss & Employee on the client, save changes and reload (using the same client proxy), the hierarchical link is broken. If however you create a new client proxy and call the server everything works as expected.


My solution to this was to include the following in the method that calls the server…


public Person GetFullPerson(int personId)
{
Person root = null;

var currentMergeOption = this.MergeOption;
this.MergeOption = MergeOption.OverwriteChanges;

this.CreateQuery<Person>("GetFullPerson")
.AddQueryOption("personId", personId).ToList().ForEach(
t =>
{
if (null == t.ManagerId)
root = t;
else
{
Person parent = this.FindManager(root, t.ManagerId.Value);
parent.Subordinates.Add(t);
}
});

this.MergeOption = currentMergeOption;

return root;
}

This ensures that the data read from the server overwrites the data on the client, and in this instance that’s enough for the ManagerId to be set correctly rather than being Null.


Example Code


The following is a link to a .zip file that contains the same code. You’ll find a database script in the EFHierarchyProblem project called SchemaAndLogic.sql. Run this against your database.


You should also update the database connection strings in each project to reference your database. There are 3 projects in the solution, a class library that contains the EF model and the service, a Web project that exposes that service, and a Test project that runs the unit tests. When you run the tests you should see the following in the test results window…


image


The Test_ServerService_WillFail should work but doesn’t, as the ManagerId is overwritten when reloading the data. The _WillWorkSameContext test uses the merge option to overwrite local data to ensure that the ManagerId isn’t overwritten, and the _WillWorkNewContext test inserts data but then requests it from a new client proxy which will always work.


The .zip can be downloaded from here.