About

The ADO.Net Entity Framework is an ORM (Object-Relational Mapping).
A tool for simplifying your Data Access Layer.

It's used to convert data between two incompatible type systems (in this case, between a relational database and a .Net object-oriented language).
It allows you to work with relational data as domain-specific objects.

Entity Framework automates databased-related activities so you don't have to write all the ADO.Net code yourself.
This guarantees consistency in the generated SQL, and saves time.
Creates connections, creates and executes commands.

Queries are issued using LINQ To Entities, data is returned as strongly-typed objects.

Also provides services like:
    change tracking
    identity resolution
    lazy loading
    query translation
    
Entity Framework can be pointed at many possible databases: SQL Server, SQL Compact, an Azure cloud, Oracle, DB2, etc.

Getting Started

Schema-First

Schema-First approach means opening a graphical designer in Visual Studio, pointing it at an existing database schema, and having it auto-import that schema information.

If you reverse-engineer to an EDMX, the file can be automatically updated when the database changes.

If you reverse-engineer to Domain classes, they cannot be automatically updated.

Model-First

Model-First approach means opening a graphical designer in Visual Studio, and drawing a diagram of the object model you'll be using.

If you generate the database schema from this model, the database cannot be automatically updated with changes later.

Code-First

Code-First approach means writing your C# classes, and having those auto-imported into the object model.

Can auto-generate database schema from Domain classes.
Can auto-update database schema when the Domain classes change (Migration).

EF starts with a set of conventions to infer what the database schema should be.
EF also supports may ways to specify non-conventional mappings.
How EF Works

Summary

Create your Domain classes.

DbContext API wraps the Domain classes. Instruct EF how this model maps to the database schema.
You may create many different DbContexts from one database.

Write your queries using LINQ To Entities, against your domain classes. EF will translate the queries into SQL queries against the database schema.

Can map to database tables or to database views.
Can execute stored procedures.

Models

EDMX Model vs Code-Based Model

EF can use a visual model (the drag-n-drop designer) or a model inferred from your domain classes.

If you use the designer, it will generate a *.edmx file (xml format). Then it will generate domain classes based on the *.edmx.
(EDMX = Entity Data Model XML)
If you write your own domain classes, those steps are skipped.

At runtime, any available *.edmx file will be used by default. Your custom domain classes will only be used if there is no *.edmx file (Code-First API generates the in-memory model).
Either way, EF generates an in-memory model.


Versions

6 Vs 7

EF 6 was the first version of Entity Framework not embedded in a .Net Framework - it is just a NuGet package. It is also open-source.
[EF is open-source on CodePlex]
(CodePlex includes some EF extensions that are not part of the full release of EF.)

EF 6 is backwards compatible to EF 4.

EF 7 is a fresh rewrite of Entity Framework.
EF 7 is not backward compatible with EF 6.
EF 7 uses 3rd party visual designers instead of the EF 6 Microsoft designer.
EF 7 can work on .Net Core, and also works on the full .Net Framework.
EF 7 is broken in smaller pieces, you can select what you need.
EF 7 supports non-relational databases.

EF 6 and EF 7 will be supported in parallel for some time.

These notes are focused on EF 6.

6 Versions

From experience, EF 6.2 works fine with Visual Studio 2015, but EF 6.3 causes reference errors in Visual Studio 2015.

Pre-6

Entity Framework began embedded in .Net 3.5.
EF4 (numbered to match the .Net Framework) was embedded in .Net 4.0.

EF 4.1 through 4.3 were NuGet extensions to EF 4.
It added "Code First" modeling and the "DbContext" object (sitting in front of the ObjectContext).

EF 5 was embedded in .Net 4.5 along with a new NuGet extension for it.
It had a lot of performance improvements, enum support, among other things.


Code First

The name is misleading: you may have already built your database. This method is really an alternative to defining the database-to-code mapping in an EDMX file. This way you define the mapping in C#.

Objects

POCO stands for Plain Old CLR Object. These are your Domain classes - they have Properties, and no Methods.
So these are not "Entities" as "Clean Architecture" defines it, since they contain no behavior, and exist in the Data Layer.


public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ContinentId { get; set; }
    public virtual Continent Continent { get; set; }
    public virtual ICollection<City> Cities { get; set; }
}

By Entity Framework convention, a Property called "Id" will be the Primary Key, and will Auto-Increment.

The "virtual" keyword is optional here. Without it, Country.Cities will be null. With it, Entity Framework will load the Cities collection at the time that you need reference it (Lazy Loading).

It is recommended to have Foreign Key Properties (such as ContinentId) explicitly defined.
This makes the relationship more clear to Entity Framework. For instance, is the Foreign Key nullable or not?
It is also critical for maintaining relationships in applications where object references are lost (disconnected applications). Like a web application, or an architecture that separates the Business Entities from the Data Access Layer.

If you don't specify a foreign key property, you're using an Independent Association.

See "Data Annotations" for more property specifications that will affect the generated model.

There's also something called "Fluent API" that could be used here.

Minimum Configuration

Define DbContext

using System.Data.Entity;

public class MyDb : DbContext
{
    public DbSet<Country> Counties { get; set; }
}
A DbSet is a "Repository", in terms of the "Repository Pattern".

Use DbContext

MyDb _db = new MyDb();
List<Country> model = _db.Countries.ToList(); //this connects to the database, sends the query, and returns the results
_db.Dispose();

Optional: set which database connection from the Web.config to use

using System.Data.Entity;

public class MyDb : DbContext
{
    public MyDb() : base("name=DefaultConnection")
    {
    }
    
    public DbSet<Country> Countries { get; set; }
}

Validating Model

You can look at the database schema EF will create from your Domain classes, to see if you need to define more specific Configuration.

1) In Visual Studio > Tools menu > Extensions and Updates
2) Install "Entity Framework Power Tools" (for VS2015, download from online)
3) Set the project with the DbContext class as the Start Up project.
4) Right-click on a DbContext class file > View Entity Data Model

Configuration

The configuration explicitly defines the mapping from object to database table.


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

public class CountryConfiguration : EntityTypeConfiguration<Country>
{
    public CountryConfiguration()
    {
        ToTable("Country", "dbo"); //use table name "Country" instead of default "Countries"
        HasKey(x => x.Id); //sets primary key
        Property(x => x.Id).HasColumnName("CountryId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.ContinentId).IsRequired();
        Property(x => x.Name).HasMaxLength(64).IsOptional();
        HasMany(x => x.Cities).WithRequired(city => city.Country).HasForeignKey(city => city.CountryId).WillCascadeOnDelete(true);        
    }
}

Relationships are only defined on one object in the relationship. Here, the Country defines the Country-City relationship and leaves the Continent-Country relationship to the ContinentConfiguration class.

Context

The database context brings together multiple configurations. A single context does not have to cover the entire database.


using System.Data.Entity;

public class GeographyContext : BaseContext<GeographyContext>
{
    public DbSet<Continent> Continents { get; set; }
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }        

    public GeographyContext(string connectionString) : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations
            .Add(new ContinentConfiguration())
            .Add(new CountryConfiguration())
            .Add(new CityConfiguration());
        base.OnModelCreating(modelBuilder);
    }
}

public abstract class BaseContext<T> : DbContext where T:DbContext
{
    static BaseContext()
    {
        Database.SetInitializer<T>(null);
    }
    
    protected BaseContext(string connectionStringOrConnectionName) : base(connectionStringOrConnectionName)
    {
    }
    
    public override int SaveChanges()
    {
        //if you have custom code to run on SaveChanges
        return base.SaveChanges();
    }
}

1 to 1..0 relationship:

    HasOptional(x => x.Child).WithRequired(child => child.Parent);

1 to 1 relationship:

    HasRequired(x => x.Child).WithRequired(child => child.Parent);

Many to many relationship:

    HasMany(objectB => objectB.ObjectsA).WithMany(objectA => objectA.ObjectsB);

Many to many relationship with joining table:

    HasMany(objectB => objectB.ObjectsA).WithMany(objectA => objectA.ObjectsB)
        .Map(objectB => objectB.MapLeftKey("BId").MapRightKey("AId").ToTable("AtoB"));

Ignore a code-only property:

    Ignore(x => x.Property);

Querying Context


using(GeographyContext context = new GeographyContext(connectionString))
{
    List<City> cities = context.Cities.Include("Country").Where(x => x.HasElectionThisYear).ToList();
}

Creating Database

In at least Visual Studio 2012, if you create your code-first classes, create the database context, instantiate the database context, and run the code - then Entity Framework will try to connect to the Default Connection defined in Web.config, and if it succeeds it will automatically create the database as described by the object model.

The database name will match the fully qualified name of the DbContext object you instantiated.
Ex: I used class "AppDb : DbContext" under namespace "App.Models" so the database was created with name "App.Models.AppDb".

To create the database semi-manually, see Migrations.

Views

Entity Framework does not treat Entities created from Views any differently than Entities created from Tables. If you edit a View object and save changes, you'll get an error when the database refuses the update command.

So Views are still for read-only operations.

Schema

How to specify the schema of a table.

With built-in Data Annotations:

[Table("Country", Schema="Other")]
public class Country
{    
}

With a custom Data Annotation and Convention:
(see Conventions notes)

Change default schema for entire model:

modelBuilder.HasDefaultSchema("Other");
This default is applied before any Conventions or Configurations that might override it.
The migration history table will also use the default schema.
Schema First

Starting with an existing database:

EDMX Model

1) In an empty class library project > add New Item > add an ADO.NET Entity Data Model
2) select "EF Designer from database"
3) select the existing database
4) select with database tables/views/procedures/etc to include in the model

You will see the generated model in the designer.

In the project, you'll see 2 T4 templates. These templates generate your Domain classes and DbContext class based on the EDMX model.
These generated classes are always Partial Classes, so you are free to extend them in another file.

Code Model

(EF 6)

1) In an empty class library project > add New Item > add an ADO.NET Entity Data Model
2) select "Code First from database"
3) select the existing database
4) select with database tables/views to include in the model
- stored procedures cannot be included here, they must be configured manually

You will see all the generated classes in the project. This was a one-time generation, so there are no T4 templates.
You'll also have the generated DbContext.

You can alter how the classes are generated by editing the Context.cs.t4 and EntityType.cs.t4 files. For instance, you could have it generate all configuration in the DbContext class, instead of using Data Annotations on the Entities.

By default, every configuration will be generated, ever those that would are implicit by convention (such as a database column name that matches the class property name). That add safety for if the property name is changed at some point.

Model First

Install the Entity Framework Power Tools to get the designer. This installation is a separate MSI you'll need to download.

1) Right-click on Project > Add New Item > ADO.Net Entity Data Model > Empty EF Designer Model
Database

Basic example to connect to SQL Server Express is in "SQL Server" notes under "LocalDB" section.

Connection String

Specify the entire connection string. If you don't, the default machine configuration connection string will be used.

public CountryDbContext() : base("connectionString")
{
}

Specify the name of the connection string in the config file.

public CountryDbContext() : base("name=nameOfConnectionString")
{
}
Note that the config file is that of the currently running process. If you put your connection string in the data access library's config, the service host can't find it. It should be in the service host's config.

Initialization


public CountryDbContext() : base("connectionString")
{
    //select one of the following
    Database.SetInitializer<CountryDbContext>(new NullDatabaseInitializer<CountryDbContext>());
    Database.SetInitializer<CountryDbContext>(new CreateDatabaseIdNoneExists<CountryDbContext>());
    Database.SetInitializer<CountryDbContext>(new DropCreateDatabaseIfModelChanges<CountryDbContext>());
    Database.SetInitializer<CountryDbContext>(new DropCreateDatabaseAlways<CountryDbContext>());
    Database.SetInitializer<CountryDbContext>(new CountryDbInitializer());
}

As of EF 6, these Initializer will take existing migrations into account. This includes skipping Seed Logic if migrations exist.

Settings

Set SQL command timeout (amount of seconds each command has to execute):

dbContext.Database.CommandTimeout = 180; //seconds

Commands

EF 6 sends only 1 command to the database at a time, even if many commands are queued up.
After all commands are run, the session is committed and the connection is closed.

1) Open 1 connection
2) Run each command, one at a time
3) Commit transaction
4) Close connection

If any command fails, the entire transaction is rolled-back.

Parameterization

String literals and numeric literals you use in a LINQ query will not be parameterized.

Variables used in a LINQ query will always be parameterized.

Configuration

DbConfiguration class

Logging


dbContext.Database.Log = HandleLog;

private void HandleLog(string message)
{
    //logging
}


//output all logging to the console
dbContext.Database.Log = Console.Write;

//collect logs into a StringBuilder
StringBuilder builder = new StringBuilder();
dbContext.Database.Log = s => builder.Append(s);

Turn on logging in Web.config instead of in code:

    <entityFramework>
        <interceptors>
            <!-- format: <full name of interceptor>,<name of assembly> -->
            <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger,EntityFramework"/>
        </interceptors>
    </entityFramework>
By default, this logger sends the log to Console.Write.

To specify saving log to a file:

    <entityFramework>
        <interceptors>
            <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger,EntityFramework">
                <parameters>
                    <parameter value="D:\Logs\AppLog.txt"/>
                    <parameter value="true" type="System.Boolean"/>
                </parameters>
            </interceptor>
        </interceptors>
    </entityFramework>

Overriding the format of the logs:

using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;

public class MyLogFormatter : DatabaseLogFormatter
{
    public MyLogFormatter(DbContext context, Action<string> writeAction) : base(context, writeAction)
    {
    }
    
    public override void LogCommand<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
        Write(string.Format("Context {0} is executing command {1}{2}",
            Context.GetType().Name, command.CommandText, Environment.NewLine)
        );
    }
}
Note that when setting the "interceptor" in the Web.config, "Context" in the formatter will always be null.
?how do you tell the application to use your formatter?

Read Committed Snapshot

Setting the Default Transaction Isolation Level to "Read_Committed_Snapshot" in a database means that: each time the data is changed, the database creates a new snapshot. Queries to the database use the snapshot instead of the live data.

Starting in EF 6, this is set by default of the database generated from "Code First". Benefits listed include "more scalability" and "fewer deadlocks".

Intermittent Connections

EF 6 added support for Connection Resiliency - settings for how to respond to intermittent database connectivity.

If you use the SqlDefaultExecutionStrategy, it does not have connection resiliency. Its error messages will suggest using something like SqlAzureExeutionStrategy instead.

Base class DbExecutionStrategy includes settings like Max Retry Count and Max Delay.

Setting your execution strategy:

using System.Data.Entity;
using System.Data.Entity.SqlServer;

class GeographyDbConfiguration : DbConfiguration
{
    public GeographyConfiguration()
    {
        SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () => new SqlAzureExecutionStrategy());
    }
}

How to automate testing:

//Mocking errors in database connections
using System.Data.Entity;
using System.Data.Entity.SqlServer;

class GeographyDbConfiguration : DbConfiguration
{
    public GeographyConfiguration()
    {
        AddInterceptor(new TransientFailureCausingCommandInterceptor());
    }
}

using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;
using SqlExceptions;

public class TransientFailureCausingCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if(!(command.CommandText.Contains("serverproperty") || (command.CommandText.Contains("_MigrationHistory")))
        {
            Debug.WriteLine("throwing fake exception from interceptor");
            throw SqlExceptionFaker.Error10053; //Transient Connection Error
        }
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
}

Async

You can run database queries asynchronously. There is an async version of all the materializing LINQ methods. There's also "LoadAsync", "FindAsync", and "SaveChangesAsync".


public async Task<List<Country>> GetCountries()
{
    using(GeographyContext context = new GeographyContext())
    {
        return await context.Countries.ToListAsync();
    }
}

This is a big topic.

But briefly, having an "await" in the middle of a method will cause execution in the method to pause, and the calling method continues to run. The remainder of the "awaiting" method will run once the async request returns.

Column Data Annotations

Instead of defining everything in a EntityTypeConfiguration<T> configuration class, you can define most column things with data annotations.

Table

Set the database table name:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("ThingTable")]
public class Thing
{
    public int Id { get; set; }
}

Column


using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    //set column name
    [Column("ThingId")]
    public int Id { get; set; }

    //set column data type
    [Column(TypeName="text")]
    public string Name { get; set; }
}

Primary Key

Single primary key:

using System.ComponentModel.DataAnnotations;

public class Thing
{
    [Key]
    public int Id { get; set; }
}

Composite primary key:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    [Key]
    [Column(Order=1)]
    public int AId { get; set; }
    [Key]
    [Column(Order=2)]
    public int BId { get; set; }
}

Foreign Key


using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    [Key]
    public int Id { get; set; }
}

public class OtherThing
{
    [ForeignKey("MyThing")]
    public int ForeignKeyId { get; set; }
    
    public virtual Thing MyThing { get; set; }
}
OR

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    [Key]
    public int Id { get; set; }
}

public class OtherThing
{
    public int ForeignKeyId { get; set; }
    
    [ForeignKey("ForeignKeyId")]
    public virtual Thing MyThing { get; set; }
}

Inverse Property

Link collection to correct property when there are multiple options:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Person
{
    [InverseProperty("WrittenBy")]
    public virtual List<Message> MessagesWritten { get; set; }
    [InverseProperty("UpdatedBy")]
    public virtual List<Message> MessagesUpdated { get; set; }
}

public class Message
{
    public virtual Person WrittenBy { get; set; }
    public virtual Person UpdatedBy { get; set; }
}

Database Generated

Identity column:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
}

Required


using System.ComponentModel.DataAnnotations;

public class Thing
{
    [Required]
    public string Name { get; set; }
}

Length

Sets character or byte length:

using System.ComponentModel.DataAnnotations;

public class Thing
{
    [MinLength(1)]
    [MaxLength(150)]
    public string Name { get; set; }
}

Not Mapped

Property will not be mapped to a database column:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Thing
{
    [NotMapped]
    public string Name { get; set; }
}

Data Types

Char

Entity Framework does not map to the C# char data type.
So in code first, char properties will be ignored.
Change them to string properties, with a maxlength of 1.
Migrations

This will help setup a new database, initialize its data, keep the database schema in sync with changes to the object model, etc.

Setup

1) In Visual Studio > View menu > Other Windows > Package Manager Console
- or Tools menu > NuGet Package Manager > Package Manager Console
2) In console > "Enable-Migrations -ContextTypeName MyDb"
- use the name of your DbContext subclass instead of "MyDb"

This will inspect your entity classes and the database, and will add a new folder "Migrations" to your solution.

Configuration

This is the "Migrations/Configuration.cs" file that was automatically added to your project:

public Configuration()
{
    //Entity Framework will not make changes to database unless you explicitly tell it to
    AutomaticMigrationsEnabled = false; 
}

"Seed" is invoked every time the database is updated by the Migration.
It can insert default data.

protected override void Seed(AppA.Models.AppADb context)
{
    //look in database, search for Countries by "Name", update them or add them
    //using Name instead of Id because Id is auto-incremented and could be anything
    context.Countries.AddOrUpdate( record => record.Id,
        new Country { Name="France" },
        new Country { Name="Germany" }
}

Setting the schema of the _MigrationHistory table:

using System.Data.Common;
using System.Data.Entity.Migrations.History;

public class MigrationHistoryTableContext : HistoryContext
{
    public MigrationHistoryTableContext(DbConnection dbConnection, string defaultSchema) : base(dbConnection, defaultSchema)
    {
    }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder); //run the base method first
        modelBuilder.HasDefaultSchema("admin"); //set schema
    }
}

using System.Data.Entity;
using System.Data.Entity.SqlServer;

internal class GeographyDbConfiguration : DbConfiguration
{
    public GeographyDbConfiguration()
    {
        SetHistoryContext(SqlServerServices.ProviderInvariantName //get name of the database provider you are using
            , (connection, defaultSchema) => new MigrationHistoryTableContext(connection, defaultSchema) //specify migration table context
        );
    }
}
Usage example: put the _MigrationHistory table into a different schema than the main database, so that its permissions can be set separately.
You can change other things about the table here, but it is not recommended unless you are writing a Provider.

Initial Create

(The file name starts with the YYYYMMDD... info for when it was generated.)

This file describes the database schema that will be created.

You can add plain SQL statements to the "Up" method, and they will be executed.

Sql(plainTextSQLStatement);

Entity Framework uses this, plus a system table in your database called "_Migrations" to determine what schema changes need to applied to a database.

Create Database

If your database does not exist yet:

1) In Package Manager Console > "Add-Migration <migration name>"
2) This will create a migration file with all necessary commands in it. It will be saved in the Migrations folder.
3) In Package Manager Console > "Update-Database -Verbose"
- by default, this will run the most recent migration file
- Verbose is optional, it outputs more information about what is happening

To create the database script, but not run it:
3) In Package Manager Console > "Update-Database -Script"
- the sql will be generated, but not automatically saved anywhere - so save that file

All migrations will also update the dbo.__MigrationHistory table that is automatically added to your database.
This table stores hashes of the database structure at different points in time. It is used by EF to figure out the next migration.

Possible Error: System.Data.SqlClient.SqlException (0x80131904)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
- When connecting to LocalDb, it worked after I manually made the connection through Visual Studio first:
- View menu > Service Explorer > right-click Data Connections > Add Connection > enter the info for your connection

Update

Manually update database based on Migration:
1) In Package Manager Console > "Update-Database -Verbose"
That's it.

If Automatic Migrations are enabled, the database schema can be updated.
If not, just the data can be updated.

If you get warnings, like the possibility of data loss because you made a charvar column shorter, you can override them:
1) In Package Manager Console > "Update-Database -Verbose -Force"

Note: if you generate a migration, but don't apply it, you'll need to delete it before creating a new migration.

Note: changes to the _MigrationHistory table will not be shown in the script you can inspect for a migration, but they do happen. Ex: changing the _MigrationHistory table's schema.

To target a specific migration:
1) In Package Manager Console > "Update-Database -TargetMigration <migration name> -Script"

Idempotent Migrations

How to update a database that is several migrations behind in updates?

"Idempotent" means something that can be applied repeatedly without causing damage. An idempotent migration script can tell if it has already been run, and will not run again if it has.

Create an idempotent script that takes all previous migrations into account:

Update-Database -Script -SourceMigration $InitialDatabase
(Exactly that text, nothing is replaced.)

Methods

EF 5 DbMigration Methods:
Create/Drop/Rename/MoveTable
Add/Drop/Alter/RenameColumn
Add/DropForeignKey
Add/DropPrimaryKey
Create/DropIndex
.Sql()

EF 6 New DbMigration Methods:
RenameIndex
Create/Drop/Alter/Rename/MoveStoredProcedure
AlterTableAnnotations(TColumns)

Creating a custom DbMigration Method to: CreateView

using System.Data.Entity.Migrations.Model;

public class CreateViewOperation : MigrationOperation
{
    public string ViewName { get; private set; }
    public string ViewQueryString { get; private set; }
    public override bool IsDestructiveChange { get { return false; } } //true if the operation might destroy data

    public CreateViewOperation(string viewName, string viewQueryString) : base(null)
    {
        ViewName = viewName;
        ViewQueryString = viewQueryString;
    }
}

public class RemoveViewOperation : MigrationOperation
{
    public string ViewName { get; private set; }
    public override bool IsDestructiveChange { get { return false; } } //true if the operation might destroy data
    
    public RemoveViewOperation(string viewName) : base(null)
    {
        ViewName = viewName;
    }
}

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;

public static class Extensions
{
    public static void CreateView(this DbMigration migration, string viewName, string viewQueryString)
    {
        ((IDbMigration)migration).AddOperation(new CreateViewOperation(viewName, viewQueryString);
    }
    
    public static void RemoveView(this DbMigration migration, string viewName)
    {
        ((IDbMigration)migration).AddOperation(new RemoveViewOperation(viewName);
    }
}

using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Utilities;
using System.Data.Entity.SqlServer;

public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(MigrationOperation migrationOperation)
    {
        if(migrationOperation is CreateViewOperation)
        {
            CreateViewOperation createViewOperation = (migrationOperation as CreateViewOperation);
            using(IndentedTextWriter writer = Writer())
            {
                writer.WriteLine("CREATE VIEW {0} AS {1};", operation.ViewName, operation.ViewQueryString);
                Statement(writer);
            }
        }
        else if(migrationOperation is RemoveViewOperation)
        {
            RemoveViewOperation removeViewOperation = (migrationOperation as RemoveViewOperation);
            using(IndentedTextWriter writer = Writer())
            {
                writer.WriteLine("DROP VIEW {0};", operation.ViewName);
                Statement(writer);
            }
        }
        //should there be a call to the base behavior on "else" here?
    }
}

using System.Data.Entity;
using System.Data.Entity.SqlServer;

public class CustomDbConfiguration : DbConfiguration
{
    public CustomDbConfiguration()
    {
        SetMigrationSqlGenerator(SqlProviderServices.ProviderInvariantName, 
            () => new CustomSqlServerMigrationSqlGenerator
        );
    }
}
To test this, generate an empty migration with "Add-Migration <migration name> -IgnoreChanges", and manually add this to the script:

public override Up()
{
    this.CreateView("viewName", "SELECT TOP....full view sql");
}

public override Down()
{
    this.RemoveView("viewName");
}
This example did not go over how to have the migration automatically generate these Create/RemoveViewOperations based on changes to your entities.

Multiple Models

If you have multiple distinct models targeting the same database:

You could have each model use a different schema, so that each model's _MigrationHistory table will also be in its own schema, and they won't conflict with each other.

You could also use ContextKeys to differentiate the Histories.

internal sealed class Configuration : DbMigrationsConfiguration
{
    public Configuration()
    {
        ContextKey = "MyUniqueKey";
    }
}
You should only be setting the ContextKey manually on a brand new database, with no history.
ContextKey defaults to the strongly-typed name of the Context class.

This does not support a Multi-Tenant Database, where one model is replicated several times within one database (such as if one database is supporting multiple separate clients).

This also does not support one entity being used in multiple models which all map to the same table in the database. Basically, don't share types or tables between models.

If you have multiple Contexts in one project, you'll need to specify a directory for each one to store its migrations in.
1) In Package Manager Console > "Enable-Migrations -ContextTypeName <context class name> -MigrationDirectory <directory name>"




Stored Procedures And Table Valued Functions

Topic is for Code-First only.

Insert, Update, Delete

You can map stored procedures to an entity's insert, update, and delete actions.

The stored procedures must accept the same parameters that those commands normally would.
Delete must accepts an Id parameter.
Insert and update must accept every field in the entity.
Insert must return the new primary key.

All parameters mapped to a stored procedure must come from entity properties. No literals, or formulas.

Configuration:

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Country>().MapToStoredProcedures();
        //the default is to expect stored procedures with names Country_Insert, Country_Update, and Country_Delete
    }
}
If you use Code Migrations, it will fill in default stored procedures that complete the basic operations.

Specifying non-conventional stored procedure names:

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //fluent syntax
        modelBuilder.Entity<Country>().MapToStoredProcedures(p =>
            p.Insert(x => x.HasName("InsertCountry")).Update(x => x.HasName("UpdateCountry")).Delete(x => x.HasName("DeleteCountry"))
        );
        //OR
        //block syntax
        modelBuilder.Entity<Country>().MapToStoredProcedures(p =>
            {
                p.Insert(x => x.HasName("InsertCountry"));
                p.Update(x => x.HasName("UpdateCountry"));
                p.Delete(x => x.HasName("DeleteCountry"));
            }
        );
    }
}

Specifying non-conventional parameter names:

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Country>().MapToStoredProcedures(p =>
            p.Insert(i => i.HasName("InsertCountry").Parameter(x => x.CountryName, "CName"))
            //property Country.CountryName will map to stored procedure parameter CName
        );
        
        //if the City to Country link is an Independent Association (we have no City.CountryId foreign key on the entity)
        //this is how we specify the non-conventional parameter name for the stored procedure
        modelBuilder.Entity<City>().MapToStoredProcedures(p =>
            p.Insert(i => i.Navigation<Country>(country => country.Cities, x => x.Parameter(country => country.Id, "CountryId")))
        );
    }
}

Specifying non-conventional return-value names:

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Country>().MapToStoredProcedures(p =>
            p.Insert(i => i.Result(r => r.Id, "NewId"))
        );
    }
}

Misc Mapping

You can also map the results of any Stored Procedure or Table Valued Function to an Entity type. The built-in way to do this is still very tricky, so the recommendation is to use this 3rd-party assembly.


Install-Package EntityFramework.CodeFirstStoreFunctions -Pre

Function Import

You can use the model editor to "Add Function Import" so you can use custom stored prodecures in your code. It'll make changes like these to your project:

EDMX:

    <Function Name="MyStoredProcedure" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
        <Parameter Name="Id" Type="int" Mode="In" />
    </Function>
    ...
    <FunctionImport Name="MyStoredProcedure" EntitySet="MyOutput" ReturnType="Collection(MyOutput)">
        <Parameter Name="Id" Mode="In" Type="Int32" />
    </FunctionImport>
    ...
    <FunctionImportMapping FunctionImportName="MyStoredProcedure" FunctionName="DBModel.Store.MyStoredProcedure">
    </FunctionImportMapping>

Add a stored procedure operation to the DbContext:

using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

public class MyContext : DbContext
{
    public List<MyOutput> MyMethod(int? id)
    {
        ObjectParameter idParameter = (id == null) ? new ObjectParameter("Id", typeof(int)) : new ObjectParameter("Id", id);
        
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<MyOutput>("MyStoredProcedure", idParameter).ToList();
        
    }
}

Call the stored procedure from code:

return myContext.MyMethod(1);

SQLQuery

You can execute stored procedure without function import with SQLQuery.


using System.Data.SqlClient;

public class MyContext : DbContext
{
    public List<MyOutput> MyMethod(int? id)
    {
        SqlParameter idParameter = new SqlParameter("Id", (object)id ?? DBNull.Value);

        return Database.SqlQuery<MyOutput>("exec MyStoredProcedure @id", idParameter).ToList<MyOutput>();
    }
}

Something to watch out for: mapping these results to an existing Entity type will work, but that entity's navigation properties will default to null.
Custom Conventions

This topic is for Code-First only.

You can create your own mapping conventions to override the defaults by using the Light Weight Conventions API or the more advanced Model-Based Conventions.

Light Weight Conventions API

These conventions specify how the Model will be generated.

Default convention example: String maps to varchar(4000) when using the SQL Server Provider.

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Property Conventions By Type
        //overriding the default string convention to varchar(150) instead of varchar(4000)
        modelBuilder.Properties<String>().Configure(s => s.HasMaxLength(150));
        
        //Property Conventions By Predicate
        //overriding the default column names for foreign keys
        modelBuilder.Properties().Where(p => p.Name.EndsWith("Id") && p.Name.Length > 2).Configure(
            c => {
                string propName = c.ClrPropertyInfo.Name;
                c.HasColumnName("fk" + propName.CapitalizeFirstLetter());
            }
        );
        
        //Type Conventions
        //placing all IHistory classes into the "History" schema
        modelBuilder.Types<IHistory>().Configure(c => c.ToTable(c.ClrType.Name, "History"));
    }
}
Todo: look through all the conventions you can override.

Custom Attributes

You can create custom Attributes and wire them into your custom Conventions.

Example: marking string properties as non-Unicode:

//custom attribute
[AttributeUsage(AttributeTargets.Property, AllowMultiple=false)] //this attribute can be placed on Properties only, and cannot be repeated on a Property
public class NonUnicode : Attribute
{
}

//using the custom attribute
[NonUnicode]
public string Description { get; set; }

//custom convention
modelBuilder.Properties().Where(p => p.GetCustomAttributes(false).OfType<NonUnicode>().Any()).Configure(c => c.IsUnicode(false));

Example: setting with schema a table should be under

//custom attribute
[AttributeUsage(AttributeTargets.Class, AllowMultiple=false)]
public class Schema : Attribute
{
    public string SchemaName { get; set; }
    public Schema(string schemaName)
    {
        SchemaName = schemaName;
    }
}

//using the custom attribute
[Schema("History")]
public class Customer : IHistory
{
}

//custom convention
modelBuilder.Types().Where(t => t.GetCustomAttributes(false).OfType<Schema>().Any()).Configure(c => c.ToTable(c.ClrType.Name, c.ClrType.GetCustomAttribute<Schema>().SchemaName));

EF has a built-in pluralization function:

new EnglishPluralizationService().Pluralize(word)

Convention Class

You can place all your custom Conventions into standard classes:

public class TableSchemaAttributeConvention : Convention
{
    public TableSchemaAttributeConvention()
    {
        Types().Where(t => t.GetCustomAttributes(false).OfType<Schema>().Any()).Configure(c => c.ToTable(c.ClrType.Name, c.ClrType.GetCustomAttribute<Schema>().SchemaName));
    }
}

public class GeographyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new TableSchemaAttributeConvention());
    
        base.OnModelCreating(modelBuilder);
    }
}

Execution Order

Execution order of Conventions:
(1) Internal Conventions
(2) Custom Conventions, in Code Order
(3) Configurations
The early Conventions/Configurations will be overriden by the later Conventions/Configurations, so put the most specific rules last.

Custom Conventions can be explicitly ordered:

public static class ConventionRules
{
    public static void Apply(DbModelBuilder modelBuilder)
    {
        //final order is A, B, C, D
        modelBuilder.Conventions.Add(new CustomConventionA());
        modelBuilder.Conventions.Add(new CustomConventionD());
        modelBuilder.Conventions.AddAfter<CustomConventionA>(new CustomConventionC());
        modelBuilder.Conventions.AddBefore<CustomConventionC>(new CustomConventionB());
    }
}

"modelBuilder.Conventions.Remove(x)" can be used to skip specific Internal Conventions.

Model-Based Conventions

Model-Based Conventions let you edit the Model, after its been generated.

Implement "IConceptualModelConvention<EntityMetadataType>" or "IStoreModelConvention<StoreMetadataType>".


modelBuilder.Conventions.Add(new CustomConceptualModelConvention()); //add these conventions in the same way

Example: apply foreign key naming convention to foreign keys in the database schema that are not Entity properties

public class ForeignKeyNameWithFkConvention : IStoreModelConvention<AssociationType>
{
    public void Apply(AssociationType associationType, DbModel model)
    {
        if(!associationType.IsForeignKey)
            return;
        if(!associationType.Constraint.FromRole.RelationshipMultiplicity != RelationshipMultiplicity.One)
            return;
        var associationProperty = associationType.Constraint.ToProperties;
        if(associationProperty[0].Name.EndsWith("Id"))
        {
            associationProperty[o].Name = "fk" + associationProperty[0].Name.CapitalizeFirstLetter();
        }
    }
}

Extensions

You can inherit from an existing class that implements one of the Convention interfaces, and override or extend its behavior.

Example: expect primary keys to be named "Key" instead of "Id" by overriding the KeyDiscoveryConvention.

AddFromAssembly

Add every single applicable class (convention or configuration) from an assembly to the modelBuilder.
You cannot control the order this way, so only use AddFromAssembly if the order does not matter at all.


modelBuilder.Configurations.AddFromAssembly(assembly);
modelBuilder.Conventions.AddFromAssembly(assembly);


//to add all from current assembly
modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly());

//to add all from an external assembly already referenced by your project
modelBuilder.Configurations.AddFromAssembly(typeof(MyConventionsContext).Assembly);

If you use an external assembly, it will need to include this minimal code:

using System.Data.Entity;

public class MyConventionsContext : DbContext //this class name is the one used to find the assembly in the example above
{
    //intentionally empty
}

using System.Data.Entity.Migrations;

internal sealed class MyMigrationConfiguration : DbMigrationConfiguration
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }
}
Indexes

Topic for Code-First only.

ForeignKeyIndexConvention

By default, migrations will add an index for each foreign key on each table. These indexes will be "non-unique" and "non-clustered".

To remove this convention:

modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();

Index A Column

Data Annotation: tell EF to add an index for this property/column:

//include reference to EntityFramework.dll in project
using System.ComponentModel.DataAnnotations.Schema;

public class Country
{
    [Index]
    public string Language { get; set; }
    [Index(IsClustered=true)] //this would, of course, conflict with the default clustered index created for the primary key
    public int Something { get; set; }
    [Index(IsUnique=true)] //note that this will be enforced in the database, but in the Entity Framework at runtime
    public int SomethingElse { get; set; }
}

Fluent API: tell EF to add an index for this property/column:

//adding the data annotation at runtime
modelBuilder.Entity<Country>().Property(p => p.Language).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute()));
Pluralization

By default, EF wants to generate entities with singularized names and database tables with pluralized names.

I'm accustomed to both entities and database tables having singularized names, which would require overriding how table names are generated from Code-First.

English


new EnglishPluralizationService().Pluralize(word);
new EnglishPluralizationService().Singularize(word);

Other Languages

The built-in grammar service only handles English.

Adding one pair at a time:

CustomPluralizationEntry[] customEntries = new CustomPluralizationEntry[] {
    new CustomPluralizationEntry("singleA", "pluralA"),
    new CustomPluralizationEntry("singleB", "pluralB")
};
EnglishPluralizationService service = new EnglishPluralizationService(customEntries);
string plural = service.Pluralize(single);

Create a custom service to apply language rules:

public class MyLanguageService : IPluralizationService
{
    public string Pluralize(string word)
    {
        //todo
    }
    
    public string Singularize(string word)
    {
        //todo
    {
}

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        SetPluralizationService(new MyLanguageService());
    }
}
You cannot derive from EnglishPluralizedService (sealed class), but if you still want to use it as part of your custom service, instantiate it internally when you need it.
Add Object

Stand-Alone Object

Stand alone new object:

Country country = new Country(name);
context.Countries.add(country);
context.SaveChanges();
Or:

Country country = new Country();
context.Entry(country).State = EntityState.Added;
context.SaveChanges();

Stand alone new object with children:

Country country = new Country();
context.Countries.add(country);     //add parent object to dbContext before attaching children
country.Cities = new List<City>();  //this could be done in the Country constructor
country.Cities.Add(new City(name)); //city is not explicitly added to dbContext
context.SaveChanges();

Attach To Existing

Attaching new object to existing object:

City city = new City(name);
Country country = context.Countries.First();
country.Cities.Add(city);
context.SaveChanges();

Query DbSet

About

Note that queries against a DataSet will not take new entities into account. These queries are only run against the data store. (This is avoid having to merge the data store with local cached values.)

LINQ

You can query DbSets using LINQ To Entities.

The query will not be executed until you either run a LINQ Execution Method (such as .ToList()) or enumerate the results (such as in a foreach statement).

See LINQ Notes for a lot more specifics.

Simple example:

List<Country> countries = dbContext.Countries.ToList();

If you use implicit enumeration to run a query, the database connection will remain open until the loop is complete. This can cause terrible slow-downs.

foreach(Country country in dbContext.Countries)
{
    ...
}

Make sure all filtering you do on data is done before any Execution Method or Enumeration. That way the minimum amount of data will be returned from the database.

The SqlClientProvider supports many SQL Server database functions as LINQ methods.
The SqlCEProvider(using System.Data.Entity.SqlServerCompact) is similar for SQL CE database functions.

EF 6 introduces LINQ support for ToString, Concat, and HasFlag (for enums).

[Flags] //can combine bitwise values
public enum Access
{
    Read = 1,
    Write = 2,
    Update = 4,
    Delete = 8
}
...
List<User> users = dbContext.Users.Where(u => u.Access.HasFlag(Access.Write)).ToList();

Find


Country country = dbContext.Countries.Find(idValue);
If the object already exists in memory, the result will be returned from local memory instead of querying the database.

This executes with the "SingleOrDefault" logic - if more than one record with this Id exists, it will throw an error.

SqlQuery

The query must return results that match the schema of the selected DbSet type.


List<Country> countries = dbContext.Countries.SqlQuery("exec MyStoredProc").ToList();

This is ok for stored procedures.
It is not recommended for custom SQL statements that could have been written in LINQ.

Raw SQL


dcContext.Database.ExecuteSqlCommand("UPDATE TableName SET Name = 'Other Name' WHERE Id = 1");

With parameters:

using System.Data.SqlClient;

string sql = @"UPDATE TableName SET FirstName = @FirstName WHERE Id = @Id";

dbContext.Database.ExecuteSqlCommand(
    sql, 
    new SqlParameter("@FirstName", firstName),
    new SqlParameter("@Id", id)
    );

Retrieve Related Objects

Eager Loading: bring all data back from database in one query.

Country country = dbContext.Countries.Include(c => c.Cities).First();
The more paths you include, the slower the query will run.
The data is flattened in the database query, and is materialized as different objects by Entity Framework.

Explicit Loading: run extra queries to retrieve related data as needed.

Country country = dbContext.Countries.First();
dbContext.Entry(country).Collection(c => c.Cities).Load();

Lazy Loading: run extra queries to retrieve related data as needed. The property must be marked as "virtual" to enable this.

public virtual List<City> Cities { get; set; }
...
Country country = dbContext.Countries.First();
List<City> cities = country.Cities.ToList();

Projections: return data that does not match a known entity type.

var anonymousObject = dbContext.Countries.Select(c => new { c.Name, c.Cities.Count(), c.Leader.Name } ).ToList();

AsNoTracking

Load data without the overhead of tracking the state of the object. Use this in disconnected applications when you know the object is about to pass out of the current context.


Country country = dbContext.Countries.AsNoTracking().First();

Async

Asynchronous queries

Performance

Before EF 6, a query like "dbContext.Countries.Where(c => idList.Contains(c.Id))" would generate a slow query using many "OR" statements. This was translated to an "IN" statement before the query was run on the database, but generating the first query was, itself, a slow process. In EF 6 and later, the first query will be generated using an "IN" statement, skipping that slow step.

The start up time (to instantiate a new DbContext) used to be much slower than it is now. Part of the problem was the "Mapping View Generation" step, which pre-generated some common-use SQL (specifically, a basic SELECT statement for each Entity type). Now you can set this generation to run at compile-time instead of run-time.
- To do this at design-time: Right-click on project > Entity Framework > Generate Views
- and the views will be compiled into your project

With EF 6 and later (distributed by NuGet), the compiler uses Just In Time compilation of the library. If this is a problem for you, you can use "ngen".
NGen will compile a C# assembly to the native-machine code of the current computer. That compiled library can be used by .Net instead of the uncompiled library. This will improve the start-up time of your program.

With EF 6, you can reuse an open database connection.

//Example test from EF 5, causing an error when you try to reuse an open connection
using(GeographyDbContext dbContext = new GeographyDbContext())
{
    DbConnection conn = dbContext.Database.Connection;
    conn.Open();
    dbContext.Countries.ToList(); //accessing the database throw exception "EntityConnection can only be constructed with a closed DbConnection
}
Assert.IsTrue(true);
Edit Object

Only the changed data will be updated.

Connected Applications

Editing within current context:

Country country = dbContext.Countries.First();
country.Name = "New Name";
dbContext.SaveChanges();

Disconnected Applications

Editing within a new context:

dbContext.Countries.Attach(country);
country.Name = "New Name";
dbContext.SaveChanges();
//This will only save changes made after the "Attach" statement.
Or

//Manually mark the object as edited.
//EVERY field in the object will be "updated", regardless of previous value.
country.Name = "New Name";
dbContext.Countries.Attach(country);
dbContext.Entry(country).State = EntityState.Modified;
dbContext.SaveChanges();

DO NOT use "dbContext.Countries.Add(country)" because it will ignore the country's Id and actually add a new record.

Editing a child object that has no foreign key to the parent in the Entity class:

//City is a child of Country, and some of the fields in City have been edited
public void SaveUpdatedCity(City updatedCity, int countryId)
{
    using(GeographyContext dbContext = new GeographyContext())
    {
        City city = dbContext.Cities.Include(c => c.Country).FirstOrDefault(c => c.Id == updatedCity.Id);
        dbContext.Entry(city).CurrentValues.SetValues(updatedCity);
        dbContext.SaveChanges();
    }
}
If the Entity class City had an explicit foreign key to Country, saving changes to City would be much less convoluted.

AcceptAllChanges

Both of these tell EF to discard all tracked changes, whether or not the database transaction succeeds. You might lose data this way.

dbContext.SaveChanges();
//or
dbContext.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
//because this enum value is 1, you'll also see
dbContext.SaveChanges(true);

This lets you check if the transaction failed before discarding the change tracking.

dbContext.SaveChanges(SaveOptions.None); //or dbContext.SaveChanges(false);
//check that transaction did not fail
dbContext.AcceptAllChanges();
Delete Object

Connected Application


Country country = dbContext.Countries.First();
dbContext.Countries.Remove(country);
dbContext.SaveChanges();

Disconnected Application


dbContext.Countries.Attach(country);
dbContext.Countries.Remove(country);
dbContext.SaveChanges();
Or

dbContext.Entry(country).State = EntityState.Deleted;
dbContext.SaveChanges();
//this works because the object is automatically Attached
Or

//Write your own stored procedure, so the object does not have to be loaded before it can be deleted
dbContext.Database.ExecuteSqlCommand("exec DeleteCountryById {0}", id);
//this is correctly parameterized

DbContext

OnModelCreating

You can extend the creation of the model.

This example shows keeping all persistence-related code in the DbContext, instead of spreading it among your Entities. (Optional, just someone's opinion).

public class GeographyContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Types().Configure(t => t.Ignore("LocalField")); //ignore "LocalField" on every/any type
        base.OnModelCreating(modelBuilder); //still run default behavior
    }
}

More configuration that cannot be set through Data Annotations:

public class GeographyContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Country>().HasMany(country => country.Cities).WithRequired(city => city.Mayor).WillCascadeOnDelete(false);
        
        modelBuilder.Entity<Country>().Property(country => country.FoundedTimeStamp).IsFixedLength();
    }
}
This is called "Fluent Configurations" because the configuration is set using the fluent api.

It is common to see these Fluent Configurations grouped into one-class-per-Entity, for organization purposes.

public class CountryConfiguration : EntityTypeConfiguration<Country>
{
    public CountryConfiguration()
    {
        HasMany(country => country.Cities).WithRequired(city => city.Mayor).WillCascadeOnDelete(false);
        Property(country => country.FoundedTimeStamp).IsFixedLength();
    }
}

public class GeographyContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CountryConfiguration());
    }
}

SaveChanges

You can extend the save method.

This example shows setting CreatedDate/ModifiedDate fields on every/any type that has them.

public class GeographyContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }

    protected override int SaveChanges()
    {
        var objQuery = this.ChangeTracker.Entries().Where(e => e.Entity is ITrackModification && (e.State == EntityState.Added || e.State == EntityState.Modified)).Select(e => e.Entity as ITrackModification);
        foreach(var obj in objQuery)
        {
            obj.DateModified = DateTime.Now;
            if(obj.DateCreated == DateTime.MinValue)
            {
                obj.DateCreated = obj.DateModified;
            }
        }
        int result = base.SaveChanges(); //still run default behavior
        return result;
    }
}

ChangeTracker

As of EF 6, you can easily check if any changes exist in a DbContext to be saved. This checks for changes to entities AND changes to relationships.


dbContext.ChangeTracker.HasChanges();
DbSet

Get

Search for an entity by id:

//returns object or null
Country country = _db.Countries.Find(id);

Include an associated collection when loading data:

//the country plus all its associated cities will load with one query (Eager Loading)
Country country = _db.Countries.Include(country => country.Cities).First();

Add


_db.Countries.Add(new Country(name, etc));
_db.SaveChanges();
DbConfiguration

About

Uses for the DbConfiguration class:
- an alternative to XML configurations in Web.config
- new features like "set execution strategy"
- provides hooks into the EF pipeline
- works with Code-First and EDMX models

Basic


using System.Data.Entity

internal class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        //code goes here
    }
}

Entity Framework is auto-discover the DbConfiguration in your project, and will auto-apply it to the DbContext you instantiate.

A DbConfiguration object will be initialized as the DbContext object is initialized for the first time. You cannot have more than one DbConfiguration instantiated in an application, even if you have multiple DbContext classes.

If your DbConfiguration is in an external project:

    <entityFramework codeConfigurationType="MyClassName,MyAssemblyName">
    </entityFramework>
OR

[DbConfigurationType(typeof(MyDbConfiguration))]
public class GeographyContext
{
}

Replace XML

Configurations you can set in a DbConfiguration class, instead of in the Web.config.


public MyDbConfiguration()
{
    this.SetDefaultConnectionFactory(new LocalDbConnectionFactory(connectionString));
}
Replaces:

    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory,EntityFramework">
            <parameters>
                <parameter value="v11.0"/>
            </parameters>
        </defaultConnectionFactory>
    </entityFramework>
If you use both, the XML will override the code.
Setting a "connectionString" in the XML will also override the in-code ConnectionFactory. (This will also result in the default Provider Service not being set.)


public MyDbConfiguration()
{
    this.SetDatabaseInitializer(new DropCreateDatabaseIfModelChanges<GeographyContext>());
    //if your DbContext is in a separate assembly from the DbConfiguration, this is probably not the place to set the DatabaseInitializer
}
Replaces:

    <entityFramework>
        <contexts>
            <context type="GeographyApp.DataLayer.GeographyContext,DataLayer">
                <databaseInitializer type="System.Data.Entity.DropCreateDatabaseIfModelChanges,EntityFramework"/>
            </context>
        </contexts>
    </entityFramework>

To disable all database initialization:

SetDatabaseInitializer<GeographyContext>(null);
//OR
SetDatabaseInitializer(new NullDatabaseInitializer<GeographyContext>());

Provider Services

Provider Services are APIs that provide Entity Framework with database-specific methods and functions.

Setting a provider in Web.config

    <connectionStrings>
        <add name="GeographyContext" connectionString="stuff" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <!-- OR -->
    <entityFramework>
        <providers> <!-- will be ignored if you also have a connectionString -->
            <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices"/>
        </providers>
    </entityFramework>

In code:

public MyDbConfiguration()
{
    //this only works for custom providers where you have access to the constructor
    SetProviderServices("System.Data.SqlClient", new SqlProviderServices());
}
Normally, you allow the ConnectionFactory to set the Provider Service.

Interceptors

For example, the IDbCommandInterceptor allows you to grab Entity Framework commands just before they are sent to the database, or the results as they return. This is commonly used to logging database interactions.

Interceptor Interfaces:
- IDbCommandInterceptor
- IDbConnectionInterceptor
- IDbCommandTreeInterceptor
- IDbConfigurationInterceptor

You can use multiple interceptors, of any type, at one time. Interceptors of the same type will be used in the order they are added to the DbConfiguration.

//add interceptors when DbConfiguration is initialized
internal class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        AddInterceptor(new InterceptorA());
        AddInterceptor(new InterceptorB());
        RemoveInterceptor(new InterceptorA());
    }
}

//add interceptors on the fly
//this code could execute anywhere
DbInterception.Add(new InterceptorC());
DbInterception.Remove(new InterceptorC());

Setup example:

using System.Data.Entity.Infrastructure.Interception;

public class CustomInterceptor : IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    //etc for the rest of the Interface methods
}
...
internal class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        AddInterceptor(new CustomInterceptor());
    }
}

Example: Use IDbCommandTreeInterceptor to perform Soft Deletes (marking a row as "deleted" instead of truly deleting it)
This examples lets you use the normal "delete" command to perform soft deletes, AND auto-filters all queries to not return "deleted" records.


[SoftDelete("IsDeleted")] //custom attribute sets which property indicates deleted-state
public class Country
{
    public int CountryId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
}
The SoftDeleteInterceptor code is too extensive to show here.
Summary: Queries are updated to always filter on "WHERE IsDeleted = 0" and Delete commands are changed into "UPDATE SET IsDeleted = 1".
For all details, see Rowan Miller on channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DEV-B417.

On GitHub, there is also the project jbogard/EntityFramework.Filters, which is a library to applying filters to your queries using this same design pattern.

Dependency Resolution


System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver

This is a pattern of relying on Interfaces, so a particular implementation is easy to set or remove.

The "RootDependencyResolver" class sets all the default implementations that Entity Framework uses.

Data Annotations

Basic

Data annotations can affect the database schema Entity Framework generates, and the form validation that ASP.Net web pages generate.


using System.ComponentModel.DataAnnotations;
...
public class MyEntity
{
    [Range(1,10)] //affects form validation
    public int Rating { get; set; }
    
    [Required]          //affects database schema and form validation
    [StringLength(100)] //affects database schema and form validation
    public string Name { get; set; }
    
    [Display(Name="First Name")] //affects web page display
    public string FirstName { get; set; }
    
    [DisplayFormat(DataFormatString="YYYY-MM-DD")] //affects web page display
    [DisplayFormat(NullDisplayText="Pending")] //affects web page display
    public DataTime? StartDate { get; set; }
    
    [Range(1900,2050,ErrorMessage="My custom error message for {0}."] //any annotation can have a custom error message format
    public int Year { get; set; }
}

TODO: there are many more than this

Schema

Schema-specific annotations. I'd probably recommend defining these inside DbContext instead of on the Entity, since it is information specific to data storage.


[Table("TableName")] //specify table name different from the class name
class EntityName
{
    [Column("ColumnName")] //specify column name different from property name
    [Column(TypeName="timestamp")] //specify column data type
    [Timestamp]
    public byte[] TimeStamp { get; set; }
}

Custom Annotations

Custom validation can only be run on the server-side. It will not be translated into JavaScript to run on the client-side.

Example:

public class MaxWordsAttribute : ValidationAttribute
{
    private readonly int maxWords;
    
    public MaxWordsAttribute(int maxWords) : base("{0} has too many words.") //sets the error string format
    {
        this.maxWords = maxWords;
    }
    
    protected override ValidationResult IsValid(object value, ValidationContext context)
    {
        if(value has too many words)
        {
            string message = FormatErrorMessage(context.DisplayName); //FormatErrorMessage is inherited from the base class
            return new ValidationResult(message);
        }
        return ValidationResult.Success;
    }
}
...
public class MyModel
{
    [MaxWords(10)]
    public string Text { get; set; }
}

Custom Validation

You may need validation that looks at multiple properties at the same time.


public class MyModel : IValidatableObject
{
    public IEnumerable<ValidationResult> Validate(ValidatationContext context)
    {
        //implement custom, complicated validation
    }
}

EDMX Structure

Editing the database-first definition through the XML file directly.

My team started editing the XML directly when our design got ahead of what the visual designer could handle. (Were we still in EF 4 or 5 at that point? This may not be relevant anymore.)

Basic Structure


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <!-- Entity Framework runtime content -->
    <edmx:Runtime>
        <!-- SSDL -->
        <!-- how the database is setup -->
        <edmx:StorageModels>
        </edmx:StorageModels>
        <!-- CSDL -->
        <!-- how the programmed objects are setup -->
        <edmx:ConceptualModels>
        </edmx:ConceptualModels>
        <!-- C-S Mapping -->
        <!-- converting between database and programmed objects -->
        <edmx:Mappings>
        </edmx:Mappings>
    </edmx:Runtime>
    <!-- Entity Framework designer content -->
    <!-- how the model is visually displayed -->
    <edmx:Designer>
    </edmx:Designer>
</edmx:Edmx>

Emtpy EDMX


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
        </edmx:ConceptualModels>
        <edmx:Mappings>
        </edmx:Mappings>
    </edmx:Runtime>
</edmx:Edmx>

EDMX Examples

One Table

A stand-alone table with columns of various types


CREATE TABLE [player].[Warrior] (
    [WarriorId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[IsMale]            BIT            NOT NULL
    ,[Level]            INT            NOT NULL    DEFAULT(1)
    ,[Weapon]             VARCHAR(20)    NULL
    ,[Armor]             VARCHAR(20)    NULL
    ,[LastLoginDate]    DATETIME     NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
);


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Warrior" EntityType="EnvironmentModel.Store.Warrior" store:Type="Tables" Schema="player" />
                </EntityContainer>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="IsMale" Type="bit" Nullable="false" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="Weapon" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="Armor" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="LastLoginDate" Type="datetime" Nullable="true" />
                    <!-- note that data types in SSDL are SQL data types -->
                </EntityType>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Warriors" EntityType="EnvironmentModel.Warrior" />
                </EntityContainer>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="IsMale" Type="Boolean" Nullable="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="Weapon" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="Armor" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="LastLoginDate" Type="DateTime" Nullable="true" />
                    <!-- note that data types in CSDL are C# data types -->
                </EntityType>
            </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Warriors">
                        <EntityTypeMapping TypeName="EnvironmentModel.Warrior">
                            <MappingFragment StoreEntitySet="Warrior">
                                <ScalarProperty Name="WarriorId" ColumnName="WarriorId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="IsMale" ColumnName="IsMale" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="Weapon" ColumnName="Weapon" />
                                <ScalarProperty Name="Armor" ColumnName="Armor" />
                                <ScalarProperty Name="LastLoginDate" ColumnName="LastLoginDate" />
                                <!-- Name is the CSDL property name, ColumnName is the SSDL database column name -->
                                <!-- your property names do not have to be the same as the database column names -->
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
</edmx:Edmx>

One-to-Many Relationship


CREATE TABLE [player].[Warrior] (
    [WarriorId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[IsMale]            BIT            NOT NULL
    ,[Level]            INT            NOT NULL    DEFAULT(1)
    ,[Weapon]             VARCHAR(20)    NULL
    ,[Armor]             VARCHAR(20)    NULL
    ,[LastLoginDate]    DATETIME     NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [player].[Loot] (
    [LootId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[fxWarriorId]        BIGINT        NOT NULL
    ,[Description]        VARCHAR(50)    NOT NULL
    ,[Quantity]            INT            NOT NULL
    ,CONSTRAINT PK_Loot PRIMARY KEY CLUSTERED (LootId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT [FK_Loot_Warrior] FOREIGN KEY ([fxWarriorId]) REFERENCES [player].[Warrior] ([WarriorId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Warrior" EntityType="EnvironmentModel.Store.Warrior" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Loot" EntityType="EnvironmentModel.Store.Loot" store:Type="Tables" Schema="player" />
                </EntityContainer>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="IsMale" Type="bit" Nullable="false" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="Weapon" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="Armor" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="LastLoginDate" Type="datetime" Nullable="true" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="fxWarriorId" Type="bigint" Nullable="false" />
                    <Property Name="Description" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Quantity" Type="int" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Loot_Warrior" Association="EnvironmentModel.Store.FK_Loot_Warrior">
                    <End Role="Loot" EntitySet="Loot" />
                    <End Role="Warrior" EntitySet="Warrior" />
                </AssociationSet>
                <Association Name="FK_Loot_Warrior">
                    <End Role="Warrior" Type="EnvironmentModel.Store.Warrior" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Store.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Warrior">
                            <PropertyRef Name="WarriorId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxWarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Warriors" EntityType="EnvironmentModel.Warrior" />
                    <EntitySet Name="Loots" EntityType="EnvironmentModel.Loot" />
                </EntityContainer>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="IsMale" Type="Boolean" Nullable="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="Weapon" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="Armor" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="LastLoginDate" Type="DateTime" Nullable="true" />
                    <NavigationProperty Name="Inventory" Relationship="EnvironmentModel.FK_Loot_Warrior" FromRole="Warrior" ToRole="Loot" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="fxWarriorId" Type="Int64" Nullable="false" />
                    <Property Name="Description" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Quantity" Type="Int32" Nullable="false" />
                    <NavigationProperty Name="Warrior" Relationship="EnvironmentModel.FK_Loot_Warrior" FromRole="Loot" ToRole="Warrior" />
                    <!-- note that Navigation Properies are optional; only create this one if you need to traverse loot.Warrior in the code -->
                    <!-- each Navigation Property will slow down your queries a little, it adds up -->
                </EntityType>
                <AssociationSet Name="FK_Loot_Warrior" Association="EnvironmentModel.FK_Loot_Warrior">
                    <End Role="Warrior" EntitySet="Warriors" />
                    <End Role="Loot" EntitySet="Loots" />
                </AssociationSet>
                <Association Name="FK_Loot_Warrior">
                    <End Role="Warrior" Type="EnvironmentModel.Warrior" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Warrior">
                            <PropertyRef Name="WarriorId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxWarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Warriors">
                        <EntityTypeMapping TypeName="EnvironmentModel.Warrior">
                            <MappingFragment StoreEntitySet="Warrior">
                                <ScalarProperty Name="WarriorId" ColumnName="WarriorId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="IsMale" ColumnName="IsMale" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="Weapon" ColumnName="Weapon" />
                                <ScalarProperty Name="Armor" ColumnName="Armor" />
                                <ScalarProperty Name="LastLoginDate" ColumnName="LastLoginDate" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="Loots">
                        <EntityTypeMapping TypeName="EnvironmentModel.Loot">
                            <MappingFragment StoreEntitySet="Loot">
                                <ScalarProperty Name="LootId" ColumnName="LootId" />
                                <ScalarProperty Name="fxWarriorId" ColumnName="fxWarriorId" />
                                <ScalarProperty Name="Description" ColumnName="Description" />
                                <ScalarProperty Name="Quantity" ColumnName="Quantity" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
</edmx:Edmx>

Sub Tables, Loading From Views

This example shows how to implement a table-subtable relationship, and how to load from a view instead of a table.

A warning (at least for Entity Framework 5 and below): if you have a lot of subclasses to one class, then querying at the base class level will generate a very large SQL query because Entity Framework will join on every single subtable, even if those columns are not required.

Slow:

Player[] recentPlayers = entities.Players.Where(p=>p.LastLoginDate > lastMonth).ToArray()

I think it was around the 19th subtable that our query became so large the database rejected it.

A specific query will be much faster.

Fast:

Warrior[] warriors = entities.Players.OfType<Warrior>().ToArray()


CREATE TABLE [player].[Player] (
    [PlayerId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[IsMale]            BIT            NOT NULL
    ,[Level]            INT            NOT NULL    DEFAULT(1)
    ,[LastLoginDate]    DATETIME     NULL
    ,[AccountIsActive]    BIT            NOT NULL
    ,CONSTRAINT PK_Player PRIMARY KEY CLUSTERED (PlayerId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [player].[Warrior] (
    [WarriorId]            BIGINT        NOT NULL
    ,[Weapon]             VARCHAR(20)    NULL
    ,[Armor]             VARCHAR(20)    NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Warrior_Player FOREIGN KEY ([WarriorId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE [player].[Mage] (
    [MageId]            BIGINT        NOT NULL
    ,[Mana]             INT            NOT NULL
    ,[ManaRegenRate]    DECIMAL        NOT NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Warrior_Player FOREIGN KEY ([WarriorId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE [player].[Loot] (
    [LootId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[fxPlayerId]        BIGINT        NOT NULL
    ,[Description]        VARCHAR(50)    NOT NULL
    ,[Quantity]            INT            NOT NULL
    ,CONSTRAINT PK_Loot PRIMARY KEY CLUSTERED (LootId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT [FK_Loot_Player] FOREIGN KEY ([fxPlayerId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE VIEW [player].[activePlayer] AS
    SELECT
        PlayerId
        ,IsMale
        ,Level
        ,LastLoginDate
        ,AccountIsActive
    FROM player.Player
    WHERE Player.AccountIsActive = 1;


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Player" EntityType="EnvironmentModel.Store.Player" Table="activePlayer" Schema="player" />
                    <EntitySet Name="Warrior" EntityType="EnvironmentModel.Store.Warrior" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Mage" EntityType="EnvironmentModel.Store.Mage" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Loot" EntityType="EnvironmentModel.Store.Loot" store:Type="Tables" Schema="player" />
                </EntityContainer>
                <EntityType Name="Player">
                    <Key>
                        <PropertyRef Name="PlayerId" />
                    </Key>
                    <Property Name="PlayerId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="IsMale" Type="bit" Nullable="false" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="LastLoginDate" Type="datetime" Nullable="true" />
                    <Property Name="AccountIsActive" Type="bit" Nullable="false" />
                </EntityType>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="bigint" Nullable="false" />
                    <Property Name="Weapon" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="Armor" Type="varchar" Nullable="true" MaxLength="20" />
                </EntityType>
                <EntityType Name="Mage">
                    <Key>
                        <PropertyRef Name="MageId" />
                    </Key>
                    <Property Name="MageId" Type="bigint" Nullable="false" />
                    <Property Name="Mana" Type="int" Nullable="false" />
                    <Property Name="ManaRegenRate" Type="decimal" Nullable="false" Scale="3" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="fxPlayerId" Type="bigint" Nullable="false" />
                    <Property Name="Description" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Quantity" Type="int" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Warrior_Player" Association="EnvironmentModel.Store.FK_Warrior_Player">
                    <End Role="Player" EntitySet="Player" />
                    <End Role="Warrior" EntitySet="Warrior" />
                </AssociationSet>
                <AssociationSet Name="FK_Mage_Player" Association="EnvironmentModel.Store.FK_Mage_Player">
                    <End Role="Player" EntitySet="Player" />
                    <End Role="Mage" EntitySet="Mage" />
                </AssociationSet>
                <AssociationSet Name="FK_Loot_Player" Association="EnvironmentModel.Store.FK_Loot_Player">
                    <End Role="Loot" EntitySet="Loot" />
                    <End Role="Player" EntitySet="Player" />
                </AssociationSet>
                <Association Name="FK_Warrior_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Warrior" Type="EnvironmentModel.Store.Warrior" Multiplicity="0..1" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Warrior">
                            <PropertyRef Name="WarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Mage_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Mage" Type="EnvironmentModel.Store.Mage" Multiplicity="0..1" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Mage">
                            <PropertyRef Name="MageId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Loot_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Store.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxPlayerId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Players" EntityType="EnvironmentModel.Player" />
                    <EntitySet Name="Loots" EntityType="EnvironmentModel.Loot" />
                </EntityContainer>
                <EntityType Name="Player" Abstract="false" >
                    <Key>
                        <PropertyRef Name="PlayerId" />
                    </Key>
                    <Property Name="PlayerId" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="IsMale" Type="Boolean" Nullable="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="LastLoginDate" Type="DateTime" Nullable="true" />
                    <Property Name="AccountIsActive" Type="Boolean" Nullable="false" />
                    <NavigationProperty Name="Inventory" Relationship="EnvironmentModel.FK_Loot_Player" FromRole="Player" ToRole="Loot" />
                </EntityType>
                <EntityType Name="Warrior" BaseType="EnvironmentModel.Player">
                    <Property Name="Weapon" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="Armor" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                </EntityType>
                <EntityType Name="Mage" BaseType="EnvironmentModel.Player">
                    <Property Name="Mana" Type="Int32" Nullable="false" />
                    <Property Name="ManaRegenRate" Type="Decimal" Nullable="false" Precision="8" Scale="3" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="fxPlayerId" Type="Int64" Nullable="false" />
                    <Property Name="Description" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Quantity" Type="Int32" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Loot_Player" Association="EnvironmentModel.FK_Loot_Player">
                    <End Role="Player" EntitySet="Players" />
                    <End Role="Loot" EntitySet="Loots" />
                </AssociationSet>
                <Association Name="FK_Loot_Player">
                    <End Role="Player" Type="EnvironmentModel.Player" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxPlayerId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Players">
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Player)">
                            <MappingFragment StoreEntitySet="Player">
                                <ScalarProperty Name="PlayerId" ColumnName="PlayerId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="IsMale" ColumnName="IsMale" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="LastLoginDate" ColumnName="LastLoginDate" />
                                <ScalarProperty Name="AccountIsActive" ColumnName="AccountIsActive" />
                            </MappingFragment>
                        </EntityTypeMapping>
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Warrior)">
                            <MappingFragment StoreEntitySet="Warrior">
                                <ScalarProperty Name="PlayerId" ColumnName="WarriorId" />
                                <ScalarProperty Name="Weapon" ColumnName="Weapon" />
                                <ScalarProperty Name="Armor" ColumnName="Armor" />
                            </MappingFragment>
                        </EntityTypeMapping>
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Mage)">
                            <MappingFragment StoreEntitySet="Mage">
                                <ScalarProperty Name="PlayerId" ColumnName="MageId" />
                                <ScalarProperty Name="Mana" ColumnName="Mana" />
                                <ScalarProperty Name="ManaRegenRate" ColumnName="ManaRegenRate" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="Loots">
                        <EntityTypeMapping TypeName="EnvironmentModel.Loot">
                            <MappingFragment StoreEntitySet="Loot">
                                <ScalarProperty Name="LootId" ColumnName="LootId" />
                                <ScalarProperty Name="fxWarriorId" ColumnName="fxWarriorId" />
                                <ScalarProperty Name="Description" ColumnName="Description" />
                                <ScalarProperty Name="Quantity" ColumnName="Quantity" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
</edmx:Edmx>

Sub Tables

Starts with a copy of the previous example, without the view. Also shows a cascading delete.


CREATE TABLE [player].[Player] (
    [PlayerId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[IsMale]            BIT            NOT NULL
    ,[Level]            INT            NOT NULL    DEFAULT(1)
    ,[LastLoginDate]    DATETIME     NULL
    ,[AccountIsActive]    BIT            NOT NULL
    ,CONSTRAINT PK_Player PRIMARY KEY CLUSTERED (PlayerId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [player].[Warrior] (
    [WarriorId]            BIGINT        NOT NULL
    ,[Weapon]             VARCHAR(20)    NULL
    ,[Armor]             VARCHAR(20)    NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Warrior_Player FOREIGN KEY ([WarriorId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE [player].[Mage] (
    [MageId]            BIGINT        NOT NULL
    ,[Mana]             INT            NOT NULL
    ,[ManaRegenRate]    DECIMAL        NOT NULL
    ,CONSTRAINT PK_Warrior PRIMARY KEY CLUSTERED (WarriorId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Warrior_Player FOREIGN KEY ([WarriorId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE [player].[Loot] (
    [LootId]            BIGINT        NOT NULL    IDENTITY (1, 1)
    ,[fxPlayerId]        BIGINT        NOT NULL
    ,[Description]        VARCHAR(50)    NOT NULL
    ,[Quantity]            INT            NOT NULL
    ,CONSTRAINT PK_Loot PRIMARY KEY CLUSTERED (LootId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Loot_Player FOREIGN KEY ([fxPlayerId]) REFERENCES [player].[Player] ([PlayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE [player].[Sidekick] (
    [SideKickId]            BIGINT        NOT NULL    IDENTITY (1,1)
    ,[fxWarriorId]             BIGINT         NOT NULL
    ,[Name]                    VARCHAR(50)    NOT NULL
    ,[Level]                INT            NOT NULL
    ,CONSTRAINT PK_Sidekick PRIMARY KEY CLUSTERED (SidekickId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Sidekick_Warrior FOREIGN KEY ([fxWarriorId]) REFERENCES [player].[Warrior] ([WarriorId]) ON DELETE CASCADE ON UPDATE NO ACTION
);


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Player" EntityType="EnvironmentModel.Store.Player" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Warrior" EntityType="EnvironmentModel.Store.Warrior" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Mage" EntityType="EnvironmentModel.Store.Mage" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Loot" EntityType="EnvironmentModel.Store.Loot" store:Type="Tables" Schema="player" />
                    <EntitySet Name="Sidekick" EntityType="EnvironmentModel.Store.Sidekick" store:Type="Tables" Schema="player" />
                </EntityContainer>
                <EntityType Name="Player">
                    <Key>
                        <PropertyRef Name="PlayerId" />
                    </Key>
                    <Property Name="PlayerId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="IsMale" Type="bit" Nullable="false" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="LastLoginDate" Type="datetime" Nullable="true" />
                    <Property Name="AccountIsActive" Type="bit" Nullable="false" />
                </EntityType>
                <EntityType Name="Warrior">
                    <Key>
                        <PropertyRef Name="WarriorId" />
                    </Key>
                    <Property Name="WarriorId" Type="bigint" Nullable="false" />
                    <Property Name="Weapon" Type="varchar" Nullable="true" MaxLength="20" />
                    <Property Name="Armor" Type="varchar" Nullable="true" MaxLength="20" />
                </EntityType>
                <EntityType Name="Mage">
                    <Key>
                        <PropertyRef Name="MageId" />
                    </Key>
                    <Property Name="MageId" Type="bigint" Nullable="false" />
                    <Property Name="Mana" Type="int" Nullable="false" />
                    <Property Name="ManaRegenRate" Type="decimal" Nullable="false" Scale="3" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="fxPlayerId" Type="bigint" Nullable="false" />
                    <Property Name="Description" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Quantity" Type="int" Nullable="false" />
                </EntityType>
                <EntityType Name="Sidekick">
                    <Key>
                        <PropertyRef Name="SidekickId" />
                    </Key>
                    <Property Name="SidekickId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="fxWarriorId" Type="bigint" Nullable="false" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Level" Type="int" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Warrior_Player" Association="EnvironmentModel.Store.FK_Warrior_Player">
                    <End Role="Player" EntitySet="Player" />
                    <End Role="Warrior" EntitySet="Warrior" />
                </AssociationSet>
                <AssociationSet Name="FK_Mage_Player" Association="EnvironmentModel.Store.FK_Mage_Player">
                    <End Role="Player" EntitySet="Player" />
                    <End Role="Mage" EntitySet="Mage" />
                </AssociationSet>
                <AssociationSet Name="FK_Loot_Player" Association="EnvironmentModel.Store.FK_Loot_Player">
                    <End Role="Loot" EntitySet="Loot" />
                    <End Role="Player" EntitySet="Player" />
                </AssociationSet>
                <AssociationSet Name="FK_Sidekick_Warrior" Association="EnvironmentModel.Store.FK_Sidekick_Warrior">
                    <End Role="Warrior" EntitySet="Warrior" />
                    <End Role="Sidekick" EntitySet="Sidekick" />
                </AssociationSet>
                <Association Name="FK_Warrior_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Warrior" Type="EnvironmentModel.Store.Warrior" Multiplicity="0..1" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Warrior">
                            <PropertyRef Name="WarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Mage_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Mage" Type="EnvironmentModel.Store.Mage" Multiplicity="0..1" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Mage">
                            <PropertyRef Name="MageId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Loot_Player">
                    <End Role="Player" Type="EnvironmentModel.Store.Player" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Store.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxPlayerId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Sidekick_Warrior">
                    <End Role="Warrior" Type="EnvironmentModel.Store.Warrior" Multiplicity="1">
                        <OnDelete Action="Cascade" />
                    </End>
                    <End Role="Sidekick" Type="EnvironmentModel.Store.Sidekick" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Warrior">
                            <PropertyRef Name="WarriorId" />
                        </Principal>
                        <Dependent Role="Sidekick">
                            <PropertyRef Name="fxWarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Players" EntityType="EnvironmentModel.Player" />
                    <EntitySet Name="Loots" EntityType="EnvironmentModel.Loot" />
                    <EntitySet Name="Sidekicks" EntityType="EnvironmentModel.Sidekick" />
                </EntityContainer>
                <EntityType Name="Player" Abstract="false" >
                    <Key>
                        <PropertyRef Name="PlayerId" />
                    </Key>
                    <Property Name="PlayerId" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="IsMale" Type="Boolean" Nullable="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="LastLoginDate" Type="DateTime" Nullable="true" />
                    <Property Name="AccountIsActive" Type="Boolean" Nullable="false" />
                    <NavigationProperty Name="Inventory" Relationship="EnvironmentModel.FK_Loot_Player" FromRole="Player" ToRole="Loot" />
                </EntityType>
                <EntityType Name="Warrior" BaseType="EnvironmentModel.Player">
                    <Property Name="Weapon" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="Armor" Type="String" Nullable="true" MaxLength="20" Unicode="false" FixedLength="false" />
                    <NavigationProperty Name="Team" Relationship="EnvironmentModel.FK_Warrior_Sidekick" FromRole="Warrior" ToRole="Sidekick" />
                </EntityType>
                <EntityType Name="Mage" BaseType="EnvironmentModel.Player">
                    <Property Name="Mana" Type="Int32" Nullable="false" />
                    <Property Name="ManaRegenRate" Type="Decimal" Nullable="false" Precision="8" Scale="3" />
                </EntityType>
                <EntityType Name="Loot">
                    <Key>
                        <PropertyRef Name="LootId" />
                    </Key>
                    <Property Name="LootId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="fxPlayerId" Type="Int64" Nullable="false" />
                    <Property Name="Description" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Quantity" Type="Int32" Nullable="false" />
                </EntityType>
                <EntityType Name="Sidekick">
                    <Key>
                        <PropertyRef Name="SidekickId" />
                    </Key>
                    <Property Name="SidekickId" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="fxWarriorId" Type="Int64" Nullable="false" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Loot_Player" Association="EnvironmentModel.FK_Loot_Player">
                    <End Role="Player" EntitySet="Players" />
                    <End Role="Loot" EntitySet="Loots" />
                </AssociationSet>
                <AssociationSet Name="FK_Sidekick_Warrior" Association="EnvironmentModel.FK_Sidekick_Warrior">
                    <End Role="Warrior" EntitySet="Players" />
                    <End Role="Sidekick" EntitySet="Sidekicks" />
                </AssociationSet>
                <Association Name="FK_Loot_Player">
                    <End Role="Player" Type="EnvironmentModel.Player" Multiplicity="1" />
                    <End Role="Loot" Type="EnvironmentModel.Loot" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Player">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Loot">
                            <PropertyRef Name="fxPlayerId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Sidekick_Warrior">
                    <End Role="Warrior" Type="EnvironmentModel.Warrior" Multiplicity="1">
                        <OnDelete Action="Cascade" />
                    </End>
                    <End Role="Sidekick" Type="EnvironmentModel.Sidekick" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Warrior">
                            <PropertyRef Name="PlayerId" />
                        </Principal>
                        <Dependent Role="Sidekick">
                            <PropertyRef Name="fxWarriorId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Players">
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Player)">
                            <MappingFragment StoreEntitySet="Player">
                                <ScalarProperty Name="PlayerId" ColumnName="PlayerId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="IsMale" ColumnName="IsMale" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="LastLoginDate" ColumnName="LastLoginDate" />
                                <ScalarProperty Name="AccountIsActive" ColumnName="AccountIsActive" />
                            </MappingFragment>
                        </EntityTypeMapping>
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Warrior)">
                            <MappingFragment StoreEntitySet="Warrior">
                                <ScalarProperty Name="PlayerId" ColumnName="WarriorId" />
                                <ScalarProperty Name="Weapon" ColumnName="Weapon" />
                                <ScalarProperty Name="Armor" ColumnName="Armor" />
                            </MappingFragment>
                        </EntityTypeMapping>
                        <EntityTypeMapping TypeName="IsTypeOf(EnvironmentModel.Mage)">
                            <MappingFragment StoreEntitySet="Mage">
                                <ScalarProperty Name="PlayerId" ColumnName="MageId" />
                                <ScalarProperty Name="Mana" ColumnName="Mana" />
                                <ScalarProperty Name="ManaRegenRate" ColumnName="ManaRegenRate" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="Loots">
                        <EntityTypeMapping TypeName="EnvironmentModel.Loot">
                            <MappingFragment StoreEntitySet="Loot">
                                <ScalarProperty Name="LootId" ColumnName="LootId" />
                                <ScalarProperty Name="fxWarriorId" ColumnName="fxWarriorId" />
                                <ScalarProperty Name="Description" ColumnName="Description" />
                                <ScalarProperty Name="Quantity" ColumnName="Quantity" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="Sidekicks">
                        <EntityTypeMapping TypeName="EnvironmentModel.Sidekick">
                            <MappingFragment StoreEntitySet="Sidekick">
                                <ScalarProperty Name="SidekickId" ColumnName="SidekickId" />
                                <ScalarProperty Name="fxWarriorId" ColumnName="fxWarriorId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
</edmx:Edmx>

Many-to-Many Relationship


CREATE TABLE [map].[Country] (
    [CountryId]            INT            NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[SquareMiles]        DECIMAL        NOT NULL
    ,[Capital]            VARCHAR(50)    NOT NULL
    ,CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [commerce].[TradeGood] (
    [TradeGoodId]        INT            NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,CONSTRAINT PK_TradeGood PRIMARY KEY CLUSTERED (TradeGoodId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [commerce].[CountryProducesTradeGood] (
    [fxCountryId]        INT        NOT NULL
    ,[fxTradeGoodId]    INT        NOT NULL
    ,CONSTRAINT PK_CountryProducesTradeGood PRIMARY KEY CLUSTERED (fxCountryId, fxTradeGoodId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_CountryProducesTradeGood_Country FOREIGN KEY ([fxCountryId]) REFERENCES [map].[Country] ([CountryId]) ON DELETE NO ACTION ON UPDATE NO ACTION
    ,CONSTRAINT FK_CountryProducesTradeGood_TradeGood FOREIGN KEY ([fxTradeGoodId]) REFERENCES [commerce].[TradeGood] ([TradeGoodId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Country" EntityType="EnvironmentModel.Store.Country" store:Type="Tables" Schema="map" />
                    <EntitySet Name="TradeGood" EntityType="EnvironmentModel.Store.TradeGood" store:Type="Tables" Schema="commerce" />
                    <EntitySet Name="CountryProducesTradeGood" EntityType="EnvironmentModel.Store.CountryProducesTradeGood" store:Type="Tables" Schema="commerce" />
                </EntityContainer>
                <EntityType Name="Country">
                    <Key>
                        <PropertyRef Name="CountryId" />
                    </Key>
                    <Property Name="CountryId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="SquareMiles" Type="decimal" Nullable="false" />
                    <Property Name="Capital" Type="varchar" Nullable="false" MaxLength="50" />
                </EntityType>
                <EntityType Name="TradeGood">
                    <Key>
                        <PropertyRef Name="TradeGoodId" />
                    </Key>
                    <Property Name="TradeGoodId" Type="int" Nullable="false" />
                    <Property Name="Name" Type="varchar" Nullable="true" MaxLength="50" />
                </EntityType>
                <EntityType Name="CountryProducesTradeGood">
                    <Key>
                        <PropertyRef Name="fxCountryId" />
                        <PropertyRef Name="fxTradeGoodId" />
                    </Key>
                    <Property Name="fxCountryId" Type="int" Nullable="false" />
                    <Property Name="fxTradeGoodId" Type="int" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_CountryProducesTradeGood_Country" Association="EnvironmentModel.Store.FK_CountryProducesTradeGood_Country">
                    <End Role="Country" EntitySet="Country" />
                    <End Role="CountryProducesTradeGood" EntitySet="CountryProducesTradeGood" />
                </AssociationSet>
                <AssociationSet Name="FK_CountryProducesTradeGood_TradeGood" Association="EnvironmentModel.Store.FK_CountryProducesTradeGood_TradeGood">
                    <End Role="TradeGood" EntitySet="TradeGood" />
                    <End Role="CountryProducesTradeGood" EntitySet="CountryProducesTradeGood" />
                </AssociationSet>
                <Association Name="FK_CountryProducesTradeGood_Country">
                    <End Role="Country" Type="EnvironmentModel.Store.Country" Multiplicity="1" />
                    <End Role="CountryProducesTradeGood" Type="EnvironmentModel.Store.CountryProducesTradeGood" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Country">
                            <PropertyRef Name="CountryId" />
                        </Principal>
                        <Dependent Role="CountryProducesTradeGood">
                            <PropertyRef Name="fxCountryId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_CountryProducesTradeGood_TradeGood">
                    <End Role="TradeGood" Type="EnvironmentModel.Store.TradeGood" Multiplicity="1" />
                    <End Role="CountryProducesTradeGood" Type="EnvironmentModel.Store.CountryProducesTradeGood" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="TradeGood">
                            <PropertyRef Name="TradeGoodId" />
                        </Principal>
                        <Dependent Role="CountryProducesTradeGood">
                            <PropertyRef Name="fxTradeGoodId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Countries" EntityType="EnvironmentModel.Country" />
                    <EntitySet Name="TradeGoods" EntityType="EnvironmentModel.TradeGood" />
                </EntityContainer>
                <EntityType Name="Country">
                    <Key>
                        <PropertyRef Name="CountryId" />
                    </Key>
                    <Property Name="CountryId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="SquareMiles" Type="Decimal" Nullable="false" />
                    <Property Name="Capital" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <NavigationProperty Name="ProducesTradeGoods" Relationship="EnvironmentModel.Country_to_TradeGood" FromRole="Country" ToRole="TradeGood" />
                </EntityType>
                <EntityType Name="TradeGood">
                    <Key>
                        <PropertyRef Name="TradeGoodId" />
                    </Key>
                    <Property Name="TradeGoodId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <NavigationProperty Name="ProducedBy" Relationship="EnvironmentModel.Country_to_TradeGood" FromRole="TradeGood" ToRole="Country" />
                </EntityType>
                <AssociationSet Name="Country_to_TradeGood" Association="EnvironmentModel.Country_to_TradeGood">
                    <End Role="Country" EntitySet="Countries" />
                    <End Role="TradeGood" EntitySet="TradeGood" />
                </AssociationSet>
                <Association Name="Country_to_TradeGood">
                    <End Role="Country" Type="EnvironmentModel.Country" Multiplicity="*" />
                    <End Role="TradeGood" Type="EnvironmentModel.TradeGood" Multiplicity="*" />
                </Association>
            </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Countries">
                        <EntityTypeMapping TypeName="EnvironmentModel.Country">
                            <MappingFragment StoreEntitySet="Country">
                                <ScalarProperty Name="CountryId" ColumnName="CountryId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="SquareMiles" ColumnName="SquareMiles" />
                                <ScalarProperty Name="Capital" ColumnName="Capital" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="TradeGoods">
                        <EntityTypeMapping TypeName="EnvironmentModel.TradeGood">
                            <MappingFragment StoreEntitySet="TradeGood">
                                <ScalarProperty Name="TradeGoodId" ColumnName="TradeGoodId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <AssociationSetMapping Name="Country_to_TradeGood" TypeName="EnvironmentModel.Country_to_TradeGood" StoreEntitySet="Country_to_TradeGood">
                        <EndProperty Name="Country">
                            <ScalarProperty Name="fxCountryId" ColumnName="fxCountryId" />
                        </EndProperty>
                        <EndProperty Name="TradeGood">
                            <ScalarProperty Name="fxTradeGoodId" ColumnName="fxTradeGoodId" />
                        </EndProperty>
                    </AssociationSetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
        </edmx:Runtime>
</edmx:Edmx>

Many-to-Many With Extra Columns


CREATE TABLE [map].[Country] (
    [CountryId]            INT            NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,[SquareMiles]        DECIMAL        NOT NULL
    ,[Capital]            VARCHAR(50)    NOT NULL
    ,CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [commerce].[TradeGood] (
    [TradeGoodId]        INT            NOT NULL    IDENTITY (1, 1)
    ,[Name]                VARCHAR(50)    NOT NULL
    ,CONSTRAINT PK_TradeGood PRIMARY KEY CLUSTERED (TradeGoodId) WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE [commerce].[Production] (
    [ProductionId]            INT        NOT NULL    IDENTITY (1, 1)
    ,[fxCountryId]            INT        NOT NULL
    ,[fxTradeGoodId]        INT        NOT NULL
    ,[QuantityPerQuarter]    INT        NOT NULL
    ,[AvgPrice]                DECIMAL    NOT NULL
    ,CONSTRAINT PK_Production PRIMARY KEY CLUSTERED (ProductionId) WITH (IGNORE_DUP_KEY = OFF)
    ,CONSTRAINT FK_Production_Country FOREIGN KEY ([fxCountryId]) REFERENCES [map].[Country] ([CountryId]) ON DELETE NO ACTION ON UPDATE NO ACTION
    ,CONSTRAINT FK_Production_TradeGood FOREIGN KEY ([fxTradeGoodId]) REFERENCES [commerce].[TradeGood] ([TradeGoodId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);


<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
    <edmx:Runtime>
        <edmx:StorageModels>
            <Schema Namespace="EnvironmentModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
                <EntityContainer Name="EnvironmentModelStoreContainer">
                    <EntitySet Name="Country" EntityType="EnvironmentModel.Store.Country" store:Type="Tables" Schema="map" />
                    <EntitySet Name="TradeGood" EntityType="EnvironmentModel.Store.TradeGood" store:Type="Tables" Schema="commerce" />
                    <EntitySet Name="Production" EntityType="EnvironmentModel.Store.Production" store:Type="Tables" Schema="commerce" />
                </EntityContainer>
                <EntityType Name="Country">
                    <Key>
                        <PropertyRef Name="CountryId" />
                    </Key>
                    <Property Name="CountryId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="Name" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="SquareMiles" Type="decimal" Nullable="false" />
                    <Property Name="Capital" Type="varchar" Nullable="false" MaxLength="50" />
                </EntityType>
                <EntityType Name="TradeGood">
                    <Key>
                        <PropertyRef Name="TradeGoodId" />
                    </Key>
                    <Property Name="TradeGoodId" Type="int" Nullable="false" />
                    <Property Name="Name" Type="varchar" Nullable="true" MaxLength="50" />
                </EntityType>
                <EntityType Name="Production">
                    <Key>
                        <PropertyRef Name="ProductionId" />
                    </Key>
                    <Property Name="Production" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="fxCountryId" Type="int" Nullable="false" />
                    <Property Name="fxTradeGoodId" Type="int" Nullable="false" />
                    <Property Name="QuantityPerQuarter" Type="int" Nullable="false" />
                    <Property Name="AvgPrice" Type="decimal" Nullable="false" />
                </EntityType>
                <AssociationSet Name="FK_Production_Country" Association="EnvironmentModel.Store.FK_Production_Country">
                    <End Role="Country" EntitySet="Country" />
                    <End Role="Production" EntitySet="Production" />
                </AssociationSet>
                <AssociationSet Name="FK_Production_TradeGood" Association="EnvironmentModel.Store.FK_Production_TradeGood">
                    <End Role="TradeGood" EntitySet="TradeGood" />
                    <End Role="Production" EntitySet="Production" />
                </AssociationSet>
                <Association Name="FK_Production_Country">
                    <End Role="Country" Type="EnvironmentModel.Store.Country" Multiplicity="1" />
                    <End Role="Production" Type="EnvironmentModel.Store.Production" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Country">
                            <PropertyRef Name="CountryId" />
                        </Principal>
                        <Dependent Role="Production">
                            <PropertyRef Name="fxCountryId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Production_TradeGood">
                    <End Role="TradeGood" Type="EnvironmentModel.Store.TradeGood" Multiplicity="1" />
                    <End Role="Production" Type="EnvironmentModel.Store.Production" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="TradeGood">
                            <PropertyRef Name="TradeGoodId" />
                        </Principal>
                        <Dependent Role="Production">
                            <PropertyRef Name="fxTradeGoodId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <edmx:ConceptualModels>
            <Schema Namespace="EnvironmentModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
                <EntityContainer Name="EnvironmentEntities" annotation:LazyLoadingEnabled="true" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
                    <EntitySet Name="Countries" EntityType="EnvironmentModel.Country" />
                    <EntitySet Name="TradeGoods" EntityType="EnvironmentModel.TradeGood" />
                    <EntitySet Name="Productions" EntityType="EnvironmentModel.Production" />
                </EntityContainer>
                <EntityType Name="Country">
                    <Key>
                        <PropertyRef Name="CountryId" />
                    </Key>
                    <Property Name="CountryId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="SquareMiles" Type="Decimal" Nullable="false" />
                    <Property Name="Capital" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <NavigationProperty Name="Productions" Relationship="EnvironmentModel.FK_Production_Country" FromRole="Country" ToRole="Production" />
                </EntityType>
                <EntityType Name="TradeGood">
                    <Key>
                        <PropertyRef Name="TradeGoodId" />
                    </Key>
                    <Property Name="TradeGoodId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <NavigationProperty Name="Productions" Relationship="EnvironmentModel.FK_Production_TradeGood" FromRole="TradeGood" ToRole="Production" />
                </EntityType>
                <EntityType Name="Production">
                    <Key>
                        <PropertyRef Name="ProductionId" />
                    </Key>
                    <Property Name="ProductionId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
                    <Property Name="fxCountryId" Type="Int32" Nullable="false" />
                    <Property Name="fxTradeGoodId" Type="Int32" Nullable="false" />
                    <Property Name="QuantityPerQuarter" Type="Int32" Nullable="false" />
                    <Property Name="AvgPrice" Type="Decimal" Nullable="false" />
                    <NavigationProperty Name="Countries" Relationship="EnvironmentModel.FK_Production_Country" FromRole="Production" ToRole="Country" />
                    <NavigationProperty Name="TradeGoods" Relationship="EnvironmentModel.FK_Production_TradeGood" FromRole="Production" ToRole="TradeGood" />
                </EntityType>
                <AssociationSet Name="FK_Production_Country" Association="EnvironmentModel.FK_Production_Country">
                    <End Role="Country" EntitySet="Countries" />
                    <End Role="Production" EntitySet="Productions" />
                </AssociationSet>
                <AssociationSet Name="FK_Production_TradeGood" Association="EnvironmentModel.FK_Production_TradeGood">
                    <End Role="TradeGood" EntitySet="TradeGoods" />
                    <End Role="Production" EntitySet="Productions" />
                </AssociationSet>
                <Association Name="FK_Production_Country">
                    <End Role="Country" Type="EnvironmentModel.Country" Multiplicity="1" />
                    <End Role="Production" Type="EnvironmentModel.Production" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="Country">
                            <PropertyRef Name="CountryId" />
                        </Principal>
                        <Dependent Role="Production">
                            <PropertyRef Name="fxCountryId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                <Association Name="FK_Production_TradeGood">
                    <End Role="TradeGood" Type="EnvironmentModel.TradeGood" Multiplicity="1" />
                    <End Role="Production" Type="EnvironmentModel.Production" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="TradeGood">
                            <PropertyRef Name="TradeGoodId" />
                        </Principal>
                        <Dependent Role="Production">
                            <PropertyRef Name="fxTradeGoodId" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
                </Schema>
        </edmx:ConceptualModels>
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
                <EntityContainerMapping StorageEntityContainer="EnvironmentModelStoreContainer" CdmEntityContainer="EnvironmentEntities">
                    <EntitySetMapping Name="Countries">
                        <EntityTypeMapping TypeName="EnvironmentModel.Country">
                            <MappingFragment StoreEntitySet="Country">
                                <ScalarProperty Name="CountryId" ColumnName="CountryId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                                <ScalarProperty Name="SquareMiles" ColumnName="SquareMiles" />
                                <ScalarProperty Name="Capital" ColumnName="Capital" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="TradeGoods">
                        <EntityTypeMapping TypeName="EnvironmentModel.TradeGood">
                            <MappingFragment StoreEntitySet="TradeGood">
                                <ScalarProperty Name="TradeGoodId" ColumnName="TradeGoodId" />
                                <ScalarProperty Name="Name" ColumnName="Name" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="Productions">
                        <EntityTypeMapping TypeName="EnvironmentModel.Production">
                            <MappingFragment StoreEntitySet="Production">
                                <ScalarProperty Name="ProductionId" ColumnName="ProductionId" />
                                <ScalarProperty Name="fxCountryId" ColumnName="fxCountryId" />
                                <ScalarProperty Name="fxTradeGoodId" ColumnName="fxTradeGoodId" />
                                <ScalarProperty Name="QuantityPerQuarter" ColumnName="QuantityPerQuarter" />
                                <ScalarProperty Name="AvgPrice" ColumnName="AvgPrice" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
        </edmx:Runtime>
</edmx:Edmx>
Nested Types

Example without nesting:

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Person Ruler { get; set; }
}

//Person is a value-type
public class Person : ValueObject<Person>
{
    public string Name { get; private set; }
    
    public Person(string name)
    {
        Name = name;
    }
}
Country.Ruler will be persisted as a Complex-Type in the database.

Example with nesting:

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Person Ruler { get; set; }

    //Person is a value-type
    public class Person : ValueObject<Person>
    {
        public string Name { get; private set; }
        
        public Person(string name)
        {
            Name = name;
        }
    }
}
EF 5 would ignore Country.Ruler entirely.
EF 6 persists Country.Ruler as a Complex-Type.

Duplicates

EF will not add duplicate objects to the DbContext.

As of EF 5, "Duplicate" was based on Object.Equals and Object.GetHashCode, so if you'd overridden that logic, it could have affect persistence.

As of EF 6, "Duplicate" is only based on internal EF logic.
Logging


//everything written to console will be logged
dbContext.Database.Log = Console.WriteLine;
Testing

Starting in EF 6, there is better support for Mocking DbSets. In EF 5, Mock was not allowed to override the DbSet constructor.

Note: any DbSet property in your DbContext that you want to Mock must be marked Virtual.

Recommendation: any time you find yourself needing to Mock EF objects in a test, consider if the method you are tested can be refactored so you can test what you need to, without Mocking any EF objects.

Mocking Linq Queries


public List<Country> GetSomeCountries()
{
    return dbContext.Countries.Where(someFilter).ToList();
}
...
public void Test_GetSomeCountries()
{
    //arrange
    IQueryable<Country> countries = (new List<Country>() { new Country("Argentina") }).AsQueryable();
    
    var mockContext = new Mock<GeographyContext>();
    Mock<DbSet<Country>> queryableMockSet = SetupQueryableMockSet(countries);
    mockContext.Setup(context => context.Countries).Returns(queryableMockSet.Object);
    
    //act
    //assert
}
private static Mock<DbSet<Country>> SetupQueryableMockSet(IQueryable<Country> countries)
{
    var mockSet = new Mock<DbSet<Country>>();
    mockSet.As<IQueryable<Country>>().Setup(x => x.Provider).Returns(countries.Provider);
    mockSet.As<IQueryable<Country>>().Setup(x => x.Expression).Returns(countries.Expression);
    mockSet.As<IQueryable<Country>>().Setup(x => x.ElementType).Returns(countries.ElementType);
    mockSet.As<IQueryable<Country>>().Setup(x => x.GetEnumerator()).Returns(countries.GetEnumerator());
    return mockSet;
}
Entity Framework Errors

Cannot Find Provider

Error: "No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'"

Project-A uses Entity Framework directly. It has the full Entity Framework package installed.
Project-B references Project-A and uses it to access the data store.

Project-B needs a reference to Project-A's EntityFramework.SqlServer.dll.
I don't know why, but that fixes the error.

Datetime2

Error: "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."

SQL DateTime minimum value is January 1, 1753.
C# DateTime minimum value is January 1, 0001.

Either change your database column from "datetime" to "datetime2", which has a larger range.
Or ensure that you don't send out-of-range values to the database.

A common cause of this error is that a DateTime property should be nullable, but isn't, so it's defaulting to DateTime.MinValue.

Note that if you have a non-nullable DateTime property, and the database column has a DEFAULT constraint, you still have to set the value in your code because a non-nullable DateTime will default to DateTime.MinValue and will not trigger the database DEFAULT constraint.