Cannot Attach the File as Database...

Short post this time.

"Cannot attach the file as database....". I came across this error when rolling Membership into EF Code First. Searching, of course, led to Stack Overflow. Most of the answers were saying to drop and re-create the database. However this did not fit my situation as the database was not even created!

The solution was rather simple. Right click the App_Data folder and add a brand new (empty) database and run the solution again.

As a side note, it is quite common to get another error dealing with the database initialization. Your initialization code needs to run before the database is accessed. A good place to do that is in Application_Start in Global.asax.

protected void Application_Start()
{
   Web.Security.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: true);
}

Of course another place to put that line of code is in OnModelCreating of your DbContext class.

Entity Framework Fluent API Mappings

Entity Framework Code First allows you to take control of your database schema. You can do this two ways, decorating your POCO's with attributes or using the fluent API. This post will show how to define relationships using the fluent API.

One to Many With Foreign Key:

public class Chats
{
   // Chats has one to many with ChatMessage
   public virtual ICollection<ChatMessage> ChatMessages { get; set; }

   // Chats has one to many with EventGroup
   public virtual ICollection<EventGroup> EventGroups { get; set; }

   // Chats is on the many side of one to many with Users
   // Set up navigation by providing Id and the POCO to reference back
   public virtual int UserId { get; set; }
   public virtual Users User { get; set; }

   // Chats is on the many side of one to many with ChatType
   // Set up navigation by providing Id and the POCO to reference back
   public virtual int ChatTypeId { get; set; }
   public virtual ChatType ChatTypeEntity { get; set; }
}

public ChatsConfig()
{
   // Chats is on the many side of one to many with Users
   HasRequired(c => c.User)
      .WithMany(u => u.Chats)
      .HasForeignKey(c => c.UserId);

   // Chats is on the many side of one to many with ChatType
   HasRequired(c => c.ChatTypeEntity)
      .WithMany(cte => cte.Chats)
      .HasForeignKey(c => c.ChatTypeId);
}

The Chats POCO has two ICollection properties and two navigation properties back to the referenced POCO's. We configure the one side which is represented by the navigation property and the corresponding Id. In this case, UserId and User, ChatTypeId and ChatTypeEntity.

 

 

Another One to Many With Foreign Key:

public class Artist
{
   public virtual int AritstId { get; set; }
   public virtual string AritstName { get; set; }
   public virtual string Country { get; set; }

   public virtual Genre Genre { get; set; }
   public virtual ICollection<Title> Titles { get; set; }
}

public ArtistConfig()
{
   // Artist has many Titles, required
   HasMany(a => a.Titles).WithRequired(t => t.Artist);
}

This approach is different from above. This time we are mapping the side that contains the ICollection<T>, as we can see the ICollection<Title> Titles property in the Artist class. Another difference is that we are using WithRequired here. This simply means that both ends of the relationship must exist in order for it to be valid. This does have a drawback. If you really want to delete an item from the collection, this set up will not work. You would have to delete the entire relationship. To get around that, you could make a composite key which will allow deleting collection items.

 

Composite Primary Key:

public class AboutUsImage
{
   public virtual long DealerId { get; set; }
   public virtual short ImageOrder { get; set; }
}

public AboutUsImageConfig()
{
   HasKey(aui => new { aui.DealerId, aui.ImageOrder } );
}

This one is pretty straightforward. You just take the two columns you need for the composite key and put them together in an anonymous object.

 

Composite Primary Key With Foreign Key:

public class Permission
{
   public virtual long UserId { get; set; }
   public virtual string PermissionType { get; set; }
   public virtual string AccessLevel { get; set; }

   public virtual User User { get; set; }
}

public PermissionConfig()
{
   // composite key, UserId and PermissionType
   // a User has many Permissions so UserId is also FK
   HasKey(p => new { p.UserId, p.PermissionType } )
      .HasRequired(p => p.User).WithMany(p => p.Permissions);

The composite key is the same as above. This foreign key case is a bit odd. Note that the foreign key is not explicitly defined here. EF inferred it. If you want to explicitly define, it would look like this:

   HasKey(p => new { p.UserId, p.PermissionType })
      .HasRequired(p => p.User).Withmany(p => p.Permissions)
      .HasForeignKey(p => p.UserId);

 

 

One to One:

public class User
{
   public virtual int UserId { get; set; }
   public virtual string Name { get; set; }
   pubilc virtual Address Address { get; set; }
}

public class Address
{
   public virtual int AddressId { get; set; }
   public virtual string Street { get; set; }
   public virtual string City { get; set; }
   public virtual string Zip { get; set; }
}

public UserConfig()
{
   HasOptional(u => u.Address).WithRequired();
}

To specify the one to one, you use HasOptional and WithRequired. There is no need to use any Id properties. If you do not see the relationship itself, create a diagram in SQL Server Management Studio.

 

Many to Many Relationship:

public class UserProfile
{
   public int UserId { get; set; }
   public string UserName { get; set; }
   public virtual ICollection Roles { get; set; }
}

public class Role
{
   public int RoleId { get; set; }
   public string RoleName { get; set; }
   public virtual ICollection UserProfiles { get; set; }
}

Right away we can see a relationship between UserProfile and Role. They each contain an ICollection of each other, of course, this is an indication of a many to many relationship. It doesn't matter which entity we choose, either will get the job done. We'll use UserProfile. Define the properties as normal for the POCO. But when dealing with the ICollection, this is where we set up the many to many:

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class UserProfileConfig : EntityTypeConfiguration
{
   public UserProfileConfig()
   {
      HasKey(up => up.UserId);
      Property(up => up.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      Property(up => up.UserName).HasColumnName("UserName").HasColumnType("nvarchar").HasMaxLength(128).IsOptional();

      HasMany(up => up.Roles)
         .WithMany(r => r.UserProfiles)
         .Map(usersinroles =>
         {
            usersinroles.MapLeftKey("UserId");
            usersinroles.MapRightKey("RoleId");
            usersinroles.ToTable("webpages_UsersInRoles");
         });

      ToTable("UserProfile");
   }
}

Integrating SimpleMembership with Entity Framework

Integrating SimpleMembership with Entity Framework is quite nice. Instead of having two databases, collapse it all into one and have alll the info you need in one place. I'll be using a MVC 4 Internet application in VS 2012. I'll be putting all the POCO's and support classes in the Models folder for simplicity. First order of business, in the Filters folder, delete the InitializeSimpleMembershipAttribute C# file, as this will not be needed. We need to remove two classes from the AccountModels C# file. Open up AccountModels in the Models folder and delete the UserContext and UserProfile classes. For SimpleMembership we will have to create the four tables necessary for it: webpages_Membership, webpages_OAuthMembership, webpages_Roles, and webpages_UsersInRoles. Additionally, we will also add a UserProfile table and a dummy Person table. I like to add some support files for the EntityConfig classes and this requires StructureMap. Grab StructureMap from NuGet, it doesn't have to be the StructureMap.MVC package, just the plain StructureMap package. In the Models folder, add a class file, IEntityConfiguration:

using System.Data.Entity.ModelConfiguration.Configuration;

public interface IEntityConfiguration
{
   void AddConfiguration(Configuration registrar);
}

Add a class file, ContextConfiguration:

using System.Collections.Generic;
using StructureMap;

public class ContextConfiguration
{
   public IEnumerable Configurations
   {
      get { return ObjectFactory.GetAllInstances(); }
   }
}

Just to get it out of the way quick, let's take care of bootstrapping StructureMap. Open global.asax.cs, in the Application_Start method, right above the call to AreaRegistration.RegisterAllAreas();, add SetStructureMap(); Now let's create that method:

private void SetStructureMap()
{
   ObjectFactory.Initialize(x =>
                               {
                                  x.Scan(scan =>
                                               {
                                                  scan.TheCallingAssembly();
                                                  scan.WithDefaultConventions();
                                                  scan.AddAllTypesOf();
                                               });
                               });
}

All the POCO's:

public class Membership
{
   public int UserId { get; set; }
   public DateTime? CreateDate { get; set; }
   public string ConfirmationToken { get; set; }
   public bool? IsConfirmed { get; set; }
   public DateTime? LastPasswordFailureDate { get; set; }
   public int PasswordFailuresSinceLastSuccess { get; set; }
   public string Password { get; set; }
   public DateTime? PasswordChangedDate { get; set; }
   public string PasswordSalt { get; set; }
   public string PasswordVerificationToken { get; set; }
   public DateTime? PasswordVerificationTokenExpirationDate { get; set; }
}

public class OAuthMembership
{
   public string Provider { get; set; }
   public string ProviderUserId { get; set; }
   public int UserId { get; set; }
}

public class Role
{
   public int RoleId { get; set; }
   public string RoleName { get; set; }
   public virtual ICollection UserProfiles { get; set; }
}

public class UserProfile
{
   public int UserId { get; set; }
   public string UserName { get; set; }
   public virtual ICollection Roles { get; set; }
}

public class Person
{
   public int PersonId { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
}

The EntityConfig classes will contain the validation and other schema information:

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class MembershipConfig : EntityTypeConfiguration<Membership>, IEntityConfiguration
{
   public MembershipConfig()
   {
      HasKey(m => m.UserId);
      Property(m => m.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
      Property(m => m.ConfirmationToken).HasColumnName("ConfirmationToken").HasColumnType("nvarchar").HasMaxLength(128);
      Property(m => m.IsConfirmed).HasColumnName("IsConfirmed").HasColumnType("bit").IsOptional();
      Property(m => m.LastPasswordFailureDate).HasColumnName("LastPasswordFailureDate").HasColumnType("datetime").IsOptional();
      Property(m => m.PasswordFailuresSinceLastSuccess).HasColumnName("PasswordFailuresSinceLastSuccess").HasColumnType("int").IsRequired();
      Property(m => m.Password).HasColumnName("Password").HasColumnType("nvarchar").HasMaxLength(128).IsRequired();
      Property(m => m.PasswordChangedDate).HasColumnName("PasswordChangedDate").HasColumnType("datetime").IsOptional();
      Property(m => m.PasswordSalt).HasColumnName("PasswordSalt").HasColumnType("nvarchar").HasMaxLength(128).IsRequired();
      Property(m => m.PasswordVerificationToken).HasColumnName("PasswordVerificationToken").HasColumnType("nvarchar").HasMaxLength(128);
      Property(m => m.PasswordVerificationTokenExpirationDate).HasColumnName("PasswordVerificationTokenExpirationDate").HasColumnType("datetime").IsOptional();
   
      ToTable("webpages_Membership");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class OAuthMembershipConfig : EntityTypeConfiguration<OAuthMembership>, IEntityConfiguration
{
   public OAuthMembershipConfig()
   {
      HasKey(o => new { o.Provider, o.ProviderUserId });
      Property(o => o.Provider).HasColumnName("Provider").HasColumnType("nvarchar").HasMaxLength(30).IsRequired();
      Property(o => o.ProviderUserId).HasColumnName("ProviderUserId").HasColumnType("nvarchar").HasMaxLength(100).IsRequired();
      Property(o => o.UserId).HasColumnName("UserId").HasColumnType("int").IsRequired();

      ToTable("webpages_OAuthMembership");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class RoleConfig : EntityTypeConfiguration<Role>, IEntityConfiguration
{
   public RoleConfig()
   {
      HasKey(r => r.RoleId);
      Property(r => r.RoleId).HasColumnName("RoleId").HasColumnType("int").IsRequired();
      Property(r => r.RoleName).HasColumnName("RoleName").HasColumnType("nvarchar").HasMaxLength(256).IsRequired();

      ToTable("webpages_Roles");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class UserProfileConfig : EntityTypeConfiguration<UserProfile>, IEntityConfiguration
{
   public UserProfileConfig()
   {
      HasKey(up => up.UserId);
      Property(up => up.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      Property(up => up.UserName).HasColumnName("UserName").HasColumnType("nvarchar").HasMaxLength(128).IsOptional();

      HasMany(up => up.Roles)
         .WithMany(r => r.UserProfiles)
         .Map(usersinroles =>
         {
            usersinroles.MapLeftKey("UserId");
            usersinroles.MapRightKey("RoleId");
            usersinroles.ToTable("webpages_UsersInRoles");
         });

      ToTable("UserProfile");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;

public class PersonConfig : EntityTypeConfiguration<Person>, IEntityConfiguration
{
   public PersonConfig()
   {
      HasKey(p => p.PersonId);
      Property(p => p.PersonId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      Property(p => p.FirstName).HasColumnName("FirstName").HasColumnType("nvarchar").HasMaxLength(128).IsRequired();
      Property(p => p.LastName).HasColumnName("LastName").HasColumnType("nvarchar").HasMaxLength(128).IsRequired();
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

Of course we will be needing a DbContext class, add a file named MembershipContext:

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

public class MembershipContext : DbContext
{
   public MembershipContext()
      : base("name=DefaultConnection")
   {
   }

   public DbSet Membership { get; set; }
   public DbSet OAuthMemberships { get; set; }
   public DbSet Roles { get; set; }
   public DbSet UserProfiles { get; set; }
   public DbSet People { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
      Database.SetInitializer(new MembershipTestInitializer());

      ContextConfiguration ctxConfiguration = new ContextConfiguration();

      foreach (IEntityConfiguration configuration in ctxConfiguration.Configurations)
      {
         configuration.AddConfiguration(modelBuilder.Configurations);
      }
   }
}

Two things to note: We are passing in a connection string on the constructor of the MembershipContext class and we are setting up some initialization in a class called MembershipTestInitializer. Regarding the connection string, if you are using VS2010 and SQL Server 2008, you can just omit the constructor altogether. In that case, by default, EF will use SQL Server Express. If you are using VS2012 and you have SQL Server 2010 and 2012 installed, you have some options. If you omit the constructor, EF will use SQL Server Express. Or, there is a default connection set in web.config. That is what we are doing. If you open web.config you will see the default connection in the connectionStrings section. You will also see that it is using the new LocalDb. LocalDb adds some jargon in the connection string that will not give us the exact database name we want. You can strip that out, for example:

<connectionStrings>
   <add name="DefaultConnection"
   connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=MembershipContext;
                     Integrated Security=SSPI; AttachDBFilename=|DataDirectory|\MembershipContext.mdf"
                     providerName="System.Data.SqlClient" />
</connectionStrings>

MembershipTestInitializer will contain seed membership info. This class will provide the bridge to bring SimpleMembership into our POCO's. After that, everything will be in one db:

using System.Data.Entity;
using System.Linq;
using System.Web.Security;
using WebMatrix.WebData;

public class MembershipTestInitializer : DropCreateDatabaseIfModelChanges
{
   protected override void Seed(MembershipContext context)
   {
      SeedMembership();
   }

   private void SeedMembership()
   {
      WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: true);

      SimpleRoleProvider roles = (SimpleRoleProvider) Roles.Provider;
      SimpleMembershipProvider membership = (SimpleMembershipProvider) System.Web.Security.Membership.Provider;

      if (!roles.RoleExists("Admin"))
      {
         roles.CreateRole("Admin");
      }
      if (membership.GetUser("sheldon", false) == null)
      {
         membership.CreateUserAndAccount("sheldon", "Password01");
      }
      if (!roles.GetRolesForUser("sheldon").Contains("Admin"))
      {
         roles.AddUsersToRoles(new string[] { "sheldon" }, new[] { "admin" } );
      }
   }
}

The call to WebSecurity.InitializeDatabaseConnection came from the InitializeSimpleMembershipAttribute C# file we deleted earlier. That is what originally kicks off the SimpleMembership db creation. In that method we are passing the connection string, the name of the table for UserProfile, the UserId, and the UserName. All of those line up nicely with the UserProfile POCO.
At this point, if we run the application, nothing will happen. We need to perform some sort of action to create the database. Initially, you may think, ok let's go and create a user. That will not work at this point because the line of code, WebSecutiry.InitializeDatabaseConnection, in the SeedMembership method has not run. The MembershipContext class has not been called, which kicks all this off.
To rectify that, simply go into the Index method of the HomeController and create a new Person. This should be enough to create the database:

public ActionResult Index()
{
   MembershipContext context = new MembershipContext();
   context.People.Add(new Person()
   {
      FirstName = "Sheldon",
      LastName = "Cooper"
   });

   return View();
}

Let's go have a look at the database:

 

And there we have it, all the SimpleMembership tables in our MembershipContext database.
EF FTW, eat it Tater.

Entity Framework & StructureMap

Entity Framework and StructureMap, weird combo, yeah I know. But here is a "cute" little trick if you use Code First and roll your own entity config classes. I started with an empty solution, added a data project and a console project.

In the data project, a simple POCO:

public class AboutUs
{
   public virtual long DealerId { get; set; }
   public virtual short ImageOrder { get; set; }
   public virtual string Url { get; set; }
   public virtual string Alt { get; set; }
   public virtual string Size { get; set; }
}

 

Now let's set up the DbContext...

using System.Data.Entity;
using EF5Demo.Data.Entities;

public class EF5DemoContext : DbContext
{
   public EF5DemoContext()
   {
      this.Configuration.AutoDetectChangesEnabled = true;
      this.Configuration.ProxyCreationEnabled = true;
   }

   public DbSet AboutUs { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {

   }
}

 

We'll fill out the OnModelCreating after we drop in StructureMap. For this little setup, StructureMap will be needed in both projects. In each project, right click on References, select Manage NuGet Packages. We just need the regular version of StructureMap.

 

 

 

Now to set up an interface and a concrete implementation that will be consumed by the entity config classes. First, the IEntityConfiguration interface:

using System.Data.Entity.ModelConfiguration.Configuration;

public interface IEntityConfiguration
{
   void AddConfiguration(ConfigurationRegistrar registrar);
}

 

The class that uses this interface is the link between StructureMap and the entities, the ContextConfiguration class:

 

using System.Collections.Generic;

public class ContextConfiguration
{
   public IEnumerable Configurations
   {
      get { return ObjectFactory.GetAllInstances(); }
   }
}

 

Let's roll the entity config class for the AboutUs POCO.This config class will implement the IEntityConfiguration interface:

 

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;
using System.ComponentModel.DataAnnotations.Schema;
using EF5Demo.Data.Entities;

public class AboutUsConfig : EntityTypeConfiguration, IEntityConfiguration
{
   public AboutUsConfig()
   {
      // composite PK
      HasKey(aui => aui.DealerId, aui.ImageOrder });
      // Identity column
      Property(aui => aui.DealerId).HasColumnName("DealerId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      Property(aui => aui.ImageOrder).HasColumnName("ImageOrder");
      Property(aui => aui.Url).HasColumnName("Url").IsRequired();
      Property(aui => aui.Alt).HasColumnName("Alt").IsRequired();
      Property(aui => aui.Size).HasColumnName("Size").IsRequired();
      ToTable("AboutUs");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

 

Let's return to the DbContext class and fill out the OnModelCreating method...

 

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   ContextConfiguration ctxConfig = new ContextConfiguration();

   foreach (var config in ctxConfig.Configurations)
   {
      config.AddConfiguration(modelBuilder.Configurations);
   }
}

 

When the model gets created, the ContextConfiguration class gets created. The Configurations property looks for all instances of the IEntityConfiguration interface (which would be all the entity config classes) and adds them to the model builder, and thus, provide structure and validation to your db tables.

 

Let's see if it works, in the console project...

using StructureMap;
using EF5Demo.Data;

static void Main(string[] args)
{
   ObjectFactory.Initialize(x =>
   {
      {
         scan.TheCallingAssembly();
         scan.WithDefaultConventions();
         scan.Assembly("EF5Demo.Data");
         scan.AddAllTypesOf();
      };
   });

   ContextConfiguration configurations = 
      ObjectFactory.GetInstance();

   foreach (var configuration in configurations.Configurations)
   {
      Console.WriteLine(configuration);
   }
}

 

There we go, entity config classes have been picked up. Like I said, this is just a cute little trick. The benefit is that for every entity config class you add, you don't have to go back and add it to the model builder. Conversely, whenever you remove an entity you won't have to worry about removing the config from the model builder.