Fluent API in Entity Framework Core

Entity Framework Core Fluent API is used to build model based on entity classes. We can also override the default Conventions of Entity Framework Core using Fluent API when targetting the database schema as Fluent API has higher precedence than conventions and data annotations.

Entity Framework Core Fluent API offers the following features.

• Model Configuration: Configures an EF model to database mappings.
• Entity Configuration: Configures PrimaryKey, AlternateKey, Index, table name, one-to-one, one-to-many, many-to-many relationships etc.
• Property Configuration: Configures property to column mapping e.g. column name, default value, nullability, Foreignkey, data type, etc.

This tutorial is a part of Entity Framework Core series.

• 1. Introduction to Entity Framework Core
• 2. Installation of Entity Framework Core
• 3. Database-First approach in Entity Framework Core
• 4. DbContext Class in Entity Framework Core
• 5. Code-First Approach in Entity Framework Core
• 6. Migrations in Entity Framework Core
• 7. Insert Records in Entity Framework Core
• 8. Read Records in Entity Framework Core
• 9. Update Records in Entity Framework Core
• 10. Delete Records in Entity Framework Core
• 11. Conventions in Entity Framework Core
• 12. Configurations in Entity Framework Core
• 13. Fluent API in Entity Framework Core
• 14. Configure One-to-Many relationship using Fluent API in Entity Framework Core
• 15. Configure One-to-One relationship using Fluent API in Entity Framework Core
• 16. Configure Many-to-Many relationship using Fluent API in Entity Framework Core
• 17. Execute Raw SQL Queries using FromSqlRaw() method in Entity Framework Core
• 18. Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core

Entity Framework Core Fluent API Example

Suppose we have am entity class named Country as shown below.

public class Country
{
    public int PId { get; set; }
 
    public string Name { get; set; }
 
    public DateTime AddedOn { get; set; }
}

We write Entity Framework Core Fluent API Configurations inside the OnModelCreating() method of the Database Context (DbContext) of the app.

public class CountryContext : DbContext
{
    public CompanyContext(DbContextOptions<CompanyContext> options) : base(options)
    {
    }

    public DbSet<Country> Country { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //Write Fluent API configurations here
 
        //Entity Configuration
        modelBuilder.Entity<Country>().HasKey(s => s.PId);
 
        //Property Configurations
        modelBuilder.Entity<Country>(entity =>
        {
            entity.Property(e => e.Name)
                  .HasColumnName("CountryName")
                  .HasDefaultValue("USA")
                  .IsRequired();
 
            entity.Property(e => e.AddedOn)
                  .HasColumnType("date")
                  .HasDefaultValueSql("(getdate())");
        });
        modelBuilder.Entity<Country>().Ignore(e => e.population); 
    }
}

In the above code the Country entity is configured in the following manner:

• Line 14 : set it’s PId property as primary key using the HasKey() function.
• Line 19-22 : configured it’s Name property to be set as CountryName on the database table, and it should be a not null column with default value as USA. The IsRequired() method is used to make a column ‘not null’ in the database.
• Line 25, 26 : The AddedOn property is configured to be of date data type with default sql value given by getdate() method.
• Line 28 : The Population will not be mapped to the database table i.e. its column will not be created. The Ignore() method has done this configuration here.

On doing the Entity Framework Core Migrations the Country table will be created on the database which is shown in the below image.

Mapping Database Views

With Fluent APIs database Views can be mapped to entities. We have to first create a view on the database manually and then with EF Core we need to map it with an entity. For example a below given view shows data from 2 tables – Employee and Department. This is done by applying Inner Join between the 2 tables.

CREATE VIEW EmpDepView AS
SELECT e.Name, e.Designation, d.Name as "DName" FROM Employee e Inner Join Department d
On e.DepartmentId=d.Id

Next, in our app we create an Entity class as shown below.

[NotMapped]
[Keyless]
public class EmpDep
{
    public string Name { get; set; }
    public string Designation { get; set; }
    public string DName { get; set; }
}

We don’t want any database table to be mapped to this entity so we applied [NotMapped] attribute. The [Keyless] attribute is applied because the View doesn’t contain a key value.

Next, on the DbContext we define this entity as given below.

public DbSet<EmpDep> EmpDep { get; set; }

Then finally inside the OnModelCreating() method we have to map the entity class with the view. We do this through the ToView() method as shown below.

modelBuilder.Entity<EmpDep>().ToView("EmpDepView");

We can now read the View’s data on the controller as:

public class HomeController : Controller
{
    private CompanyContext context;
    public HomeController(CompanyContext cc)
    {
        context = cc;
    }

    public IActionResult EmpDepView()
    {
        var empDep = context.EmpDep;

        return View();
    }
}

Mapping Table-valued function

It is also possible to map an entity type to a table-valued function (TVF) instead of a table in the database. First create the following table-valued function in the database. The function gives departments that have more than one employees.

CREATE FUNCTION DepartmentWithMultipleEmployee()
RETURNS TABLE
AS
RETURN
(
    SELECT d.Name, COUNT(e.Id) AS Count
    FROM Department AS d
    JOIN Employee AS e ON d.Id = e.DepartmentId
    GROUP BY d.Id, d.Name
    HAVING COUNT(e.Id) > 1
)

In our app we add the following class.

[NotMapped]
public class DepartmentWithMultipleEmployee
{
    public string Name { get; set; }
    public int Count { get; set; }
}

Next on the DbContext we define the entity.

public DbSet<DepartmentWithMultipleEmployee> DepartmentWithMultipleEmployee { get; set; }

Then with ToFunction() we map this entity class with the table-valued function (TVF). The code is given below.

modelBuilder.Entity<DepartmentWithMultipleEmployee>().HasNoKey().ToFunction("DepartmentWithMultipleEmployee");

Note that in the example, the entity is keyless, but it doesn’t have to be.

Generated Values

Entity Framework Core allows to generate columns values either in default manner or in computed values. For example in the below code we have applied default value as 2 for the “Rating” column of the City table. This means whenever a new row is inserted to the City table and we do not supply value for the “Rating” column then value of 2, which is the default value, for the Rating column will be inserted.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<City>()
        .Property(b => b.Rating)
        .HasDefaultValue(2);
}

In the below example we set the default value for the “Created” column to be generated with the SQL getdate() method. This return the current database system date and time.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<City>()
        .Property(b => b.Created)
        .HasDefaultValueSql("getdate()");
}

EF Core can also computes column values in the database. In the below example the value of DisplayName column is computed every time it is fetched from the database. Note that this will not store the value on the Column in the database.

modelBuilder.Entity<Employee>()
    .Property(p => p.DisplayName)
    .HasComputedColumnSql("[LastName] + ', ' + [FirstName]");

We can also let the computes column values to be stored on the table’s Column by using stored: true. Check below example:

modelBuilder.Entity<Employee>()
    .Property(p => p.DisplayName)
    .HasComputedColumnSql("[LastName] + ', ' + [FirstName]", stored: true);

Check Constraints

EF Core can also add contraints which are some conditions that must hold for all rows in a table. Attempts to insert or modify data that violates the constraints will fail.

In the below code we added a constraint Check_Salary that ensures that the Salary column in the database is always more than Tax column for every row of the table.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Employee>()
        .ToTable(b => b.HasCheckConstraint("Check_Salary", "[Salary] > [Tax]"));
}

Fluent API Methods

In the below table we have listed down some of the most common methods of Entity Framework Core Fluent API.

“Has/With” Pattern for Configuring Relationships

The Has/With pattern is widely used when configuring relationships with the Fluent API. The Has & With methods must be combined to configure a valid relationship.

There are 2 variants of Has method, these are:

• 1. HasOne
• 2. HasMany

Similarly, there are 2 variants of With method, these are:

• 1. WithOne
• 2. WithMany

The HasOne method along with the WithOne method is used to create Reference Navigation Properties. Similarly, the HasMany method along with the WithMany method is used to create Collection Navigation Properties.

You will learn the usage of these methods in the below 3 tutorials.

• 1. Configure One-to-Many relationship using Fluent API in Entity Framework Core
• 2. Configure One-to-One relationship using Fluent API in Entity Framework Core
• 3. Configure Many-to-Many relationship using Fluent API in Entity Framework Core