SQL Server and Entity mapping


This article will show you how the Entity and SQL Server database are mapped
This article will show you how the Entity and SQL Server database are mapped

Written by Do Minh Tuan


Posted at 24 Sep 2017, 03:42 PM



  1. Mapping Entity class and table in Database:

Each Entity class will map with one table in your database (DB).

Tables in DB are often named with plural-form. For example: Customers, Books, Orders...

Entity classes are often named with singular form. For example: Customer.cs, Book.cs,...

Entity Framework (EF) will automatically analyze and convert from class name from singular form to plural form and map with the corresponding table in DB.

Rules about converting classes name/tables name follow English-based standard.

 Incase Entity class is named with plural form, table in DB also be named with plural form, EF will map correctly.

In calse Entity class is named with plural or singular, but table in DB is named with singular form, it will NOT map.

 

Entity class name

Table name

Can map?

ChiTietHoaDon

ChiTietHoaDons

YES

ChiTietHoaDons

ChiTietHoaDons

YES

Category

Category

NO

Categories

Category

NO

Category

Categories

YES

Categories

Categories

YES

 

The yellow rows (Entity class is named with singular, table is named with plural form) is highly recommended. 

In case we use Data Annotations attribute [Table()] of System.ComponentModel.DataAnnotations.Schema to point out the table to which that class should map, EF will read this attribute and map without considering Entity class name or any rules above. 

Entity Class name

Table name

[Table()]

Can map?

Category

Category

Table[(“Category”)]

YES

Categories

Category

YES

dsfadaxzdada

Category

YES

ThePinkDuck

Category

YES

Whatever

Category

YES

But if in DB there is no table with the corresponding name declared in [Table()] properties, it WONT map, even the rules about plural and singular form above is meet. 

  1. Mapping table columns with Entity class propertise:

Properties of Entity class have to have the same name with columns they map in DB.

Properties of Entity class have to have the corresponding data-type with columns they map in DB.

About how to determine which data type in C# corresponse with which data type of SQL Server, see: https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

Nullable columns in DB also have to be declared with Nullable type in C# 

public Nullable Amount{ get; set; }

public int? Amount{ get; set; }

Like mapping tables, when mapping columns, we can use [Column()] to specify the column that propertise map with.

Primary key mapping:

There are 2 ways to map primary key:

  • Named the primary key in DB "ID" or "Id". Then in Entity class we only have to declare a propertise named "ID" or "Id"
  • In Entity class, use [Key] before propertise(s) that are primary key's column.

In case there are more than 1 column in primary key, use [Key] before we declare them, and specify [Column(Order=xxx)] is the order of column in your primary key. When use LINQ Find() method, pass the arguments to Find() with the corresponding order we declared.



Comments


Search