Entity Framework

Go Up to ADO.NET Driver

This guide shows you the steps for setting up and using the Entity Framework core.

Creating New Project

1. Open Visual Studio
2. On the File menu go to New and then click Project. The New Project dialog box will open.
3. On the Create a new project dialog, select Console Application for C# from the templates list and click Next.

   

4. On the Configure your new project section, change the project name and location if necessary. Click on Next.

   

5. On the Additional information section, select a .NET Core version and click Create.

   

6. A new project will be created.

Installing NuGet Packages

After creating a new project, you need to add the necessary NuGet packages to it.

To install the necessary packages follow these steps:

1. On the Tools menu go to NuGet Package Manager and then click Manage NuGet Packages for Solution.
2. For Entity Framework Core 6.0, add Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.Design.
3. Add the InterBase package InterBaseSQL.EntityFrameworkCore.InterBase

Note:
Make sure both Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.Design are version 6.0.11.

Creating a Model From the Database

To create a model from the database enter the Scaffold-DbContext command using a connection string and a provider as parameters. For example, run the following command in the Package Manager Console:

Scaffold-DbContext "data source=localhost;initial catalog=Employee;user id=sysdba;password=masterkey" InterBaseSQL.EntityFrameworkCore.InterBase

This assumes an Alias pointing to the Employee database. If there are tables in the database, you may use additional parameters -Schemas and -Tables to filter the list of schemas and/or tables that are added to the model. For example, you can use the following command:

Scaffold-DbContext "data source=localhost;initial catalog=Employee;user id=sysdba;password=masterkey" InterBaseSQL.EntityFrameworkCore.InterBase -Tables employee,department,customer –o Model

This sets up the basic framework. The DBContext Framework in the EmployeeContext.cs is:

   public partial class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employee { get; set; }
        public EmployeeContext()
        {
        }
        public EmployeeContext(DbContextOptions<EmployeeContext> options)
            : base(options)
        {
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseInterBase("data source=localhost;initial catalog=Employee;user id=sysdba;password=masterkey");
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            OnModelCreatingPartial(modelBuilder);
        }        
        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Next, add a new C# class called "Employee". You need to define the Employee class the Entity framework will work through.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;
namespace ConsoleApp3.Model
{
    public partial class Employee
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Emp_No {get; set;}
        public string First_Name { get; set; }
        public string Last_Name { get; set; }
        public string Phone_Ext { get; set; }
        public DateTime Hire_Date { get; set; }
        public string Dept_No { get; set; }
        public string Job_Code { get; set; }
        public int Job_Grade { get; set; }
        public string Job_Country { get; set; }
        public decimal Salary { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string Full_Name { get; }
    }
}

Next, go back to the EntityContext class and update the mapping from the Employee class to the Employee table. This is done in the OnModelCreating method

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            OnModelCreatingPartial(modelBuilder);
            modelBuilder.Entity<Employee>(entity =>
            {
                entity.HasKey(e => e.Emp_No).HasName("EMP_NO");
                entity.Property(e => e.Emp_No).HasColumnName("EMP_NO");
                entity.Property(e => e.First_Name).HasColumnName("FIRST_NAME");
                entity.Property(e => e.Last_Name).HasColumnName("LAST_NAME");
                entity.Property(e => e.Phone_Ext).HasColumnName("PHONE_EXT");
                entity.Property(e => e.Hire_Date).HasColumnName("HIRE_DATE");
                entity.Property(e => e.Dept_No).HasColumnName("DEPT_NO");
                entity.Property(e => e.Job_Code).HasColumnName("JOB_CODE");
                entity.Property(e => e.Job_Grade).HasColumnName("JOB_GRADE");
                entity.Property(e => e.Job_Country).HasColumnName("JOB_COUNTRY");
                entity.Property(e => e.Salary).HasColumnName("SALARY");
                entity.Property(e => e.Full_Name).HasColumnName("FULL_NAME");

                entity.ToTable("EMPLOYEE");
            });
        }

Also add a method to get Generator values

using Microsoft.EntityFrameworkCore;
namespace EFCore101 {
partial class EmployeeContext : DbContext {
 public long GetNextSequenceValue(string genName) {
    using(var cmd = Database.GetDbConnection().CreateCommand()) {
      Database.GetDbConnection().Open();
      cmd.CommandText = "SELECT gen_id(" + genName + ", 1) from rdb$database";
      var obj = cmd.ExecuteScalar();
      return (long)obj;
    }
  }
}
}

Now the Framework is ready to be used for the Employee table. Go back to the main program and insert a new record, then list all the records like this:

using System;
using ConsoleApp3.Model;
namespace ConsoleApp3
{
class Program
{
  static void Main(string[] args)
  {
    using(var db = new EmployeeContext())
    {
      // Creating a new Employee and saving it to the database

      var newEmp = new Employee()
          {
              Emp_No = (short)db.GetNextSequenceValue("EMP_NO_GEN"),
              First_Name = "John",
              Last_Name = "Doe",
              Phone_Ext = "000",
              Hire_Date = DateTime.Now,
              Dept_No = "900",
              Job_Code = "Sales",
              Job_Grade = 3,
              Job_Country = "USA",
              Salary = 45000
          };
      db.Employee.Add(newEmp);
      var count = db.SaveChanges();
      Console.WriteLine("{0} records saved to database", count);
      // Retrieving and displaying data
      Console.WriteLine();
      Console.WriteLine("All Employees in the database:");
      foreach (var emp in db.Employee)
      {
        Console.WriteLine("{0} | {1} | {2}", emp.Full_Name, emp.Salary, emp.Dept_No);
      }
    }
  }
}
}

For the most part everything should work just like any other Entity framework example. Current drawback is retrieving changes made on the DB side on Inserts and Updates. To see these you need to requery the Record.

Advance To:

• DDEX