Entity Framework Entity Design and Object Relation

Designing entity in entity framework is one of the key essences we all enjoy as developer, in earlier version of entity framework, we had edmx file to generate entity from database tables directly, and here we learn how to design entity for code first approach in entity framework core.

So, here we learn how to design entity and set various type of attribute to map the entity with database objects, also learn how to setup relationship among business objects, and finally generate model.

In entity design, we will use many attribute for class and properties, most of them comes under following two namespace, so let’s first add them in your entity class

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

EF Core Entity Mapping with database objects

Here is an example of client class, which will be mapped with "tbClient" table in database. as you can see in code below we have used attribute [Table("tbClient")] to specify the name of database table, this also can be a sql view instead of sql table.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace WebTrainingRoom.BO
{
    [Table("tbClient")]
    public class Client
    {
        [Key]
        public long ClientId { get; set; }
        public string Firstname { get; set; } = "Not Set";
        public string Lastname { get; set; } = "Not Set";
        public string Address { get; set; }
        public string Mobile { get; set; }
        public string Email { get; set; }
        [NotMapped]
        public Lazy<List<OrderItem>> OrderItems { get; set; }
        [NotMapped]
        public Lazy<List<vwClientOrder>> OrderList { get; set; }
    }
}

The key attribute indicates that this will be the primary key in associated database table, but you need to make sure that data type is same or compatible with database table data type, for example here in entity we have defined clientId as long, indicates that data type in sql column is bigint.

[Key]
public long ClientId { get; set; }

Now if you have different column name in database table which is not matching with the property name you have defined, then you need to specify with column attribute like example below.

[Key]
[Column("cid")]
public long ClientId { get; set; }

Above code indicates that the property to be matched with database column name “cid”

If you have any property which is not available in database object, but present in your entity class, then the property must be marked with [NotMapped] attribute. here is an example from above client class.

[NotMapped]
public Lazy<List<vwClientOrder>> OrderList { get; set; }

The above property is an example of lazy list property, you may be interested to know how to perform lazy loading in entity framework core.

There are two steps to add an entity in DbContext file for model generation.

1. Creating a DbSet property
2. Adding the property to OnModelCreating method

Creating a DbSet property is simple, for each entity we need to create a property in DbContext File like example below.

public DbSet<Client> tbClient { get; set; }   
public DbSet<vwClientOrder> vwOrderItem { get; set; }

Now you need to override the OnModelCreating method, and keep adding each entity in model builder with specifying their primary key property HasKey method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     
modelBuilder.Entity<Client>().HasKey(s => s.ClientId);
   
modelBuilder.Entity<OrderItem>(ov =>
{
    ov.HasKey(v => v.OrderItemId);
    ov.ToTable("tbOrderItem");
    ov.Property(v => v.OrderItemId).HasColumnName("oItemId");
});
modelBuilder
    .Entity<vwClientOrder>(ov =>
    {
        ov.HasNoKey();
        ov.ToView("vwOrderItem");
        ov.Property(v => v.clientId).HasColumnName("clientId");
    });
base.OnModelCreating(modelBuilder);
}

Notice how to map an entity to sql view, where you don’t have any primary key, use HasNoKey() method and the specify the sql view name in ToView method, like i specified "vwOrderItem" in above code.

Sometimes you come across situation where the entity primary key name is different than property name specified, like ov.Property(v => v.OrderItemId).HasColumnName("oItemId");, in such scenario you can set the sql column name explicitly.

We can define one to many relation in entity framework using methods like WithMany, HasOne or HasMany and WithOne, we can define the relation either in child entity or in parent entity, both ways it works exactly same, take a look at following example.

public DbSet<Order> tbOrder { get; set; }
public DbSet<OrderItem> tbOrderItem { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<OrderItem>()
 .HasOne<Order>(o => o.Order)
 .WithMany(oi => oi.Orderlists);
}

// Or we could have written 

modelBuilder.Entity<Order>()
.HasMany<OrderItem>(oi => oi.Orderlists)
.WithOne(o => o.Order);

Entity framework supports three type of loading of child entities from dbContext object, eager loading, explicit loading and lazy loading

Eager loading means loading all related data at the time of initial query.

We can use Include method to specify that all related data to be included in query results, like in example below I am including all order items while loading the order object from database.

order = await context.tbOrder
	.Where(c => c.OrderId == orderId)
	.Include(o => o.Orderlists)
	.FirstOrDefaultAsync<Order>();

we also can use ThenInclude method to include multiple levels of related data from database.

Explicit loading means loading all related data later, not at the time of initial query.

We need to use DbContext.Entry(...) API, inside the Entry method we need to specify the collection property we want to load.

order = await context.tbOrder
	.Where(c => c.OrderId == orderId)
	.FirstOrDefaultAsync<Order>();
// Explicit loading [working fine]
context.Entry(order)
	.Collection(b => b.Orderlists)
	.Load();

You may be interested in following posts!

• Fluent API in Entity Framework Core Example
• Lazy Loading in Entity Framework Core