Entity Framework – Code First – Many to Many Relationship

Here is a simple example of code first many to many relationship.  A user can have multiple roles and a role can have multiple users.

public class Role
public int Id { get; set; }

public string RoleName { get; set; }

public virtual ICollection<User> Users { get; set; }

public class User
public int Id { get; set; }

public string Email { get; set; }

public string Password { get; set; }

public virtual ICollection<Role> Roles { get; set; }

The virtual property Users in class Role and the virtual property Roles in class User help establish a many to many relationship. 

In fact, entity framework will generate a association table UserRole for you.

public override void Up()
c => new
Id = c.Int(nullable: false, identity: true),
RoleName = c.String(nullable: false, maxLength: 50),
.PrimaryKey(t => t.Id);

c => new
Id = c.Int(nullable: false, identity: true),
Email = c.String(nullable: false, maxLength: 100),
Password = c.String(nullable: false, maxLength: 50),
.PrimaryKey(t => t.Id);

c => new
User_Id = c.Int(nullable: false),
Role_Id = c.Int(nullable: false),
.PrimaryKey(t => new { t.User_Id, t.Role_Id })
.ForeignKey("dbo.User", t => t.User_Id, cascadeDelete: true)
.ForeignKey("dbo.Role", t => t.Role_Id, cascadeDelete: true)
.Index(t => t.User_Id)
.Index(t => t.Role_Id);



VS2013 and SQL Server Compact Tips

  1. You should install SQL Server Compact Toolbox via VS2013 –> Tools –> Extensions and Updates.  Remember to restart VS2013.
  2. Nuget EntityFramework.SqlServerCompact.  It should download SQL Server Compact 4.0 and EntityFramework 6.1.0.
  3. If your model entities reside in a different project (i.e. class library) with your start up project (i.e. console app), you need to be aware of a couple of things.
    1. You need to nuget EntityFramework.SqlServerCompact for both your projects.  The “app.config” in your model class library project is redundant; it is your start up project’s app.config file that counts.
    2. By default, the SQL Server Compact (*.sdf) is located in the bin folder of your model class library project when you do migration (i.e. update-database).  When in doubt, use Windows File Explorer to search for “*.sdf”.
    3. But when you run the application, it will create another *.sdf in your start project’s bin folder. 
    4. Having 2 sdf files is a problem.  What we really want is have have only 1 sdf in the startup project’s bin folder, to be used for code first migration and app runtime.  There are a couple of solutions:
      1. You can add a connection string to the app.config with the Data Source having an absolute path and reference that connection string in your DbContext class. 
      2. <connectionStrings>
        <add name="DefaultConnection"
        connectionString="Data Source=C:\Program Files\xxxx\xxxx.sdf"/>

        public class XxxxContext : DbContext
        public XxxxContext()
        : base("DefaultConnection")


      3. However, I am wary of absolute path, so I do the following hack:
        public class XxxxContext : DbContext
        public XxxxContext()
        string connectionString = string.Format(@"Data Source={0}XxxxDB.sdf", AppDomain.CurrentDomain.BaseDirectory);

        if (connectionString == @"Data Source=C:\Projects\Xxxx\Main\Source\Xxxx.Entity\bin\Debug\XxxxDB.sdf")
        connectionString = @"Data Source=C:\Projects\Xxxx\Main\Source\XxxxApp\bin\Debug\AnnClientDB.sdf";

        this.Database.Connection.ConnectionString = connectionString;


  4. When you update database, you can use “update-database -Verbose”.  This let you know the connection string it is using.

Entity Framework Code First: SQL Server Compact vs LocalDB vs SQLite

If you are deploying a local database with your application to be installed on a user desktop, you should compare the following options:

  • LocalDb —
    • Entity Framework Code First Integration is seamless. 
    • It targets developer machines or IIS or Azure environment.  It isn’t meant to be deployed to end user machines.
    • Administrators of the installed machine has rights to everything.
    • Separate installation of the database software is needed. 
  • SQL Server Compact –
    • You can password protect the database. 
    • Entity Framework Code First Integration is seamless. 
    • It does not support stored procedures. 
    • Integrated application installation.
    • It is not supported in Windows 8 Metro App.
  • SQLite –
    • It supports most platforms, even Windows 8 Metro App. 
    • Code First Migration is not smooth at of this writing. 
    • You can encrypt the database. 
    • No separate installation needed.

To future proof your app, you should choose either LocalDb or SQLite.

The following requirements make me to choose SQL Server Compact:

  • Easy development with VS2013.  Code First migration is seamless.  You can download “SQL Server Compact Toolbox” for VS2013.
  • Easy Deployment/Installation.  Users should be able to install the application by launching only 1 installation.  In other words, users should not be required to install the database separately.
  • The database needs some security protection from the users/administrators on the installed machines.  One can debate the need for encryption and password protection.  However, these simple security mechanisms are better than none for non-critical applications.

Sticky Idea SUCCESs Model

One can evaluate a sticky ideas using SUCCESs model principles:

  1. Simple –
    1. What is the core message?  What is the lead of the story?  What would be the 1 sentence description?  Consider using proverbs or analogies.
  2. Unexpected
    1. Use elements of surprise, knowledge gaps, and mystery to grab attentions, pick curiosity, and hold interests.  The audience has to “want” to learn more.
  3. Concrete
    1. To be concrete, use sensory languages to describe a mental picture.
  4. Credible
    1. Gain credibility from authorities or living proof.
    2. Statistics that can be compared to tangible daily things.
    3. Vivid Details
    4. “See for yourself”
  5. Emotional
    1. People care about people, not numbers.
    2. Identity appeals. 
  6. Stories
    1. Use stories to simulate a situation and share the solutions to problems.
    2. A success story can inspire people to want to follow.  (e.g. Subway sandwich diet)




Visual Studio Project Platform Missing ‘Any CPU’


There are times when you add some projects (e.g. ProjectXXXX) from someone else into your solution. 


Then when you build, you get some errors such as

“C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(1635,5): warning MSB3270: There was a mismatch between the processor architecture of the project being built “MSIL” and the processor architecture of the reference “ProjectXXXX”, “x86”. This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.”

Or when you deploy the build to a 64-bit server, you can an error like “System.BadImageFormatException: Could not load file or assembly ‘{ProjectXXXX.dll}‘ or one of its dependencies. An attempt was made to load a program with an incorrect format.”


The most likely cause is that your ProjectXXXX has a build target against “x86” instead of “Any CPU”.  In fact, you will notice your Platform is “Active (x86)”



Required Fix: change Platform target from “x86” to “Any CPU” and rebuild.

An additional fix is change the Platform (located at the top) from “Active (x86)” to “Any CPU”.  But Platform drop down lists only “Active (x86)”; “Any CPU” is missing.

  1. Go to Menu “Build” –> Configuration Manager
  2. Locate a project with Platform “x86”.
  3. SNAGHTML1b310721
  4. Click <New> in the drop down list
  5. image
  6. Select <Empty> in Copy Settings from
  7. Uncheck Create new solution platforms.
  8. OK
  9. Be sure your project has the Build checkbox checked in the Configuration Manager.
  10. Rebuild solution.