Entity Framework Core 1 (Entity Framework 7) Support in ADO.NET Data Providers

Entity Framework Core 1, formerly known as Entity Framework 7 (hereafter also called EF Core or EF7), is supported in Devart ADO.NET provider product line. It is supported in providers for relational databases: Oracle, MySQL, PostgreSQL, SQLite, and DB2, and provides for cloud data sources, such as Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, MailChimp, ExactTarget, Bigcommerce, and Magento. Currently, the providers support Entity Framework Core Release Candidate 1. Entity Framework Core support is currently in its public beta stage. It is implemented for the full .NET Framework platform–.NET Framework 4.5.1 and higher.

We need to mention first that Entity Framework Core 1 (Entity Framework 7) is a completely new ORM, which inherited only the name, LINQ support, and some classes with the same or similar names from Entity Framework 6, and even these classes often have incomplete functionality in comparison to their Entity Framework 6 counterparts. You can find more information about EF Core compatibility with EF6 and issues of migration in our other article Migrating Entity Framework 6 projects to Entity Framework Core 1 (Entity Framework 7). Additionally, you can study Entity Framework Core features in its official documentation.

New Assemblies and Provider Registration

For Entity Framework Core, a new assembly is added to our providers, and it should be deployed with applications using Entity Framework Core.

Additionally, we have included new extension methods for provider registration and specifying a connection string to our Entity Framework providers for EF Core (EF7).

The new assemblies and extension methods are listed in the following table:

In order to register an Entity Framework provider and set a connection string, you need to override the OnConfiguring method of your DbContext class descendant.

Example for Devart dotConnect for Oracle:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {

  optionsBuilder.UseOracle(@"user id=user; password=password; server=ORCL1210;");
}

Mapping

The mapping supported by Entity Framework Core is described in details in the corresponding section of Entity Framework documentation.

If you plan to support several data sources for one base Entity Framework model, that is mapped to tables and columns named differently in different data sources, you can use special provider-specific extension methods in fluent mapping in order to support specific mapping for each data source simultaneously:

There are also similar extension methods for specific mapping for each data source simultaneously:

• For{DataSourceName}HasName() – specifies the name of the index and primary key
• For{DataSourceName}HasConstraintName() – specifies the foreign key name
• For{DataSourceName}HasDefaultValueSql() – specifies the default column value

For example, for dotConnect for Oracle, these methods would be:

• ForOracleHasName()
• ForOracleHasConstraintName()
• ForOracleHasDefaultValueSql()

And, for other providers there are also such methods named respectively.

The following example demonstrates mapping an Account class to various tables depending on the data source:

  protected override void OnModelCreating(ModelBuilder modelBuilder) {

    modelBuilder.Entity<Account>()
      .ForSqlServerToTable("Account")
      .ForOracleToTable("ACCOUNTS")
      .ForMySqlToTable("active_accounts")
      .ForPostgreSqlToTable("account")
      .ForDB2ToTable("ACCOUNT")
      .ForSQLiteToTable("Account");
  }

Current Limitations

When applying this solution to production, you should consider that our Entity Framework Core support is still at beta stage, and Entity Framework Core has not yet reached the release stage too.

Our support is implemented only for the Full .NET Framework platform of version 4.5.1 and higher. We also considering the possibility of implementation for .NET Core and Universal Windows Platform, but there are no release dates scheduled.

Additionally, our providers support only a part of provider configuration settings. For example, for config.DatabaseScript.Schema.DeleteDatabaseBehaviour setting, only the DeleteDatabaseBehaviour.ModelObjectsOnly value (default value) is supported.

Some of the features present in EF6 providers are also not supported in EF Core providers because Entity Framework Core itself does not support many of Entity Framework 6 features. For example, these features include spatials or stored procedure support, etc. You can find more information about not supported features in another our blog article, “Migration of Entity Framework 6 projects to Entity Framework Core 1 (Entity Framework 7)”.

Behavior Changes

Some provider configuration settings changed their default value. For example, config.CodeFirstOptions.TruncateLongDefaultNames is now true by default, though it was false before. This change was made in order to eliminate or decrease the need to configure the provider in simple cases. Entity Framework often generates too long name for foreign keys, and user often had to set this option to true in order to perform dynamic database object generation, especially for Oracle, where there is the 30 character limit on the names. MySQL and PostgreSQL allow longer names, but sometimes users encounter such situation with them too.

There are some other changes in other aspects in comparison too Entity Framework 6, but these changes were made because we are trying to implement all the features of Entity Framework 6 we can with completely new EF Core engine. Some aspects are not yet implemented, but we are working hard to make behavior as similar as possible.

Database-First and Model-First in Entity Framework Core

Model-First approach is not supported in Entity Framework Core and there are no visual designer for Entity Framework Core models.

Database-First is supported via Package Manager Console. It is console-mode and very feature-limited. We have not yet implemented support for this functionality.

We plan to release a new version of a visual ORM model designer for Entity Framework – Devart Entity Developer. The new version will provide full implementation of the Database First and Model First approaches for Entity Framework Core. Devart Entity Developer has a wide feature set, advanced “Update From Database” support, generates code of entities, DbContext, and fluent mapping for them, and implements lots of other features.

Creating Simple Code-First Entity Framework Core Application

Let’s create a simple Code-First application that will create tables in the database based on the model in run-time, fill them with sample data, and execute queries.

Note that dynamic database object creation (tables/FK/PK/indexes/triggers/sequences) based on an Entity Framework model is implemented in Devart Entity Framework providers only for relational databases: Oracle, MySQL, PostgreSQL, SQLite, and DB2.

Prerequisites:

• You must have Visual Studio 2013 or Visual Studio 2015 installed.
• Install at least one of the Devart ADO.NET providers that support EF Core (EF7) and dynamic database object creation.

To create the sample application, let’s perform the following steps:

1. Create a new console application.
2. Ensure you are targeting .NET Framework 4.5.1 or later.
3. Install the Entity Framework Core NuGet package by executing the following command in the Package Manager Console:

   Install-Package EntityFramework.Relational –Pre

4. Add our provider-specific assemblies to the project references:
   • dotConnect common assembly – Devart.Data.dll
   • Provider-specific assembly Devart.Data.{DataSourceName}.dll: for Oracle it would beDevart.Data.Oracle.dll, for MySQL – Devart.Data.MySql.dll, and so on.
   • EF7 provider assembly Devart.Data.{DataSourceName}.Entity.EF7.dll: for Oracle it would beDevart.Data.Oracle.Entity.EF7.dll, for MySQL – Devart.Data.MySql.Entity.EF7.dll, and so on.
   Note that when using one of our providers for cloud applications, you would also need to add a reference to Devart.Data.SqlShim.dll – common assembly of Devart ADO.NET providers for cloud applications. However, this our example uses only dotConnect providers for relational databases.
5. Create a DbContext descendant.

   using Microsoft.Data.Entity;
   using System;
   using System.Collections.Generic;
   using System.ComponentModel.DataAnnotations;
   using System.ComponentModel.DataAnnotations.Schema;
   using System.Data;
   using System.IO;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   
   public class MyDbContext : DbContext {
   
   }

6. Register Entity Framework Core provider for using with our DbContext and specify the connection string. For this override the OnConfiguring method. Example for Devart dotConnect for Oracle:

   public class MyDbContext : DbContext {
   
     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
   
        optionsBuilder.UseOracle(@"user id=user; password=password; server=ORCL1210;");
     } 
   }

7. Create entity classes, used in the model. If necessary, set DataAnnotation attributes for the classes and properties.

     [Table("Product")]
     public class Product {
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long ProductID { get; set; }
   
       [Required]
       [MaxLength(50)]
       public string ProductName { get; set; }
   
       public string UnitName { get; set; }
       public int UnitScale { get; set; }
       public long InStock { get; set; }
       public double Price { get; set; }
       public double DiscontinuedPrice { get; set; }
   
       public virtual ProductCategory Category { get; set; }
       public virtual ICollection<OrderDetail> OrderDetails { get; set; }
     }
   
     [Table("ProductCategory")]
     public class ProductCategory {
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long CategoryID { get; set; }
   
       [Required]
       [MaxLength(20)]
       public string CategoryName { get; set; }
   
       public virtual ProductCategory ParentCategory { get; set; }
       public virtual ICollection<ProductCategory> ChildCategories { get; set; }
       public virtual ICollection<Product> Products { get; set; }
     }
   
     [Table("Order Details")]
     public class OrderDetail {
   
       [Column(Order = 1)]
       public long OrderID { get; set; }
   
       [Column(Order = 2)]
       public long ProductID { get; set; }
   
       public double Price { get; set; }
       public double Quantity { get; set; }
   
       public virtual Product Product { get; set; }
       public virtual Order Order { get; set; }
     }
   
     [Table("Orders")]
     public class Order {
   
       public Order() {
   
         OrderDate = DateTime.Now;
       }
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long OrderID { get; set; }
   
       [Required]
       public DateTime OrderDate { get; set; }
   
       public double Freight { get; set; }
       public DateTime? ShipDate { get; set; }
       public Double Discount { get; set; }
   
       public virtual ICollection<OrderDetail> OrderDetails { get; set; }
   
       [InverseProperty("Orders")]
       public virtual PersonContact PersonContact { get; set; }
   
       public virtual Company Company { get; set; }
   
       public virtual Company ShipCompany { get; set; }
     }
   
     [Table("Company")]
     public class Company {
   
       public Company() {
       }
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long CompanyID { get; set; }
   
       [Required]
       [MaxLength(40)]
       public string CompanyName { get; set; }
   
       [MaxLength(100)]
       public string Web { get; set; }
   
       [MaxLength(50)]
       public string Email { get; set; }
   
       public virtual AddressType Address { get; set; }
   
       [InverseProperty(nameof(Order.Company))]
       public virtual ICollection<Order> Orders { get; set; }
   
       [InverseProperty(nameof(Order.ShipCompany))]
       public virtual ICollection<Order> ShippedOrders { get; set; }
   
       [InverseProperty(nameof(PersonContact.Companies))]
       public virtual PersonContact PrimaryContact { get; set; }
   
       [InverseProperty(nameof(PersonContact.Company))]
       public virtual ICollection<PersonContact> Contacts { get; set; }
   
     }
   
     [Table("Person Contact")]
     public class PersonContact {
   
       public PersonContact() {
       }
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long ContactID { get; set; }
   
       [MaxLength(8)]
       public string Title { get; set; }
   
       [MaxLength(50)]
       public string FirstName { get; set; }
   
       [MaxLength(50)]
       public string MiddleName { get; set; }
   
       [MaxLength(50)]
       public string LastName { get; set; }
   
       [MaxLength(25)]
       public string HomePhone { get; set; }
   
       [MaxLength(25)]
       public string MobilePhone { get; set; }
   
       public virtual ddressType Address { get; set; }
   
       public virtual ICollection<Order> Orders { get; set; }
       public virtual Company Company { get; set; }
       public virtual ICollection<Company> Companies { get; set; }
     }
   
     [Table("AddressType")]
     public class AddressType {
   
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long Id { get; set; }
   
       [MaxLength(120)]
       public string AddressTitle { get; set; }
   
       [MaxLength(60)]
       public string Address { get; set; }
   
       [MaxLength(30)]
       public string City { get; set; }
   
       [MaxLength(20)]
       public string Region { get; set; }
   
       [MaxLength(15)]
       public string PostalCode { get; set; }
   
       [MaxLength(20)]
       public string Country { get; set; }
   
       [MaxLength(25)]
       public string Phone { get; set; }
   
       [MaxLength(25)]
       public string Fax { get; set; }
     }

8. Add our classes to the DbContext descendant as DbSet properties. If necessary, you can also write fluent mapping, by overriding the OnModelCreating method.

   public class MyDbContext : DbContext {
   
     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
   
       optionsBuilder.UseOracle(@"user id=user; password=password; server=ORCL1210;");
     }
   
     protected override void OnModelCreating(ModelBuilder modelBuilder) {
   
       modelBuilder.Entity<OrderDetail>()
         .HasKey(p => new { p.OrderID, p.ProductID });
     } 
   
     public DbSet<Product> Products { get; set; }
     public DbSet<ProductCategory> ProductCategories { get; set; }
     public DbSet<OrderDetail> OrderDetails { get; set; }
     public DbSet<Order> Orders { get; set; }
     public DbSet<Company> Companies { get; set; }
     public DbSet<PersonContact> PersonContacts { get; set; }
   }

9. Now let’s choose how to create the database. We can generate Code-First Migrations. Or, for test purposes, we can implement the analogue of Entity Framework 6 initialization strategy DropCreateDatabaseAlways.

     public static class MyDbContextSeeder {
   
       public static void Seed(MyDbContext context) {
   
         context.Database.EnsureDeleted();
         context.Database.EnsureCreated();
   
         context.ProductCategories.Add(new ProductCategory() {
           CategoryName = "prose"
         });
         context.ProductCategories.Add(new ProductCategory() {
           CategoryName = "novel"
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "poetry",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "novel")
         });
         context.ProductCategories.Add(new ProductCategory() {
           CategoryName = "detective story"
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "fantasy",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "novel")
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "pop art",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "fantasy")
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "textbook"
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "research book",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "textbook")
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "poem",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "novel")
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "collection",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "textbook")
         });
         context.ProductCategories.Add(new ProductCategory() { 
           CategoryName = "dictionary",
           ParentCategory = context.ProductCategories.Local().Single(p => p.CategoryName == "collection")
         });
   
         context.Products.Add(new Product() { 
           ProductName = "Shakespeare W. Shakespeare's dramatische Werke",
           Price = 78,
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "prose")
         });
         context.Products.Add(new Product() { 
           ProductName = "King Stephen. 'Salem's Lot", 
           Price = 67, 
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "poetry")
         });
         context.Products.Add(new Product() { 
           ProductName = "Plutarchus. Plutarch's moralia", 
           Price = 89, 
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "prose")
         });
         context.Products.Add(new Product() { 
           ProductName = "Twain Mark. Ventures of Huckleberry Finn", 
           Price = 34, 
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "novel")
         });
         context.Products.Add(new Product() { 
           ProductName = "Harrison G. B. England in Shakespeare's day", 
           Price = 540, 
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "novel")
         });
         context.Products.Add(new Product() { 
           ProductName = "Corkett Anne. The salamander's laughter", 
           Price = 5,
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "poem")
         });
         context.Products.Add(new Product() { 
           ProductName = "Lightman Alan. Einstein''s dreams", 
           Price = 5,
           Category = context.ProductCategories.Local().Single(p => p.CategoryName == "poem")
         });
   
         context.Companies.Add(new Company() {
           CompanyName = "Borland UK CodeGear Division",
           Web = "support.codegear.com/"
         });
         context.Companies.Add(new Company() {
           CompanyName = "Alfa-Bank",
           Web = "www.alfabank.com"
         });
         context.Companies.Add(new Company() {
           CompanyName = "Pioneer Pole Buildings, Inc.",
           Web = "www.pioneerpolebuildings.com"
         });
         context.Companies.Add(new Company() {
           CompanyName = "Orion Telecoms (Pty) Ltd.",
           Web = "www.oriontele.com"
         });
         context.Companies.Add(new Company() {
           CompanyName = "Orderbase Consulting GmbH",
           Web = "orderbase.de"
         });
   
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2007, 4, 11),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Borland UK CodeGear Division")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2006, 3, 11),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Borland UK CodeGear Division")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2006, 8, 6),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Alfa-Bank")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2004, 7, 6),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Alfa-Bank")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2006, 8, 8),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Alfa-Bank")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2003, 3, 1),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Pioneer Pole Buildings, Inc.")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2005, 8, 6),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2006, 8, 1),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2007, 7, 1),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2007, 2, 6),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Orderbase Consulting GmbH")
         });
         context.Orders.Add(new Order() {
           OrderDate = new DateTime(2007, 8, 1),
           Company = context.Companies.Local().Single(c => c.CompanyName == "Orderbase Consulting GmbH")
         });
   
         context.SaveChanges();
       }
     }
   
   namespace Microsoft.Data.Entity {
   
     public static class DbSetExtensions {
   
       public static IEnumerable<T> Local<T>(this DbSet<T> set)
         where T : class {
   
         var infrastructure = (Microsoft.Data.Entity.Infrastructure.IInfrastructure<IServiceProvider>)set;
   
         var context = (DbContext)infrastructure.Instance.GetService(typeof(DbContext));
   
         return context.ChangeTracker.Entries<T>()
           .Where(e => e.State == EntityState.Added || e.State == EntityState.Unchanged || e.State == EntityState.Modified)
           .Select(e => e.Entity);
       }
     }
   }

10. Now, let’s add code that creates the context, re-creates the database, fills it with the test data, and executes LINQ to Entities queries.

      class Program {
    
        static void Main(string[] args) {
    
          var context = new MyDbContext ();
    
          Console.WriteLine("Entity Framework Core (EF7) Code-First sample");
          Console.WriteLine();
    
          MyDbContextSeeder.Seed(context);
    
          Console.WriteLine("Products with categories");
          Console.WriteLine();
    
          var query = context.Products.Include(p => p.Category)
              .Where(p => p.Price > 20.0)
              .ToList();
    
          Console.WriteLine("{0,-10} | {1,-50} | {2}", "ProductID", "ProductName", "CategoryName");
          Console.WriteLine();
          foreach (var product in query )
            Console.WriteLine("{0,-10} | {1,-50} | {2}", product.ProductID, product.ProductName, product.Category.CategoryName);
    
          Console.ReadKey();
        }
      }

11. Now, we can run the application. It will create tables in the database, fill them with data, execute a query and output its results to the console:

    Entity Framework Core (EF7) Code-First sample
    
    Products with categories
    
    ProductID  | ProductName                                        | CategoryName
    
    1          | Harrison G. B. England in Shakespeare's day        | novel
    2          | Twain Mark. Ventures of Huckleberry Finn           | novel
    3          | Plutarchus. Plutarch's moralia                     | prose
    4          | Shakespeare W. Shakespeare's dramatische Werke     | prose
    5          | King Stephen. 'Salem's Lot                         | poetry

The attached archive contains a more complete application version with one Entity Framework model with several implementations for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, and DB2. The project also enables monitoring of the database calls via the free Devart DbMonitor tool.

Open the solution in Visual Studio, select a project for the installed provider and set it as Startup Project. Modify the connection string in the app.config file of the project, and after this, you can start it. You can also start Devart DbMonitor, and it will display the log of all the database interactions: connection opening/closing, transactions, DDL and DML statements, etc.