How to work with Entity Framework and ASP.NET membership tables?

Question

I'm creating an entity model for a Silverlight application which is currently in constant flux. My general workflow is to add and remove bits as required then generate the DB from the entity model as I work.

I'm trying to incorporate the ASP.NET user/membership tables into the entity model but I'm running into some annoying problems. I'm trying to relate a table of my own, Courses, with the aspnet_Users table in a many-to-many relationship. This shouldn't require modifying the aspnet_Users table at all, just a foreign key on Courses. However when I try to build the DB from the model I get errors from EF because it's trying to drop aspnet_Users and recreate it, which fails because of foreign key relationships from other aspnet_* tables.

Am I going about this in totally the wrong way? Should I perhaps be using some kind of membership provider model to hook into ASP.NET membership/roles etc? Is there some way I can do what I need to do but still leverage all of the work the ASP.NET team did and save myself the hassle of creating my own role/membership/profile system?

Answer 1

(I'm here because I was reading posts on SO going down the pan after getting frustrated with no one answering mine, and well I thought I'd put my 2c worth in on this one still waiting for an answer...)

Previous to EF V5 I solved this problem using the roles and memberships profile provider.

In your web.config file (The one that holds the connection for the database with your asp.net membership data in, you can define something like the following:

<profile enabled="true" defaultProvider="MyProfileProvider">

  <providers>
    <clear />
    <add name="MyProfileProvider"
         connectionStringName="vbox01"
         type="System.Web.Profile.SqlProfileProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  </providers>

  <properties>
    <add name="FirstName" type="System.String" />
    <add name="LastName" type="System.String" />
    <add name="BirthDay" type="System.DateTime" />
    <add name="CustomBackColour" type="System.String" />
    <add name="CustomLinkColour" type="System.String" />
    <add name="CustomLinkHoverColour" type="System.String" />
    <add name="ProfilePhoto" type="System.String" />
    <add name="Modules" type="System.Collections.Generic.List`1[System.String]" serializeAs="Binary"/>
    <add name="SideModules" type="System.Collections.Generic.List`1[System.String]" serializeAs="Binary"/>
    <add name="DefaultPageName" type="System.String" />
    <add name="DefaultModule" type="System.String" />
    <add name="PersonalPage" type="System.Boolean" />
  </properties>
</profile>

once you have that added, you can then support access to it in your web app with the following:

using System;
using System.Collections.Generic;
using System.Web.Profile;

namespace intranet.web.Classes
{
  public class ProfileCommon : ProfileBase
  {
    [SettingsAllowAnonymous(false)]
    public string FirstName
    {
      get { return base["FirstName"] as string; }
      set { base["FirstName"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string LastName
    {
      get { return base["LastName"] as string; }
      set { base["LastName"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public DateTime BirthDay
    {
      get { return (DateTime)base["BirthDay"]; }
      set { base["BirthDay"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string CustomBackColour
    {
      get { return base["CustomBackColour"] as string; }
      set { base["CustomBackColour"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string CustomLinkColour
    {
      get { return base["CustomLinkColour"] as string; }
      set { base["CustomLinkColour"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string CustomLinkHoverColour
    {
      get { return base["CustomLinkHoverColour"] as string; }
      set { base["CustomLinkHoverColour"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string ProfilePhoto
    {
      get { return base["ProfilePhoto"] as string; }
      set { base["ProfilePhoto"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public List<string> Modules
    {
      get { return (List<string>)base["Modules"]; }
      set { base["Modules"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public List<string> SideModules
    {
      get { return (List<string>)base["SideModules"]; }
      set { base["SideModules"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string DefaultPageName
    {
      get { return base["DefaultPageName"] as string; }
      set { base["DefaultPageName"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public string DefaultModule
    {
      get { return base["DefaultModule"] as string; }
      set { base["DefaultModule"] = value; }
    }

    [SettingsAllowAnonymous(false)]
    public bool PersonalPage
    {
      get { return (bool)base["PersonalPage"]; }
      set { base["PersonalPage"] = value; }
    }

  }
}

Which builds on the "ProfileBase" class exposed by System.Web.Profile in the ASP.NET runtime.

I also built myself a few helpers, similar to the following:

public static MemberData GetMemberDataFor(string userName)
{
  MemberData result = new MemberData();

  MembershipUser user = Membership.GetUser(userName);
  if(user != null)
  {
    ProfileCommon pc = new ProfileCommon();
    pc.Initialize(userName, true);

    result.UserName = user.UserName;
    result.FirstName = pc.FirstName;
    result.LastName = pc.LastName;
    result.Birthday = pc.BirthDay;
    result.BackGroundColour = pc.CustomBackColour;
    result.LinkColour = pc.CustomLinkColour;
    result.HoverColour = pc.CustomLinkHoverColour;
    result.ProfilePhoto = pc.ProfilePhoto;
    result.DefaultPage = pc.DefaultPageName;
    result.HasPersonalPage = pc.PersonalPage;
    result.Modules = pc.Modules;
    result.SideModules = pc.SideModules;
    result.DefaultModule = pc.DefaultModule;
    result.DateRegistered = user.CreationDate;
    result.MemberId = (Guid?) user.ProviderUserKey;

  }
  else
  {
    result = null;
  }

  return result;
}

and one going the other way (Although I guess you could use automapper, stucturemap on any other myrid of things, for my purposes the above and it's cousins where enough for the small project I was working on, to allow me to get things back and forth too and from my view models.

In your case, you would most likley put this class and config info behind some kind of services API which would then server Json objects to your silverlight app.

The clever part about all of this however is that it doesn't require database schema changes. The roles and membership system already has columns to hold the serialised/deserialised data, so you can at any time add/remove entries from the classes and from the web.config file and the system will just use them as is, any removed my be pulled back once but will never be saved again, and new ones may not exist on first use but will be saved the first time you save an object.

Those two edge cases however can be be handled relatively easy with some creative error handling.

I know it's not the same as having individual tables, but it's a good work around and it plays nice with older versions of EF too.

If your using EF5+ however , then you should just check out the entity migrations stuff, to put it bluntly it just rocks. Once your set up to go, DB updates are a simple as a few new commands in the package manager console window.

I know it's 2 years too late, but hopefully it helps in some obscure way.

Shawty