DotNetCurry Logo

Entity Framework Core (EF Core) 2.0 – New Cool Features

Posted by: Mahesh Sabnis , on 11/2/2017, in Category Entity Framework
Views: 9399
Abstract: New Features in Entity Framework Core 2.0. EF Core is a lightweight, extensible, and cross-platform Object/Relational Mapping (O/RM) framework for .NET.

This article explains and demonstrates some new cool features of Entity Framework Core 2.0. EF Core is a lightweight, extensible and cross-platform ORM version of Entity Framework. This article explains the following new features:

1. Flexible Object Mapping

2. Table Splitting and Owned Types

3. Scalar Function Mapping

4. Compiled Queries

5. String Interpolation

6. DBContext Pooling

This article demonstrates the EF Core 2.0 new features by creating a separate Console Applications. Readers can create ASP.NET Core applications while implementing the EF Core 2.0 new features.

Editorial Note: If you are interested in how Entity Framework has evolved so far, read this useful article from Ricardo at The State of Entity Framework Core - Past, Present and Future

Prerequisites for the application

To implement the application, the following software must be installed on the machine:

1. Microsoft Visual Studio 2017 Community Edition with Version update 15.3.x

2. .NET Core Runtime

3. .NET Core SDK 2.0 (This includes .NET Core runtime)

4. SQL Server 2012 Onwards

Are you keeping up with new developer technologies? Advance your IT career with our Free Developer magazines covering C#, Patterns, .NET Core, MVC, Azure, Angular, React, and more. Subscribe to the DotNetCurry (DNC) Magazine for FREE and download all previous, current and upcoming editions.

EF Core 2.0 – New Features

Open a SQL Server instance and create a new Database using the following command:

Create database PersonInfoDB

Flexible Object Mapping

In the Code-First approach, by convention, Entity Framework generates database table based on the public properties defined in the entity class. The ModelBuilder class maps the public properties of the entity class with the Database Table Columns.

In EF Core 2.0, the Flexible Object Mapping feature reads the backing field (non-public field) as string and maps it with the Database Table Column. The Flexible Object Mapping feature is implemented using code, so it is important to define public methods which will Read/Write the data in the backing field.

Step 1: Open Visual Studio 2017 and create a blank solution. Name this solution as EFCore_20_Demos. In this solution, add a new .NET Core Console application as shown in the following image:

new-consoleapp

Name this project as CS_Core_Flexible_Object_Mapping.

Step 2: Add the following NuGet Packages (Note: All these packages must be added to all projects.)

Microsoft.EntityFrameworkCore

add-nuget-efcore

Microsoft.EntityFrameworkCore.SqlServer

add-nuget-efcore-sql

Microsoft.Extensions.Logging

add-nuget-ms-logging

Microsoft.Extensions.Logging.Console

add-nuget-ms-logging-console

Step 3: In the project, add a new folder of the name Models. In this folder, add a new class file called ModelClasses.cs (alternatively, separate code file per class can be created having the file name same as the name of the class, e.g. Person.cs) with the following code in it:

namespace CS_Core_Flexible_Object_Mapping.Models
{
    public class Person
    {
        public int PersonId { get; set; }
        public string FisrtName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }

        private string _Email;

        public void SetEmail(string email)
        {
            _Email = email;
        }

        public string GetEmail()
        {
            return _Email;
        }

        private string _ContactNo;

        public void SetContactNo(string contact)
        {
            _ContactNo = contact;
        }

        public string GetContact()
        {
            return _ContactNo;
        }
    }
}

This class has two private fields _Email and _ContactNo. Methods SetEmail(), GetEmail(), SetContactNo() and GetContactNo() are used to Read/Write values for these backing fields.

Step 4: In the Models folder, add a new class file called PersonalInformationDB.cs. In this class file, add the following code:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace CS_Core_Flexible_Object_Mapping.Models
{
    public class PersonalInfoDBContext : DbContext
    {
        public DbSet Persons { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"Server=.;Database=PersonInfoDB;Trusted_Connection=True;")
                .UseLoggerFactory(new LoggerFactory().AddConsole());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property("Email").HasField("_Email");
            modelBuilder.Entity()
                .Property("ContactNo").HasField("_ContactNo");
        }
    }
}

The class PersonalInfoDBContext is derived from DbContext.

The class defines Persons property of the type DbSet. This maps with the Persons Table which will be created in the database. The class also overrides OnConfiguring() method. This method defines the connection string of the Sql Server database.

The UseLoggerFactory() method accepts LoggerFactory() object and its AddConsole() method logs the actions to the console. The important part of the code is the OnModelCreating() method.

The following is the implementation code for the method:

modelBuilder.Entity()
                .Property("Email").HasField("_Email");
modelBuilder.Entity()
                .Property("ContactNo").HasField("_ContactNo");

The ModelBuilder parameter of this method reads the Person entity object and its Property() method reads the property of the entity class. If the property exists in the entity class then it will be added to the model. If the property does not exist in the entity class then a Shadow State Property will be added.

A Shadow State property is one that does not have corresponding property in the entity class. The current value of the property is stored in the Microsoft.EntityFrameworkCore.ChangeTracking.ChangeTracker instance of DbContext rather than being stored in an instance of the entity class. The HasField() method accepts the backing field (non-public field). The value set for this backing field is then added in the table after the table is created in the database.

Step 5: In Program.cs, add the following code:

using CS_Core_Flexible_Object_Mapping.Models;
using Microsoft.EntityFrameworkCore;
using System;

namespace CS_Core_Flexible_Object_Mapping
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                ManageDatabase();
                var person = new Person
                {
                    FisrtName = "Mahesh",
                    MiddleName = "Ramesh",
                    LastName = "Sabnis",
                    Address = "Bavdhan-Pune"
                };

                person.SetEmail("sabnis_m@somemail.com");
                person.SetContactNo("633123857528272636");

                var db = new PersonalInfoDBContext();
                db.Persons.Add(person);
                db.SaveChanges();
                var persons = db.Persons.ToListAsync().Result;
                foreach (var item in persons)
                {
                    Console.WriteLine($" Person Details {item.FisrtName} {item.MiddleName} {item.LastName} {item.Address} {item.GetEmail()} {item.GetContact()}");
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }

        static void ManageDatabase()
        {
            
            using (var ctx= new PersonalInfoDBContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
            }
        }
    }
}

In the above code, the ManageDatabase() method checks if the database is created by deleting it and forcing its creation. This method is called in the Main() method.

The person object is initialized with values for the properties in the class. The SetEmail() and SetContactNo() methods accepts Email and ContactNo values which will be passed to _Email and _ContactNo backing fields. The code further creates an instance of PersonalInfoDBContext class and add the Person object to the Persons DbSet property.

Furthermore, the code reads data from the database table and iterates over it.

Run the application, the following result will be shown:flexible-mappingThe output shows that the Persons table is created with Email and ContactNo columns, this means that although the _ContactNo and _Email are backing fields, the shadow state property added by EFCore is used by the EFCore to create columns in the database table. This provides flexibility for defining entity class (model class) so that it’s now optional to define public properties in the class which need to be mapped to the Database table using EF.

Table Splitting and Owned Types

In case of an ORM, sometimes it so happens that the entity class defines complex type properties e.g. If the Person class has a CurrentAddress property of the type Address, then the Address property is defined as navigation properties in the Person class when the database table mapping is defined.

In EFCore 2.0, Table Splitting and Own Type features are provided which are used to manage the database table generation and mapping.

Table Splitting

In EF Core 2.0, it is now possible to map two or more entity types to the same table. In this case, the primary key column will be shared and each row will correspond to all mapped entities.

In table splitting, the key relationship must be configured across all of the entity types for those shared tables.

Owned Types

In this feature, owning entity can share the same CLR type with another owning entity type. But in this case, there must be a navigation set to the other entity type because the owning type cannot be just identified by the CLR type.

The entity which contains the defining navigation is the owner. When the owner is queried, the owned types will be included by default.

Step 1: In the solution created in the previous demonstration, add a new .NET Core console application. Name this application as CS_OwnType_TableSplitting. In this project, add same NuGet packages which are added in the Flexible Object Mapping demonstration.

Step 2: Copy the Models folder of the CS_OwnType_TableSplitting project and paste it in the current project. In the ModelClasses.cs file add the Add, Region and Country class and modify the Person class as shown in the following code:

using System;
using System.Collections.Generic;
using System.Text;

namespace CS_Core_Flexible_Object_Mapping.Models
{
    public class Person
    {
        public int PersonId { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public Address CurrentAddress { get; set; }
        public Address PermanentAddress { get; set; }



        private string _Email;

        public void SetEmail(string email)
        {
            _Email = email;
        }

        public string GetEmail()
        {
            return _Email;
        }

        private string _ContactNo;

        public void SetContactNo(string contact)
        {
            _ContactNo = contact;
        }

        public string GetContact()
        {
            return _ContactNo;
        }
    }

    public class Address
    {
        public string HouseNo { get; set; }
        public string Society { get; set; }
        public string Details { get; set; }
        public Region Region { get; set; }
    }

    public class Region
    {
        public int RegionId { get; set; }
        public string City { get; set; }
        public string District { get; set; }
        public string State { get; set; }
        public string CountryId { get; set; }
        public Country Country { get; set; }
    }

    public class Country
    {
        public string CountryId { get; set; }
        public string CountryName { get; set; }
    }
}

The above code has the following specifications:

  • The Region class contains CountryId property of the type string and Country property of the type Country object.
  • The Address class contains Region property of the type Region object. The Person class defines CurrentAddress and PermanantAddress properties of the type Address object.

This design creates three tables - Country, Region and Person. To make sure that the Person Owns Address type and since the Address contains Region as a property, we need to split the mapping in Person and Region tables.

Modify the PersonalInfoDBContext class as shown in the following code:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace CS_Core_Flexible_Object_Mapping.Models
{
    public class PersonalInfoDBContext : DbContext
    {
        public DbSet Persons { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"Server=.;Database=PersonInfoDB;Trusted_Connection=True;")
                .UseLoggerFactory(new LoggerFactory().AddConsole());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            //The Person table will have the current address as its own property
            modelBuilder.Entity()
                 .OwnsOne(p => p.CurrentAddress)
                 .OwnsOne(r=>r.Region);

            //The Table Splitting, this will generate two tables Persons and Region
            modelBuilder.Entity()
                .OwnsOne(p => p.PermanentAddress)
                .ToTable("Region").
                OwnsOne(r => r.Region);
        }
    }
}

The PersonalInDBContext defines the Persons property of type DbSet.

The OnModelCreating() method defines the following code:

modelBuilder.Entity()
                 .OwnsOne(p => p.CurrentAddress)
                 .OwnsOne(r=>r.Region);

Here the OwnOne() method is called twice, the first call of the OwnOne() method means that the entity Person owns the CurrentAddress property of the type Address because this property is defined in the Person entity. The Address class defines Region property of the type Region, the next call to OwnOne() states that the Region property is owned by the Address class.

Here’s the code in the OnModelMapping() method:

modelBuilder.Entity()
    .OwnsOne(p => p.PermenantAddress)
    .ToTable("Region").
    OwnsOne(r => r.Region);

In the above case, the PermanentAddress property is owned by the Person class. The ToTable() method accepts the string parameter, in this case Region, this means that the new table Region will be mapped with the entity.

Step 3: In the Program.cs, add the following code:

using System;
using Microsoft.EntityFrameworkCore;
using CS_Core_Flexible_Object_Mapping.Models;

namespace CS_Core_Flexible_Object_Mapping
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                ManageDatabase();
                var db = new PersonalInfoDBContext();
                var india = new Country { CountryId ="IN", CountryName = "India" };
                db.Add(india);

                var person = new Person
                {
                    FirstName = "Mahesh",
                    MiddleName = "Ramesh",
                    LastName = "Sabnis",
                    CurrentAddress = new Address() {
                        HouseNo = "601",
                        Society = "PF",
                        Details = "Bavdhan",
                        Region = new Region() {
                            RegionId = 1001, 
                             City = "Pune",
                             District = "Pune",
                             State = "Maharashtra",
                             Country = india
                         }
                    },
                    PermanentAddress =new Address() {
                        HouseNo = "3/601",
                        Society = "AF",
                        Details = "Bavdhan-Kh",
                        Region = new Region() {
                            City = "Pune",
                            District = "Pune",
                            State = "Maharashtra",
                            Country = india
                        }
                    }

                };

                person.SetEmail("sabnis_m@hotmail.com");
                person.SetContactNo("633123857528272636");

             
                db.Persons.Add(person);
                db.SaveChanges();
                var persons = db.Persons.ToListAsync().Result;
                foreach (var item in persons)
                {
                    Console.WriteLine($"Person Details {item.FisrtName} {item.MiddleName} {item.LastName}  {item.GetEmail()} {item.GetContact()}");
                    Console.WriteLine($"Address {item.CurrentAddress.HouseNo} {item.CurrentAddress.Society} {item.CurrentAddress.Details}");
                    Console.WriteLine($"Region {item.CurrentAddress.Region.City} {item.CurrentAddress.Region.District} {item.CurrentAddress.Region.State}");
                    Console.WriteLine($"Address {item.PermenantAddress.HouseNo} {item.PermenantAddress.Society} {item.PermenantAddress.Details}");
                    Console.WriteLine($"Region {item.PermenantAddress.Region.City} {item.PermenantAddress.Region.District} {item.PermenantAddress.Region.State}");
                }
                Console.ReadLine();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }

        static void ManageDatabase()
        {

            using (var ctx = new PersonalInfoDBContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
            }
        }
    }
}

In the above code, the ManageDatabase() method makes sure that the database is created.

This method is called in the Main() method. The Main() method defines an instance of the PersonalInfoDbContext class. The Country is instantiated with default values for properties CountryId and CountryName. This object is added to the PersonalInfoDbContext class.

The Person object is initialized with its properties including CurrentAddress and PermenantAddress and Region property of the Address class. The person object is added to the PersonalInfoDbContext. The code further queries the Persons DbSet and prints the data.

Run the application, the result will be as shown here:

own-type-table-splitting

This will create the following three tables:

  • Country > CountryId as Primary Key
  • Persons > PersonId as primary Key and CountryId as foreign Key. This is because the CountryId is defined in the Region class and the Region property of the type Region is Owned By the Address class and the Person class owns CurrentAddress property of the type Address. So as explained in the Own Types, since the Person class defines navigation to Address and Address Region, and then Region to Country; the CountryId is set as foreign key in the Persons table.
  • Region > The table which contains PersonId as Primary key.

The Owned Type and Table Splitting features provides an easy mechanism to manage navigation types to define Owner Entity and mapping an entity to more than one table.

Although the PersonalInfoDBContext defines only one DbSet property, the database is added with Country, Persons and Region tables.

Scalar Function Mapping

This is another important feature of EFCore 2.0. This enables mapping database scalar functions to a method in the class.

This method can be used in LINQ queries and is translated into a SQL Statement. This provides a cool functionality to the developer who is writing Data Access Layer to create a method and apply the DbFunctionAttribute on it. This attribute maps the static CLR method to the database function so that this method can be used in the LINQ Query.

By default, the CLR static method name in the database function must be same, unless we specify a different name in the DbFunctionAttribute.

Step 1: In the solution, add a new .NET Core console project, name this project as CS_DB_Functions. In this project, add NuGet Packages which are the same as those added in Flexible Object Mapping example. In addition to this, add the following NuGet Package in this project.

add-nuget-linq

Step 2: In this project, copy the Models folder from the Flexible Object Mapping example. In the Models folder, modify the ModelsClasses.cs file as shown in the following code:

namespace CS_DB_Functions.Models
{
    public class Employee
    {
        public int EmpId { get; set; }
        public string EmpName { get; set; }
        public string DeptName { get; set; }
        public string Designation { get; set; }
        public int Salary { get; set; }
    }
}

Step 3: In the PersonalInformationDB.cs file, add the following code:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;

namespace CS_DB_Functions.Models
{
    public class PersonalInfoDBContext : DbContext
    {
        public DbSet Employees { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"Server=.;Database=PersonInfoDB;Trusted_Connection=True;")
                .UseLoggerFactory(new LoggerFactory().AddConsole());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasKey("EmpId");
            modelBuilder.HasDbFunction(() => DatabaseOperations.GetEmployeeCountByDeptName(""));
        }
    }

    ///      /// Class containing Code to make sure that the Database is created     /// and add new Employees table in it with records.     /// 
    public class DatabaseOperations
    {
        public static void ManageDatabase()
        {

            using (var ctx = new PersonalInfoDBContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
                List employees = new List();

                employees.Add(new Employee() { EmpName = "Mahesh", DeptName = "IT", Designation = "Manager", Salary = 12000 });
                employees.Add(new Employee() { EmpName = "Ajay", DeptName = "HR", Designation = "Lead", Salary = 8000 });
                employees.Add(new Employee() { EmpName = "Kishore", DeptName = "SL", Designation = "Lead", Salary = 22000 });
                employees.Add(new Employee() { EmpName = "Abhay", DeptName = "IT", Designation = "Manager", Salary = 7000 });
                employees.Add(new Employee() { EmpName = "Anil", DeptName = "HR", Designation = "Lead", Salary = 32000 });
                employees.Add(new Employee() { EmpName = "Jaywant", DeptName = "SL", Designation = "Manager", Salary = 10000 });
                employees.Add(new Employee() { EmpName = "Shyam", DeptName = "IT", Designation = "Manager", Salary = 62000 });
                employees.Add(new Employee() { EmpName = "Ramesh", DeptName = "HR", Designation = "Lead", Salary = 4000 });
                employees.Add(new Employee() { EmpName = "Keshav", DeptName = "SL", Designation = "Manager", Salary = 34000 });
                employees.Add(new Employee() { EmpName = "Anil", DeptName = "SL", Designation = "Lead", Salary = 15000 });

                foreach (var emp in employees)
                {
                    ctx.Employees.Add(emp);
                }
                ctx.SaveChanges();

                ctx.Database.ExecuteSqlCommand(
                       @"CREATE FUNCTION  [GetEmployeeCountByDeptName] (@DeptName Varchar) RETURNS INT 
                          AS
                          BEGIN
                            DECLARE @count AS INT
                            SELECT @count = COUNT(*) FROM Employees WHERE DeptName = @DeptName
                            RETURN @count
                          END");
            }
        } 
        ///          /// The CLR Static function which will be mapped to the database scalar function of the same name         /// 
        /// 
        /// 
        [DbFunction()]
        public static int GetEmployeeCountByDeptName(string DeptName)
        {
            return 0;
        }

    }
}

In the above code, the PersonalInfoDBContext class defines a connection string to the database. The Employees property of the type DbSet is defined for mapping with the Employees table in the database.

The class DatabaseOperations contains the ManageDatabase() method. This method makes sure that the database is created. This method defines employees List object with default data. This data is added in the Employees DbSet defined in the PersonalInfoDBContext class.

The ManageDatabase() method also create a Database function of name GetEmployeeCountByDeptName which accepts DeptName as an input parameter. This function returns count of the Employees based on the DeptName.

The DatabaseOperations class contains GetEmployeeCountByDeptName() static CLR method which is applied with DbFunction attribute on it. This means that the static CLR method is mapped with the scalar function of the database. The mapping between the CLR method and the database function is defined in the OnModelCreating() method of the PersonalInfoDBContext class as shown in the following code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity().HasKey("EmpId");
    modelBuilder.HasDbFunction(() => DatabaseOperations.GetEmployeeCountByDeptName(""));
}

The HasDbFunction() method of the ModelBuilder class accepts the static CLR method with DbFunction attribute to map it with the database function. There are two approaches for the Scalar function mapping i.e. DbFunctionAttribute and HasDbFunction() method. The above code explains both these approaches.

Step 4: In the Program.cs, add the following code:

using CS_DB_Functions.Models;
using System;
using System.Linq;

namespace CS_DB_Functions
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                DatabaseOperations.ManageDatabase();
                using (var ctx = new PersonalInfoDBContext())
                {
                    var empCount = ctx.Employees.Select(e=>DatabaseOperations.GetEmployeeCountByDeptName(e.DeptName));

                    Console.WriteLine($"Count = {empCount.Count()}");
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();   
        }
      
    }
}

In the above code, the GetEmployeeCountByDeptName() method is used in the LINQ query.

Running the application, gets the following result:

scalar-function

The CLR method mapped with the database function is executed and the result is returned.

Compiled Queries

Compiled queries are a performance improving feature of EFCore. This is designed to offer high-scaling application scenarios.

This feature allows applications to cache the translation of queries so that they are computed only once and executed several times. This approach helps to gain performance for the application.

Step 1: In the solution, add a new .NET Core console application of name CS_Compiled_Queries. In this project, add NuGet Packages as explained in the Flexible Object Mapping example. Copy the Models folder of the Scalar function mapping example and paste it in the current project.

Step 2: Add the following code in the Program.cs:

using CS_Compiled_Queries.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Diagnostics;
using System.Linq;

namespace CS_Compiled_Queries
{
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseOperations.ManageDatabase();
            RegularQuery("IT");
            Console.WriteLine();
            Console.WriteLine();
            CompiledQuery("IT");
            Console.ReadLine();
        }

        ///          /// The Regular LINQ Example         /// 
        ///         
        static void RegularQuery(string dname)
        {

            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var ctx = new PersonalInfoDBContext();
            var Emps = ctx.Employees.Where(e=>e.DeptName==dname).ToList();
            foreach (var emp in Emps)
            {
                Console.WriteLine($"{emp.EmpId} {emp.EmpName} {emp.Designation} {emp.DeptName} {emp.Salary}" );
            }
            stopwatch.Stop();
            Console.WriteLine($"Regular Query by => {dname}: takes  {stopwatch.ElapsedMilliseconds.ToString().PadLeft(4)}ms time");
        }

        ///          /// The Compiled Query Example         /// 
        /// 
        static void CompiledQuery(string dname)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var queryResult = EF.CompileAsyncQuery(
                  (PersonalInfoDBContext ctx, string d)=>ctx.Employees.Where(e=>e.DeptName==d));

            PersonalInfoDBContext db = new PersonalInfoDBContext();

            var Emps = queryResult(db, dname).ToListAsync().Result;

            foreach (var emp in Emps)
            {
                Console.WriteLine($"{emp.EmpId} {emp.EmpName} {emp.Designation} {emp.DeptName} {emp.Salary}");
            }

            stopwatch.Stop();
            Console.WriteLine($"Compiled Query by => {dname}: Taken {stopwatch.ElapsedMilliseconds.ToString().PadLeft(4)}ms time");
        }
    }
}

In the above code, the RegularQuery() method shows the regular example of the LINQ query, this contains code for querying Employees table based on the DeptName. The CompiledQuery() function contains code for Compiled Query using the static EF class.

The CompiledQuery() method contains the following code:

var queryResult = EF.CompileAsyncQuery(
                  (PersonalInfoDBContext ctx, string d)=>ctx.Employees.Where(e=>e.DeptName==d));

The CompileAsyncQuery() method accepts a delegate. This delegate will execute the LINQ query when this delegate is invoked.

The following code shows the delegate invocation:

PersonalInfoDBContext db = new PersonalInfoDBContext();

var Emps = queryResult(db, dname).ToListAsync().Result;

The result of the LINQ query execution will be stored in the Emps object.

RegularQuery() and CompiledQuery() methods are accessed in the Main() method and the DeptName is passed to these method.

Run the application, the following result will be displayed:

compiled-query

The above image clearly shows that the Compiled query improves performance of the execution.

String Interpolation in FromSql() method

C# 6.0 introduced the string interpolation featur. This allowed to embed C# expressions directly in string literals. This provides an easy mechanism of building strings at runtime.

In EF 2.0, special support for string interpolation is introduced which provides an easy syntax for embedding C# expression in SQL Queries. A major advantage of String Interpolation in FromSql() method is that, it is useful to prevent SQL injection as it uses parameterized SQL queries.

Step 1: In the solution, add a new .NET Core console application of the name CS_String_Interpolation. Add NuGet packages as like the previous examples. Copy the Models folder of the previous example in this project.

Step 2: Modify the PersonalInfoDBContext class as shown in the following code:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace CS_String_Interpolation.Models
{
    public class PersonalInfoDBContext : DbContext
    {
        public DbSet Employees { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"Server=.;Database=PersonInfoDB;Trusted_Connection=True;")
                .UseLoggerFactory(new LoggerFactory().AddConsole());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasKey("EmpId");
        }
    }
}

Step 3: Add the following code in Program.cs:

using System;
using System.Collections.Generic;
using CS_String_Interpolation.Models;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace CS_String_Interpolation
{
    class Program
    {
        static void Main(string[] args)
        {
            List employees = new List(); 
            try
            {
                ManageDatabase();

                var db = new PersonalInfoDBContext();

                employees.Add(new Employee() {  EmpName = "Mahesh", DeptName = "IT", Designation = "Manager", Salary = 12000 });
                employees.Add(new Employee() {  EmpName = "Ajay", DeptName = "HR", Designation = "Lead", Salary = 8000 });
                employees.Add(new Employee() {  EmpName = "Kishore", DeptName = "SL", Designation = "Lead", Salary = 22000 });
                employees.Add(new Employee() {  EmpName = "Abhay", DeptName = "IT", Designation = "Manager", Salary = 7000 });
                employees.Add(new Employee() {  EmpName = "Anil", DeptName = "HR", Designation = "Lead", Salary = 32000 });
                employees.Add(new Employee() {  EmpName = "Jaywant", DeptName = "SL", Designation = "Manager", Salary = 10000 });
                employees.Add(new Employee() {  EmpName = "Shyam", DeptName = "IT", Designation = "Manager", Salary = 62000 });
                employees.Add(new Employee() {  EmpName = "Ramesh", DeptName = "HR", Designation = "Lead", Salary = 4000 });
                employees.Add(new Employee() {  EmpName = "Keshav", DeptName = "SL", Designation = "Manager", Salary = 34000 });
                employees.Add(new Employee() {  EmpName = "Anil", DeptName = "SL", Designation = "Lead", Salary = 15000 });

                foreach (var emp in employees)
                {
                    db.Employees.Add(emp);   
                }
                db.SaveChanges();

                Console.WriteLine("Enter the DeptName");
                var dept = Console.ReadLine();
                //The string interpolation
                var res = db.Employees.FromSql($"Select * from Employees where DeptName={dept}");
                foreach (var item in res)
                {
                    Console.WriteLine($"{item.EmpId} {item.EmpName} {item.DeptName} {item.Designation} {item.Salary}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }

        static void ManageDatabase()
        {

            using (var ctx = new PersonalInfoDBContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
            }
        }
    }
}

In the above code, string interpolation is defined in the SQL Query which is passed to the FromSql() method as shown in the following code:

var res = db.Employees.FromSql($"Select * from Employees where DeptName={dept}");

The dept variable is embedded in the SQL Query using {dept} syntax.

Run the application, the following result will be displayed:

sting-interpolation

DbContext Pooling

Typically, in ASP.NET Core applications, the EF Core DbContext type is registered into the dependency injection (DI) system. The instance is later obtained through the constructor parameter in the controller. This means that whenever the controller is requested, a new instance of the DbContext is created.

In EF Core 2.0, a new mechanism of registering DbContext in dependency injection is introduced to create a pool of reusable DbContext instances.

This means that instead of creating a new instance of DbContext for each request, an instance is provided from the pool. This helps increase the performance of the application. The method AddDbContextPool() is introduced to register the custom DbContext type in the dependency injection.

The following code shows the syntax for DbContext pooling:

var provider = new ServiceCollection()
            .AddEntityFrameworkSqlServer()
            .AddDbContextPool(
                s => s.UseSqlServer(
                    @"Server=.;Database=PersonInfoDB;Trusted_Connection=True;"),
                8)
            .BuildServiceProvider();

The AddDbContextPool() method accepts a lambda expression for DbContextOptionBuilder defining the connection string and the second parameter is the integer containing value of the maximum number of instances in the DbContext pool.

The default value is set to 128.

One important point is once the request processing finalizes, any state on the instance is reset and the instance is itself returned to the pool.

Conclusion:

Entity Framework (EF) Core is a lightweight, cross-platform Object/Relational Mapping (O/RM) framework for .NET. EF Core now targets .NET Standard 2.0, which makes it compatible with .NET Core 2.0, .NET Framework 4.6.1, and other libraries that implement .NET Standard 2.0. All these new feature of EF Core 2.0 provide a great experience to developer while doing Data access programming.

This article was technically reviewed by Ricardo Peres.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!