4.4. Creating an Entity Data Model (EDM)

In our application, we will use the Code First approach.

To create an EDM, right-click the project name in Solution Explorer and select Add New Item from the menu.

Figure 16. Solution Explorer — Add New Item

Next, in the Add New Item wizard, select ADO.NET Entity Data Model.

Figure 17. Add New Item wizard — select ADO.NET Entity Data Model

Since we already have a database, we will generate the EDM from the database. Select the icon captioned Code First from database.

Figure 18. Add New Item wizard — select ‘Code First from database’

Now we need to select the connection the model will be created from. If the connection does not exist, it will have to be created.

Figure 19. Add New Item wizard — choose Connection

You might need to specify some advanced properties in addition to the main connection properties. You might want to set the transaction isolation, for example, to a level different from the default Read Committed, or to specify connection pooling, or something else that differs from defaults.

Figure 20. Add Connection wizard — Connection properties

Figure 21. Add Connection wizard — Advanced connection properties

Next, the Entity Data Model wizard will ask you how to store the connection string.

Figure 22. EDM wizard — connection string storage

For a web application or another three-tier architecture, where all users will be working with the database using a single account, select Yes. If your application is going to request authentication for connecting to the database, select No.

<add name=”DbModel”
  connectionString=”character set=UTF8; data source=localhost;
  initial catalog=examples; port number=3050;
  user id=sysdba; dialect=3; isolationlevel=Snapshot;
  pooling=True; password=masterkey;”
  providerName=”FirebirdSql.Data.FirebirdClient” />

# WireCrypt = Enabled

WireCrypt = Disabled

Next, you will be asked which tables and views should be included in the model.

Figure 23. EDM wizard — select tables and views

For our project, select the four tables that are checked in the screenshot.

The basic EDM is now ready.

4.4.1. The EDM Files

When the wizard’s work is finished, you should have five new files: a model file and four files each describing an entity in the model.

An Entity File

Let’s take a look at the generated file describing the INVOICE entity:

[Table("Firebird.INVOICE")]
public partial class INVOICE
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
"CA2214:DoNotCallOverridableMethodsInConstructors")]
    public INVOICE()
    {
      INVOICE_LINES = new HashSet<INVOICE_LINE>();
    }
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int INVOICE_ID { get; set; }
    public int CUSTOMER_ID { get; set; }
    public DateTime? INVOICE_DATE { get; set; }
    public decimal? TOTAL_SALE { get; set; }
    public short PAYED { get; set; }
    public virtual CUSTOMER CUSTOMER { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
"CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<INVOICE_LINE> INVOICE_LINES { get; set; }
}

The class contains properties for each field of the INVOICE table. Each of these properties has attributes that describe constraints. You can study the details of the various attributes in the Microsoft document, Code First Data Annotations.

Navigation Properties and “Lazy Loading”

Two navigation properties are generated: CUSTOMER and INVOICE_LINES. The first one contains a reference to the customer entity. The second contains a collection of invoice lines. It is generated because the INVOICE_LINE table has a foreign key to the INVOICE table. Of course, you can remove this property from the INVOICE entity, but it is not really necessary. The CUSTOMER and INVOICE_LINES properties use “lazy loading” which means that loading is not performed until the first access to an object. That way, the loading of related data is avoided unless it is actually needed. Once the data are accessed via the navigation property, they will be loaded from the database automatically.

The DbModel File

Next, we examine the DbModel.cs file that describes the overall model.

public partial class DbModel : DbContext
{
    public DbModel()
        : base("name=DbModel")
    {
    }
    public virtual DbSet<CUSTOMER> CUSTOMERS { get; set; }
    public virtual DbSet<INVOICE> INVOICES { get; set; }
    public virtual DbSet<INVOICE_LINE> INVOICE_LINES { get; set; }
    public virtual DbSet<PRODUCT> PRODUCTS { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CUSTOMER>()
            .Property(e => e.ZIPCODE)
            .IsFixedLength();
        modelBuilder.Entity<CUSTOMER>()
            .HasMany(e => e.INVOICES)
            .WithRequired(e => e.CUSTOMER)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<PRODUCT>()
            .HasMany(e => e.INVOICE_LINES)
            .WithRequired(e => e.PRODUCT)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<INVOICE>()
            .HasMany(e => e.INVOICE_LINES)
            .WithRequired(e => e.INVOICE)
            .WillCascadeOnDelete(false);
    }
}

The properties coded here describe a dataset for each entity, along with advanced properties that are specified for creating a model with Fluent API. A complete description of the Fluent API can be found in the Microsoft document entitled Configuring/Mapping Properties and Types with the Fluent API.

We will use the Fluent API to specify precision and scale for properties of type DECIMAL in the OnModelCreating method, by adding the following lines:

modelBuilder.Entity<PRODUCT>()
    .Property(p => p.PRICE)
    .HasPrecision(15, 2);
modelBuilder.Entity<INVOICE>()
    .Property(p => p.TOTAL_SALE)
    .HasPrecision(15, 2);
modelBuilder.Entity<INVOICE_LINE>()
    .Property(p => p.SALE_PRICE)
    .HasPrecision(15, 2);
modelBuilder.Entity<INVOICE_LINE>()
    .Property(p => p.QUANTITY)
    .HasPrecision(15, 0);