How can I change the data type of UserID in the default account model made by Visual Studio 2012?

Question

I was making an internet MVC 4 application in Visual Studio 2012 and I wanted to add users using SingleMembership. At first I saw that the users database was not exactly where I wanted them to be, they were on a SQL/Express database and I want them on a SQL/Server I have already created for all the models in my application. So what I did is modify the connection string created by Visual Studio 2012 for my database which was:

<add name="Test2_Entities" connectionString="metadata=res://*/Models.Model.csdl|res://*/Models.Model.ssdl|res://*/Models.Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=FERNANDO-PC\SQLSERVER;initial catalog=Test2;persist security info=True;user id=****;password=****;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

and then I realized I needed 2 connection strings because the metadata part was giving some errors and the provider name change it to "System.Data.SqlClient", so the new connection string is really the first one but with this changes:

<add name="Test2_Entities2" connectionString="data source=FERNANDO-PC\SQLSERVER;initial catalog=Test2;persist security info=True;user id=****;password=****;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

In otder to have the users tables created in my database, I also changed a line in the InitializeSingleMemebershipAttribute.cs file in the Filters folder (to be precise, the line 41) from:

WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: true);

to:

WebSecurity.InitializeDatabaseConnection("Test2_Entities2", "UserProfile", "UserId", "UserName", autoCreateTables: true);

and other line in the AccountModels.cs which is in the Models folder from:

public UsersContext() : base("DefaultConnection")

to:

public UsersContext() : base("Test2_Entities")

Whit that the default tables which control the memberships, roles, OAuth and those things, are created in the database I want. (This is the background of the problem, and thought that some people would find it helpful)

The problem is that when the tables are created in my SQL database, there are columns that are not in the datatypes I defined in the AccountModels.cs and the additional fields are not created. More specifically, in the AccountModels.cs I have this class:

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Guid UserId { get; set; }
    public string UserName { get; set; }
    public string RandomString { get; set; }
}

and in the tables created, the UserID gets an int which when I try to change in I get an error:

Conversion from 'int' to 'uniqueidentifier' is not supported on the connected database server.

and also, the RandomString string does not appear on the UserProfiles table.

The answer to this question: "How do I manage profiles using SingleMembership" stackoverflow question is really helpful, but I can't change the fields and datatypes like the answer proposes.

Is there something I'm missing? or something I should set before doing this changes?

Answer 1

You cannot change the UserId type because it is used as a foreign key to other tables that are also generated in this database. If you successfully get it to change this column in the database you will get the following error:

UserProfile.UserId' is not the same data type as referencing column 'webpages_UsersInRoles.UserId' in foreign key 'fk_UserId'

When I tried this some of the tables were created and as you can see other tables use the UserId to link to the UserProfile table.

But as you can also see from this snapshot I was able to add the custom column called RandomString. I was baffled by your comment that you could not change the UserProfile object and have it reflected in the table. I have seen numerous complaints about this on this site so I tried messing around with making changes to see if I could reproduce the problem. When I was working with this example and I was able to change UserId back to an int so that it would work and even add another property/column. It also worked as you can see in this snaphsot.

But then I opened an older project that had a custom UserProfile and tried modifying it and it would not reflect the changes in the database. It was stuck with the last model I had used (a custom model that differed from the one generated by the MVC 4 template) and would not change even if I deleted the database or modified the database name. This MVC 4 template uses EF 5 and I think it has to do with how database migration was handled in that version and the unconventional way that InitializeSimpleMembershipAttribute creates the database as seen in this code snippet:

// Create the SimpleMembership database without Entity Framework migration schema
((IObjectContextAdapter)context).ObjectContext.CreateDatabase();

To make a long story short, I have seen these MVC 4 projects get into this state where changes to the UserProfile will not work and I have not figured out a solution to the problem yet. I will update this answer if I find a solution.

Answer 2

Just remove the GUID part from your code (let the default UserID remain the primary key) and it will work. Kevin Junghans has explained why.

Running your MvcApplication should now make the necessary alteration to the Table Structure.

In case it doesn't: You'll have to do a migration. or You could just drop the UserProfile (and the other webpages_* tables) from the database and then Run the Application

This may help you with the migration process