Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications with Microsoft Entity Framework → Creating an Entity Data Model (EDM)
Firebird Home Firebird Home Prev: Creating a ProjectFirebird Documentation IndexUp: Developing Firebird Applications with Microsoft Entity FrameworkNext: Creating a User Interface

Creating an Entity Data Model (EDM)

Table of Contents

The EDM Files

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 4.6. Solution Explorer - Add—>New Item

Solution Explorer - Add—>New Item


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

Figure 4.7. Add New Item wizard - select ADO.NET Entity Data Model

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 4.8. Add New Item wizard - select 'Code First from database'

Add New Item wizard - select 'Code First from database'


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

Figure 4.9. Add New Item wizard - choose Connection

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 4.10. Add Connection wizard - Connection properties

Add Connection wizard - Connection properties


Figure 4.11. Add Connection wizard - Advanced connection properties

Add Connection wizard - Advanced connection properties


Tip

Snapshot is the recommended isolation level because Entity Framework and ADO.NET both use disconnected data access—where each connection and each transaction is active only for a very short time.

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

Figure 4.12. EDM wizard - connection string storage

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.

Tip

It is much more convenient to work with wizards if you select Yes for each property. You can always change the isolation level in the application when it is ready for testing and deployment by just editing the connection string in the <AppName>.exe.conf application configuration file. The connection string will be stored in the connectionStrings section and will look approximately like this:

<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" />
          

For the configuration file to stop storing the confidential information, just delete this parameter from the connection string: password=masterkey;

Firebird 3.0 Notes

Unfortunately, the current ADO.Net provider for Firebird (version 5.9.0.0) does not support network traffic encryption, which is enabled by default in Firebird 3.0 and higher versions. If you want to work with Firebird 3.0, you need to change some settings in firebird.conf (or in databases.conf for a specific database) to make Firebird to work without trying to use network encryption.

To do it, change the setting from the default

 # WireCrypt = Enabled
        

to

 WireCrypt = Disabled
        

making sure to delete the '#' comment marker. Remember that you must restart the server for configuration changes to take effect.

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

Figure 4.13. EDM wizard - select tables and views

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.

The EDM Files

Table of Contents

An Entity File
The DbModel File

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.

Important

If lazy loading is in effect, classes that use it must be public and their properties must have the keywords public and virtual.

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);
          

Prev: Creating a ProjectFirebird Documentation IndexUp: Developing Firebird Applications with Microsoft Entity FrameworkNext: Creating a User Interface
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications with Microsoft Entity Framework → Creating an Entity Data Model (EDM)