Entity Framework/Core and LINQ to Entities (2) Modeling Database: Object-Relational Mapping

[LINQ via C#] - [Entity Framework]

.NET and SQL database and  have 2 different data type systems. For example, .NET has System.Int64 and System.String, while SQL database has bigint and nvarchar; .NET has sequences and objects, while SQL database has tables and rows;, etc. Object-relational mapping is a popular technology to map and convert between application data objects and database relational data. In LINQ to Entities, the queries are based on Object-relational mapping.

EF provides 3 options to build the mapping between C#/.NET and SQL database:

  • Model first: The entity data models (a .edmx diagram consists of entities, entity properties, entity associations, etc.) are first created in EF, typically by the ADO.NET Entity Data Model Designer tool in Visual Studio. Then, EF can use the models to generate database and the mapping .NET types.
    image_thumb
  • Database first: From an existing database, EF can generate the entity data models (.edmx diagram) and the mapping .NET types., typically by Entity Data Model Wizard too:
    image_thumb5
  • Code first: The mapping .NET types are coded to enabled LINQ to Entities queries and other operations. EF generates the entity data models at runtime, so there is no .edmx diagram at design time in the code base. If the database exits, the .NET types are just mapped to the existing database; if not, EF can generate the database. “Code first” is not an intuitive naming. It does not mean code is created before the database. It is just code-based modeling for existing database or new database.

Comparing to code generation from entity data models (.edmx), it is more intuitive and transparent to build code from scratch. Also, regarding EF Core does not support entity data models (.edmx) and only supports code first, this tutorial follows the code first approach.

Data types

EF/Core can map most SQL data types to .NET types:

SQL type category SQL type .NET type C# primitive
Exact numeric bit System.Boolean bool
  tinyint System.Byte byte
  smallint System.Int16 short
  int System.Int32 int
  bigint System.Int64 long
  smallmoney, money, decimal, numeric System.Decimal decimal
Approximate numeric real System.Single float
  float System.Double double
Character string char, varchar, text System.String string
  nchar, nvarchar, ntext System.String string
Binary string binary, varbinary System.Byte[] byte[]
  image System.Byte[] byte[]
  rowversion (timestamp) System.Byte[] byte[]
Date time date System.DateTime  
  time System.TimeSpan  
  smalldatetime, datetime, datetime2 System.DateTime  
  datetimeoffset System.DateTimeOffset  
Spatial type geography System.Data.Entity.Spatial.DbGeography*  
  geometry System.Data.Entity.Spatial.DbGeometry*  
Other hierarchyid No built-in mapping or support  
  xml System.String string
  uniqueidentifier System.Guid  
  sql_variant No built-in mapping or support  

Currently the spatial types marked with * are only supported by EF.

Database

A SQL database is mapped to a type derived from DbContext:

public partial class AdventureWorks : DbContext { }

DbContext is provided as:

namespace Microsoft.EntityFrameworkCore
{
    public class DbContext : IDisposable, IInfrastructure<IServiceProvider>
    {
        public DbContext(DbContextOptions options);
    </span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ChangeTracker </span><span style="color: black;">ChangeTracker { </span><span style="color: blue;">get</span><span style="color: black;">; }

    </span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">DatabaseFacade </span><span style="color: black;">Database { </span><span style="color: blue;">get</span><span style="color: black;">; }

    </span><span style="color: blue;">public virtual void </span><span style="color: black;">Dispose();

    </span><span style="color: blue;">public virtual int </span><span style="color: black;">SaveChanges();

    </span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">TEntity</span><span style="color: black;">&gt; Set&lt;</span><span style="color: #2b91af;">TEntity</span><span style="color: black;">&gt;() </span><span style="color: blue;">where </span><span style="color: #2b91af;">TEntity </span><span style="color: black;">: </span><span style="color: blue;">class</span><span style="color: black;">;

    </span><span style="color: blue;">protected internal virtual void </span><span style="color: black;">OnModelCreating(</span><span style="color: #2b91af;">ModelBuilder </span><span style="color: black;">modelBuilder);

    </span><span style="color: green;">// Other members.
</span><span style="color: black;">}

}

In EF, the members of DbContext and DbContext have slightly different signatures::

namespace System.Data.Entity
{
    public class DbContext : IDisposable, IObjectContextAdapter
    {
        public DbContext(DbConnection existingConnection, bool contextOwnsConnection);
    </span><span style="color: blue;">public </span><span style="color: #2b91af;">DbChangeTracker </span><span style="color: black;">ChangeTracker { </span><span style="color: blue;">get</span><span style="color: black;">; }

    </span><span style="color: blue;">public </span><span style="color: #2b91af;">Database </span><span style="color: black;">Database { </span><span style="color: blue;">get</span><span style="color: black;">; }

    </span><span style="color: blue;">public void </span><span style="color: black;">Dispose();

    </span><span style="color: blue;">public virtual int </span><span style="color: black;">SaveChanges();

    </span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">TEntity</span><span style="color: black;">&gt; Set&lt;</span><span style="color: #2b91af;">TEntity</span><span style="color: black;">&gt;() </span><span style="color: blue;">where </span><span style="color: #2b91af;">TEntity </span><span style="color: black;">: </span><span style="color: blue;">class</span><span style="color: black;">;

    </span><span style="color: blue;">protected virtual void </span><span style="color: black;">OnModelCreating(</span><span style="color: #2b91af;">DbModelBuilder </span><span style="color: black;">modelBuilder);

    </span><span style="color: green;">// Other members.
</span><span style="color: black;">}

}

DbContext implements IDisposable. Generally, a database instance should be constructed and disposed for each unit of work - a collection of data operations that should succeed or fail as a unit:

internal static void Dispose()
{
    using (AdventureWorks adventureWorks = new AdventureWorks())
    {
        // Unit of work.
    }
}

In EF/Core, most of the object-relational mapping can be implemented declaratively, and the rest of the mapping can be implemented imperatively by overriding DbContext.OnModelCreating, which is called by EF/Core when initializing the entity models:

public partial class AdventureWorks
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    </span><span style="color: black;">MapCompositePrimaryKey(modelBuilder);
    </span><span style="color: black;">MapManyToMany(modelBuilder);
    </span><span style="color: black;">MapDiscriminator(modelBuilder);
}

}

The above MapCompositePrimaryKey, MapManyToMany, MapDiscriminator methods are implemented soon later.

Connection resiliency and execution retry strategy

As the mapping of the database, AdventureWorks also manages the connection to the database, which can be injected from the constructor:

public partial class AdventureWorks
{
    public AdventureWorks(DbConnection connection = null)
        : base(new DbContextOptionsBuilder<AdventureWorks>().UseSqlServer(
            connection: connection ?? new SqlConnection(ConnectionStrings.AdventureWorks),
            sqlServerOptionsAction: options => options.EnableRetryOnFailure(
                maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null)).Options) { }
}

Here when database connection is not provided to the constructor, a new database connection is created with the previously defined connection string. Also, regarding the connection between application and SQL database may be interrupted (because of network, etc.), EF/Core support connection resiliency for SQL database. This is especially helpful for Azure SQL database deployed in the cloud instead of local network. In the above example, EF Core is specified to automatically retries up to 5 times with the retry interval of 30 seconds.

In EF, the database connection can be injected through constructor too:

public partial class AdventureWorks
{
    public AdventureWorks(DbConnection connection = null) : base(
        existingConnection: connection ?? new SqlConnection(ConnectionStrings.AdventureWorks),
        contextOwnsConnection: connection == null) { }
}

The connection resiliency needs to be specified as part of the EF configuration, which must be a type derived from System.Data.Entity.DbConfiguration:

public class RetryConfiguration : DbConfiguration
{
    public RetryConfiguration()
    {
        this.SetExecutionStrategy(
            providerInvariantName: SqlProviderServices.ProviderInvariantName,
            getExecutionStrategy: () => ExecutionStrategy.DisableExecutionStrategy
                ? new DefaultExecutionStrategy() : ExecutionStrategy.Create());
    }
}

public partial class ExecutionStrategy { public static bool DisableExecutionStrategy { get => (bool?)CallContext.LogicalGetData(nameof(DisableExecutionStrategy)) ?? false; set => CallContext.LogicalSetData(nameof(DisableExecutionStrategy), value); }

</span><span style="color: blue;">public static </span><span style="color: #2b91af;">IDbExecutionStrategy </span><span style="color: black;">Create() =&gt;
    </span><span style="color: blue;">new </span><span style="color: #2b91af;">SqlAzureExecutionStrategy</span><span style="color: black;">(maxRetryCount: 5, maxDelay: </span><span style="color: #2b91af;">TimeSpan</span><span style="color: black;">.FromSeconds(30));

}

At runtime, EF discovers and instantiates the above RetryConfiguration type with reflection, so that RetryConfiguration constructor is called. Here ExecutionStrategy.DisableExecutionStrategy can be used to turn on/off the above default retry logic: when ExecutionStrategy.DisableExecutionStrategy is true, a System.Data.Entity.Infrastructure.DefaultExecutionStrategy instance specifies EF do not retry; when it is false, a System.Data.Entity.SqlServer.SqlAzureExecutionStrategy instance specified EF to retry up to 5 times with the retry interval of 30 seconds.

Why not simply leave the retry enabled all the time? The reason is, EF connection resiliency does not directly work with custom transactions. So this switch is introduced to disable default retry logic for custom transactions, which is discussed in the transaction part.

Tables

There are tens of tables in the AdventureWorks database, but don’t panic, this tutorial only involves a few tables, and a few columns of these tables. In EF/Core, a table definition can be mapped to an entity type definition, where each column is mapped to a entity property. For example, the AdventureWorks database has a Production.ProductCategory table, which is defined as:

CREATE SCHEMA [Production];
GO

CREATE TYPE [dbo].[Name] FROM nvarchar(50) NULL; GO

CREATE TABLE [Production].[ProductCategory]( [ProductCategoryID] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_ProductCategory_ProductCategoryID] PRIMARY KEY CLUSTERED,

</span><span style="color: black;">[Name] [dbo]</span><span style="color: gray;">.</span><span style="color: black;">[Name] </span><span style="color: gray;">NOT NULL, </span><span style="color: green;">-- nvarchar(50).

</span><span style="color: black;">[rowguid] </span><span style="color: blue;">uniqueidentifier ROWGUIDCOL </span><span style="color: gray;">NOT NULL </span><span style="color: green;">-- Ignored in mapping.
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[DF_ProductCategory_rowguid] </span><span style="color: blue;">DEFAULT </span><span style="color: gray;">(</span><span style="color: magenta;">NEWID</span><span style="color: gray;">()),

</span><span style="color: black;">[ModifiedDate] </span><span style="color: blue;">datetime </span><span style="color: gray;">NOT NULL </span><span style="color: green;">-- Ignored in mapping.
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[DF_ProductCategory_ModifiedDate] </span><span style="color: blue;">DEFAULT </span><span style="color: gray;">(</span><span style="color: magenta;">GETDATE</span><span style="color: gray;">()));

GO

This table definition can be mapped to a ProductCategory entity definition:

public partial class AdventureWorks
{
    public const string Production = nameof(Production); // Production schema.
}

[Table(nameof(ProductCategory), Schema = AdventureWorks.Production)] public partial class ProductCategory { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ProductCategoryID { get; set; }

[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]</span><span style="color: black;">
</span><span style="color: blue;">public string </span><span style="color: black;">Name { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: green;">// Other columns are ignored.

}

The [Table] attribute specifies the table name and schema. [Table] can be omitted when the table name is the same as the entity name, and the table is under the default dbo schema. In the table-entity mapping:

  • The ProductCategoryID column of int type is mapped to a System.Int32 property with the same name. The [Key] attribute indicates it is a primary key. EF/Core requires a table to have primary key to be mapped. [DatabaseGenerated] indicates it is an identity column, with value generated by database.
  • The Name column is of dbo.Name type. which is actually nvarchar(50), so it is mapped to Name property of type System.String. The [MaxLength] attribute indicates the max length of the string value is 50. [Required] indicates it should not be null or empty string or whitespace string.
  • The other columns rowguid and ModifiedDate are not mapped. They are not used in this tutorial to keep the code examples simple.

At runtime, each row of Production.ProductCategory table is mapped to a ProductCategory instance.

EF by default does not directly instantiate ProductCategory. It dynamically defines another proxy type derived from ProductCategory, with a name like System.Data.Entity.DynamicProxies.Product_F84B0F952ED22479EF48782695177D770E63BC4D8771C9DF78343B4D95926AE8. This proxy type is where EF injects more logic like lazy loading, so that at design time the mapping entity type can be clean and declarative.

The rows of the entire table can be mapped to objects in an IQueryable<T> data source, exposed as a property of the database type. DbSet<T> implements IQueryable<T>, and is provided to represent a table data source:

public partial class AdventureWorks
{
    public DbSet<ProductCategory> ProductCategories { get; set; }
}

Relationships

In SQL database, tables can have foreign key relationships, including one-to-one, one-to-many, and many-to-many relationships.

One-to-one

The following Person.Person table and HumanResources.Employee table has a one-to-one relationship:

image

HumanResources.Employee table’s BusinessEntityID column is a foreign key that refers to Person.Person table’s primary key:

CREATE TABLE [Person].[Person](
    [BusinessEntityID] int NOT NULL
        CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED,
</span><span style="color: black;">[FirstName] [dbo]</span><span style="color: gray;">.</span><span style="color: black;">[Name] </span><span style="color: gray;">NOT NULL,

</span><span style="color: black;">[LastName] [dbo]</span><span style="color: gray;">.</span><span style="color: black;">[Name] </span><span style="color: gray;">NOT NULL

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">);

GO

CREATE TABLE [HumanResources].[Employee]( [BusinessEntityID] int NOT NULL CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED CONSTRAINT [FK_Employee_Person_BusinessEntityID] FOREIGN KEY REFERENCES [Person].[Person] ([BusinessEntityID]),

</span><span style="color: black;">[JobTitle] </span><span style="color: blue;">nvarchar</span><span style="color: gray;">(</span><span style="color: black;">50</span><span style="color: gray;">) NOT NULL,

</span><span style="color: black;">[HireDate] </span><span style="color: blue;">date </span><span style="color: gray;">NOT NULL

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">);

GO

So each row in HumanResources.Employee table refers to one row in Person.Person table (an employee must be a person). On the other hand, each row in Person.Person table can be referred by 0 or 1 row in HumanResources.Employee table (a person can be an employee, or not). This relationship can be represented by navigation property of entity type:

public partial class AdventureWorks
{
    public const string Person = nameof(Person);
</span><span style="color: blue;">public const string </span><span style="color: black;">HumanResources = </span><span style="color: blue;">nameof</span><span style="color: black;">(HumanResources);

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">Person</span><span style="color: black;">&gt; People { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">Employee</span><span style="color: black;">&gt; Employees { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

}

[Table(nameof(Person), Schema = AdventureWorks.Person)] public partial class Person { [Key] public int BusinessEntityID { get; set; }

[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]
[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
</span><span style="color: blue;">public string </span><span style="color: black;">FirstName { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]
[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
</span><span style="color: blue;">public string </span><span style="color: black;">LastName { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">Employee </span><span style="color: black;">Employee { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

}

[Table(nameof(Employee), Schema = AdventureWorks.HumanResources)] public partial class Employee { [Key] [ForeignKey(nameof(Person))] public int BusinessEntityID { get; set; }

[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]
[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
</span><span style="color: blue;">public string </span><span style="color: black;">JobTitle { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DateTime </span><span style="color: black;">HireDate { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">Person </span><span style="color: black;">Person { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

}

The [ForeignKey] attribute indicates Employee entity’s BusinessEntityID property is the foreign key for the relationship represented by navigation property. Here Person is called the primary entity, and Employee is called the dependent entity. Their navigation properties are called reference navigation properties, because each navigation property can refer to a single entity.

For EF, the navigation property needs to be virtual to enable proxy entity to implement lazy loading. This will be discussed in the lazy loading part. EF Core does not support lazy loading, so the virtual keyword does not make difference for EF Core.

One-to-many

The Production.ProductCategory and Production.ProductSubcategory tables have a one-to-many relationship, so are Production.ProductSubcategory and Production.Product:

image

Each row in Production.ProductCategory table can refer to many rows in Production.ProductSubcategory table (category can have many subcategories), and each row in Production.ProductSubcategory table can refer to many rows in Production.Product table (ubcategory can have many products):

CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] int IDENTITY(1,1) NOT NULL
        CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID] PRIMARY KEY CLUSTERED,
</span><span style="color: black;">[Name] [dbo]</span><span style="color: gray;">.</span><span style="color: black;">[Name] </span><span style="color: gray;">NOT NULL, </span><span style="color: green;">-- nvarchar(50).

</span><span style="color: black;">[ProductCategoryID] </span><span style="color: blue;">int </span><span style="color: gray;">NOT NULL
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[FK_ProductSubcategory_ProductCategory_ProductCategoryID] </span><span style="color: blue;">FOREIGN KEY
    REFERENCES </span><span style="color: black;">[Production]</span><span style="color: gray;">.</span><span style="color: black;">[ProductCategory] </span><span style="color: gray;">(</span><span style="color: black;">[ProductCategoryID]</span><span style="color: gray;">),

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">)

GO

CREATE TABLE [Production].[Product]( [ProductID] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED,

</span><span style="color: black;">[Name] [dbo]</span><span style="color: gray;">.</span><span style="color: black;">[Name] </span><span style="color: gray;">NOT NULL, </span><span style="color: green;">-- nvarchar(50).

</span><span style="color: black;">[ListPrice] </span><span style="color: blue;">money </span><span style="color: gray;">NOT NULL,

</span><span style="color: black;">[ProductSubcategoryID] </span><span style="color: blue;">int </span><span style="color: gray;">NULL
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[FK_Product_ProductSubcategory_ProductSubcategoryID] </span><span style="color: blue;">FOREIGN KEY
    REFERENCES </span><span style="color: black;">[Production]</span><span style="color: gray;">.</span><span style="color: black;">[ProductSubcategory] </span><span style="color: gray;">(</span><span style="color: black;">[ProductSubcategoryID]</span><span style="color: gray;">)

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">)

GO

These one-to-many relationships can be represented by navigation property of type ICollection<T>:

public partial class ProductCategory
{
    public virtual ICollection<ProductSubcategory> ProductSubcategories { get; set; } // Collection navigation property.
}

[Table(nameof(ProductSubcategory), Schema = AdventureWorks.Production)] public partial class ProductSubcategory { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ProductSubcategoryID { get; set; }

[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]
</span><span style="color: blue;">public string </span><span style="color: black;">Name { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public int </span><span style="color: black;">ProductCategoryID { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ProductCategory </span><span style="color: black;">ProductCategory { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ICollection</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">Product</span><span style="color: black;">&gt; Products { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Collection navigation property.

}

[Table(nameof(Product), Schema = AdventureWorks.Production)] public partial class Product { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ProductID { get; set; }

[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
[</span><span style="color: #2b91af;">Required</span><span style="color: black;">]
</span><span style="color: blue;">public string </span><span style="color: black;">Name { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public decimal </span><span style="color: black;">ListPrice { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public int</span><span style="color: black;">? ProductSubcategoryID { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ProductSubcategory </span><span style="color: black;">ProductSubcategory { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

}

Notice Production.Product table’s ProductSubcategoryID column is nullable, so it is mapped to a System.Nullable<int> property. Here [ForeignKey] attribute is omitted, because the dependent entities’ foreign keys are different from their primary keys, and each foreign key have the same name as its primary key, so they can be automatically discovered by EF/Core.

Many-to-many

Production.Product and Production.ProductPhoto tables has many-to-many relationship.

image

This is implemented by 2 one-to-many relationships with another Production.ProductProductPhoto junction table:

CREATE TABLE [Production].[ProductPhoto](
    [ProductPhotoID] int IDENTITY(1,1) NOT NULL
        CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY CLUSTERED,
</span><span style="color: black;">[LargePhotoFileName] </span><span style="color: blue;">nvarchar</span><span style="color: gray;">(</span><span style="color: black;">50</span><span style="color: gray;">) NULL,

</span><span style="color: black;">[ModifiedDate] </span><span style="color: blue;">datetime </span><span style="color: gray;">NOT NULL 
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[DF_ProductPhoto_ModifiedDate] </span><span style="color: blue;">DEFAULT </span><span style="color: gray;">(</span><span style="color: magenta;">GETDATE</span><span style="color: gray;">())

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">)

GO

CREATE TABLE [Production].[ProductProductPhoto]( [ProductID] int NOT NULL CONSTRAINT [FK_ProductProductPhoto_Product_ProductID] FOREIGN KEY REFERENCES [Production].[Product] ([ProductID]),

</span><span style="color: black;">[ProductPhotoID] </span><span style="color: blue;">int </span><span style="color: gray;">NOT NULL
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[FK_ProductProductPhoto_ProductPhoto_ProductPhotoID] </span><span style="color: blue;">FOREIGN KEY
    REFERENCES </span><span style="color: black;">[Production]</span><span style="color: gray;">.</span><span style="color: black;">[ProductPhoto] </span><span style="color: gray;">(</span><span style="color: black;">[ProductPhotoID]</span><span style="color: gray;">),

</span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[PK_ProductProductPhoto_ProductID_ProductPhotoID] </span><span style="color: blue;">PRIMARY KEY NONCLUSTERED </span><span style="color: gray;">(</span><span style="color: black;">[ProductID]</span><span style="color: gray;">, </span><span style="color: black;">[ProductPhotoID]</span><span style="color: gray;">)

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">)

GO

So the many-to-many relationship can be mapped to 2 one-to-many relationships with the junction:

public partial class Product
{
    public virtual ICollection<ProductProductPhoto> ProductProductPhotos { get; set; } // Collection navigation property.
}

[Table(nameof(ProductPhoto), Schema = AdventureWorks.Production)] public partial class ProductPhoto { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ProductPhotoID { get; set; }

[</span><span style="color: #2b91af;">MaxLength</span><span style="color: black;">(50)]
</span><span style="color: blue;">public string </span><span style="color: black;">LargePhotoFileName { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

[</span><span style="color: #2b91af;">ConcurrencyCheck</span><span style="color: black;">]
</span><span style="color: blue;">public </span><span style="color: #2b91af;">DateTime </span><span style="color: black;">ModifiedDate { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ICollection</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">ProductProductPhoto</span><span style="color: black;">&gt; ProductProductPhotos { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Collection navigation property.

}

[Table(nameof(ProductProductPhoto), Schema = AdventureWorks.Production)] public partial class ProductProductPhoto { [Key] [Column(Order = 0)] public int ProductID { get; set; }

[</span><span style="color: #2b91af;">Key</span><span style="color: black;">]
[</span><span style="color: #2b91af;">Column</span><span style="color: black;">(Order = 1)]
</span><span style="color: blue;">public int </span><span style="color: black;">ProductPhotoID { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">Product </span><span style="color: black;">Product { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

</span><span style="color: blue;">public virtual </span><span style="color: #2b91af;">ProductPhoto </span><span style="color: black;">ProductPhoto { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; } </span><span style="color: green;">// Reference navigation property.

}

ProductPhoto.ModifiedDate has a [ConcurrencyCheck] attribute for concurrency conflict check, which is discussed in the concurrency part. Production.ProductProductPhoto table has a composite primary key. As a junction table, each row in the table has a unique combination of ProductID and ProductPhotoID. EF Core requires additional information for composite primary key, which can be provided as anonymous type in OnModelCreating:

public partial class AdventureWorks
{
    private static void MapCompositePrimaryKey(ModelBuilder modelBuilder) // Called by OnModelCreating.
    {
        modelBuilder.Entity<ProductProductPhoto>()
            .HasKey(productProductPhoto => new
            {
                ProductID = productProductPhoto.ProductID,
                ProductPhotoID = productProductPhoto.ProductPhotoID
            });
    }
}

EF does not require above anonymous type to represent composite primary key, but it requires the ordering, which can be simply provided by the [Column] attribute.

EF Core also requires additional information for many-to-many relationship represented by 2 one-to-many relationships, which can be provided in OnModelCreating as well:

public partial class AdventureWorks
{
    private static void MapManyToMany(ModelBuilder modelBuilder) // Called by OnModelCreating.
    {
        modelBuilder.Entity<ProductProductPhoto>()
            .HasOne(productProductPhoto => productProductPhoto.Product)
            .WithMany(product => product.ProductProductPhotos)
            .HasForeignKey(productProductPhoto => productProductPhoto.ProductID);
    modelBuilder.Entity&lt;ProductProductPhoto&gt;()
        .HasOne(productProductPhoto =&gt; productProductPhoto.ProductPhoto)
        .WithMany(photo =&gt; photo.ProductProductPhotos)
        .HasForeignKey(productProductPhoto =&gt; productProductPhoto.ProductPhotoID);
</span><span style="color: black;">}

}

The above code in MapCompositePrimaryKey and MapManyToMany methods are not needed by EF.

EF also provides another option to directly map the many-to-many relationship with API calls. With the this approach, the above ProductProductPhoto entity and one-to-many navigation properties are not needed. Just define 2 collection navigation properties, and specified the mapping in OnModelCreating:

public partial class Product
{
    public virtual ICollection<ProductPhoto> ProductPhotos { get; set; }
}

public partial class ProductPhoto { public virtual ICollection<Product> Products { get; set; } }

public partial class AdventureWorks { protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder);

    modelBuilder
        .Entity&lt;</span><span style="color: #2b91af;">Product</span><span style="color: black;">&gt;()
        .HasMany(product =&gt; product.ProductPhotos)
        .WithMany(photo =&gt; photo.Products)
        .Map(mapping =&gt; mapping
            .ToTable(</span><span style="color: blue;">nameof</span><span style="color: black;">(</span><span style="color: #2b91af;">ProductProductPhoto</span><span style="color: black;">), Production)
            .MapLeftKey(</span><span style="color: blue;">nameof</span><span style="color: black;">(</span><span style="color: #2b91af;">Product</span><span style="color: black;">.ProductID))
            .MapRightKey(</span><span style="color: blue;">nameof</span><span style="color: black;">(</span><span style="color: #2b91af;">ProductPhoto</span><span style="color: black;">.ProductPhotoID)));
}

}

Finally, the rows of each above table can be expose as an IQueryable<T> data source:

public partial class AdventureWorks
{
    public DbSet<Person> People { get; set; }
</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">Employee</span><span style="color: black;">&gt; Employees { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">ProductSubcategory</span><span style="color: black;">&gt; ProductSubcategories { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">Product</span><span style="color: black;">&gt; Products { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">ProductPhoto</span><span style="color: black;">&gt; ProductPhotos { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

}

Inheritance

EF/Core also supports inheritance for entity types.

EF supports 3 types of inheritance for the mapping:

EF Core supports table per hierarchy (TPH) inheritance, which is also the default strategy of EF. With TPH, rows in 1 table is mapped to many entities in the inheritance hierarchy, so a discriminator column is needed to identify each specific row’s mapping entity. Take the following Production.TransactionHistory table as example:

CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] int IDENTITY(100000,1) NOT NULL
        CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY CLUSTERED,
</span><span style="color: black;">[ProductID] </span><span style="color: blue;">int </span><span style="color: gray;">NOT NULL
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[FK_TransactionHistory_Product_ProductID] </span><span style="color: blue;">FOREIGN KEY
    REFERENCES </span><span style="color: black;">[Production]</span><span style="color: gray;">.</span><span style="color: black;">[Product] </span><span style="color: gray;">(</span><span style="color: black;">[ProductID]</span><span style="color: gray;">),

</span><span style="color: black;">[TransactionDate] </span><span style="color: blue;">datetime </span><span style="color: gray;">NOT NULL,

</span><span style="color: black;">[TransactionType] </span><span style="color: blue;">nchar</span><span style="color: gray;">(</span><span style="color: black;">1</span><span style="color: gray;">) NOT NULL
    </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">[CK_Product_Style] 
    </span><span style="color: blue;">CHECK </span><span style="color: gray;">(</span><span style="color: magenta;">UPPER</span><span style="color: gray;">(</span><span style="color: black;">[TransactionType]</span><span style="color: gray;">) = </span><span style="color: red;">N'P' </span><span style="color: gray;">OR </span><span style="color: magenta;">UPPER</span><span style="color: gray;">(</span><span style="color: black;">[TransactionType]</span><span style="color: gray;">) = </span><span style="color: red;">N'S' </span><span style="color: gray;">OR </span><span style="color: magenta;">UPPER</span><span style="color: gray;">(</span><span style="color: black;">[TransactionType]</span><span style="color: gray;">) = </span><span style="color: red;">N'W'</span><span style="color: gray;">),

</span><span style="color: black;">[Quantity] </span><span style="color: blue;">int </span><span style="color: gray;">NOT NULL,

</span><span style="color: black;">[ActualCost] </span><span style="color: blue;">money </span><span style="color: gray;">NOT NULL

</span><span style="color: green;">/* Other columns. */</span><span style="color: gray;">);

GO

Its TransactionType column allows value “P”, “S”, or “W” to indicate each row representing a purchase transaction, sales transaction, or work transaction. So the mapping hierarchy can be:

[Table(nameof(TransactionHistory), Schema = AdventureWorks.Production)]
public abstract class TransactionHistory
{
    [Key]
    public int TransactionID { get; set; }
</span><span style="color: blue;">public int </span><span style="color: black;">ProductID { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DateTime </span><span style="color: black;">TransactionDate { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public int </span><span style="color: black;">Quantity { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public decimal </span><span style="color: black;">ActualCost { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

}

public class PurchaseTransactionHistory : TransactionHistory { }

public class SalesTransactionHistory : TransactionHistory { }

public class WorkTransactionHistory : TransactionHistory { }

Then the discriminator must be specified via OnModelCreating. The EF and EF Core APIs are different:

public enum TransactionType { P, S, W }

public partial class AdventureWorks { private static void MapDiscriminator(ModelBuilder modelBuilder) // Called by OnModelCreating. { #if EF modelBuilder .Entity<TransactionHistory>() .Map<PurchaseTransactionHistory>(mapping => mapping.Requires(nameof(TransactionType)) .HasValue(nameof(TransactionType.P))) .Map<SalesTransactionHistory>(mapping => mapping.Requires(nameof(TransactionType)) .HasValue(nameof(TransactionType.S))) .Map<WorkTransactionHistory>(mapping => mapping.Requires(nameof(TransactionType)) .HasValue(nameof(TransactionType.W))); #else modelBuilder.Entity<TransactionHistory>() .HasDiscriminator<string>(nameof(TransactionType)) .HasValue<PurchaseTransactionHistory>(nameof(TransactionType.P)) .HasValue<SalesTransactionHistory>(nameof(TransactionType.S)) .HasValue<WorkTransactionHistory>(nameof(TransactionType.W)); #endif } }

Now these entities can all be exposed as data sources:

public partial class AdventureWorks
{
    public DbSet<TransactionHistory> Transactions { get; set; }
</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">PurchaseTransactionHistory</span><span style="color: black;">&gt; PurchaseTransactions { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">SalesTransactionHistory</span><span style="color: black;">&gt; SalesTransactions { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public </span><span style="color: #2b91af;">DbSet</span><span style="color: black;">&lt;</span><span style="color: #2b91af;">WorkTransactionHistory</span><span style="color: black;">&gt; WorkTransactions { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

}

Views

A view can also be mapped as if it is a table, if the view has one or more columns which can be viewed as primary key. Take the Production.vEmployee view as example:

CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[BusinessEntityID],
    p.[FirstName],
    p.[LastName],
    e.[JobTitle]  
    -- Other columns.
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = e.[BusinessEntityID]
    /* Other tables. */;
GO

The BusinessEntityID is unique and can be viewed as primary key. So it can be mapped to the following entity:

[Table(nameof(vEmployee), Schema = AdventureWorks.HumanResources)]
public class vEmployee
{
    [Key]
    public int BusinessEntityID { get; set; }
</span><span style="color: blue;">public string </span><span style="color: black;">FirstName { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public string </span><span style="color: black;">LastName { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }

</span><span style="color: blue;">public string </span><span style="color: black;">JobTitle { </span><span style="color: blue;">get</span><span style="color: black;">; </span><span style="color: blue;">set</span><span style="color: black;">; }</span><span style="color: green;">

}

And then expose as data source:

public partial class AdventureWorks
{
    public DbSet<vEmployee> vEmployees { get; set; }
}

Stored procedures and functions

EF can also mapping stored procedures and functions in SQL database, including:

  • Stored procedures, with single result type, multiple result types, output parameter
  • Table-valued functions
  • Scalar-valued functions, composable or non-composable
  • Aggregate functions
  • Built-in functions
  • Niladic functions
  • Model defined functions

These contents are covered by a separate article: EntityFramework.Functions: Code First Functions for Entity Framework.

1 Comment

Add a Comment

As it will appear on the website

Not displayed

Your website