Conditional Mapping in entity framework with code first

I Explained before that Conditional mapping in Entity Framework means When we want only a portion of data From database or table according to a condition then for fulfil this situation we use Conditional Mapping in EntityFramework.

Here we will learn Conditional mapping in Entity Framework with code first Approach.

Step 1: Create a new web application And install Entity framework..

Step 2: Add a class to the project. Give the Name as Student.cs. Write Below code.

namespace EFTableSplitting {     public class Student     {         public int StudentID { get; set; }         public string FirstName { get; set; }         public string LastName { get; set; }         public string Gender { get; set; }           public bool IsDiscard s{ get; set; }     } }

Step 3: Add a class to the project For the DBContext and give the name  it asStudentDBContext.cs. And Write below code.

using System.Data.Entity; namespace EFTableSplitting {     public class StudentDBContext : DbContext     {         public DbSet<Student> Students { get; set; }         protected override void OnModelCreating(DbModelBuilder _Modal)         {            _Modal.Entity<Student>()                 .Map(c => c.Requires("IsDiscard")                 .HasValue(false))                 .Ignore(c => c.IsDiscard);             base.OnModelCreating(_Modal);         }     } }

Step 4: Add the database connection string in web.config file.

<connectionStrings>   <add name="StudentDBContext"       connectionString="server=.; database=entitysplitting; integrated security=SSPI;"       providerName="System.Data.SqlClient" /> </connectionStrings>

Step 5: Add a webform to the project. Go to Tool Box and Drag and drop a GridView control. 

<asp:GridView ID="GridView1" runat="server">     </asp:GridView> s

Step 6: Write below code at page load event.

protected void Page_Load(object sender, EventArgs e) {     StudentDBContext studentDBContext = new StudentsDBContext();     GridView1.DataSource = studentDBContext.Students.ToList();     GridView1.DataBind(); }

Step 7: If you already have entitysplitting database in SQL Server. Delete it first

And then Run the application.

Step 8: At this point, run the application. entitysplitting database and Student table should be created by the entity framework.

Step 9: Insert data using the following SQL script

Insert into Students values ('Munesh', 'Sharma', Male',m@g.com,5555555555) Insert into Students values ('Rahul', 'Sharma', 'Male',R@g.com,333333333) Insert into Students values ('Sara', 'vilium', 'Female',S@g.com,111111111) Insert into Students values ('Mark', 'hash', 'Female',M1@g.com,2222222222) Insert into Students values ('ABC', 'EFG', 'Male',A@g.com,6666666666)

Conditional Mapping in EntityFramework

Conditional mapping in Entity Framework means When we want only a portion of data From database or table according to a condition then for fulfil this situation we use Conditional Mapping in EntityFramework.

What is Conditional Mapping

Conditional mapping is a condition that Allow us to filter the result  that comes from the database for a entity. It Also enforces that an entity is mapped to data in the database With a particular conditions which are given in the conditional mapping. For using conditional mapping in Entity, we have to Right click on entity and click on Table mapping. There we can <Add a Condition>: 

As Shown below

When we add a condition, that condition will be added to query that we will change to the database.

Available Conditional Mappings Operators

In Conditional mapping there are two type of operators. First one is “equality (=)” and other one “ Is” operator. The equality operator (=) have values of  integers or strings . The Is operator have Null or Not Null. 

Conditional Mapping Example

Step(1)

In Conditional mapping example we will use Students table with a extra column with the name “ IsDiscard”  Throw this we will see that particular student is Discard or not.

Create table Students (      StudentID int primary key identity,      FirstName nvarchar(50),      LastName nvarchar(50),      Gender nvarchar(50),      IsDiscard bit not null ) Insert into Students values ('Munesh', 'Sharma', Male',0) Insert into Students values ('Rahul', 'Sharma', 'Male',0) Insert into Students values ('Sara', 'vilium', 'Female',1) Insert into Students values ('Mark', 'hash', 'Female',1) Insert into Students values ('ABC', 'EFG', 'Male',0)

Step(2)  Now Go to your application and add a “Ado.NetDataModal “ then select Modal Fiirst approach then Give the connection and select your table from database. After click on OK your entity will show with “IsDiscard” column.

Step(3)  To Use Conditional Mapping

(A)                Right click on Entity and click  on Table Mapping a table a screen will come there you give the condition When IsDiscard = False

(B)                At this time if we try to build this solution it gives compilation error because it cannot map a column more then once.

(C)                For solving this problem we have to delete “IsDiscard” column from entity ,Because we have used this column on Conditional mapping

Add a Web form to the project and dragdown a gidview after that bind this gridview. For binding this gridview write this below code.

protected void Page_Load(object sender, EventArgs e) {     StudentDBContext _student = new StudentDBContext();     GridView1.DataSource = _student.Students;     GridView1.DataBind(); }

Table splitting in entity framework with code first approach

 Table splitting we split one table into two tables.

Step 1: Create a new web application And install Entity framework.

Step 2: Add a class to the project. Give the Name as Student.cs. Write Below code.

namespace EFTableSplitting {     public class Student     {         public int StudentID { get; set; }         public string FirstName { get; set; }         public string LastName { get; set; }         public string Gender { get; set; }         public StudentContactDetail StudentContactDetail { get; set; }     } }

Step 3: Add a another class to the project And give the  Name as StudentContactDetail.cs. And write the below code.

namespace EFTableSplitting {     public class StudentContactDetail     {         public int StudentID { get; set; }         public string Email { get; set; }         public string Mobile { get; set; }                  public Student Student { get; set; }     } }

Step 4: Add a class to the project For the DBContext and give the name  it as StudentDBContext.cs. And Write below code.

using System.Data.Entity; namespace EFTableSplittings {     public class StudentDBContext : DbContext     {         public DbSet<Student> Student { get; set; }         protected override void OnModelCreating(DbModelBuilder _Modal)         {            _Modal.Entity<Student>()                 .HasKey(c => c.StudentID)                 .ToTable("Student");            _Modal.Entity<StudentContactDetail>()                 .HasKey(c => c.StudentID)                 .ToTable("Student");            _Modal.Entity<Student>()                 .HasRequired(c => c.StudentContactDetail)                 .WithRequiredPrincipal(c => c.Student);         }     } }

Step 5: Add the database connection string in web.config file.

<connectionStrings>   <add name="StudentDBContext"       connectionString="server=.; database=entitysplitting; integrated security=SSPI;"       providerName="System.Data.SqlClient" /> </connectionStrings>

Step 6: Now Add a web form And pest below code in HTML code portion.

<asp:Button ID="FullDataWithContactDetail" runat="server" Text="GetStudent Data with contact detail"         onclick="Button1_Click" />     <br />     <asp:Button ID="Button2" runat="server" Text="Get Student Data"         onclick="Button2_Click" />     <asp:GridView ID="GridView1" runat="server">     </asp:GridView> </div> </div>

Step 7: Write the below code in code behind section

public partial class WebForm1 : System.Web.UI.Page {     private DataTable GetStudentData()     {         StudentDBContext studentDBContext = new StudentDBContext();         List<Student> students = studentDBContext.Student.ToList();         DataTable dt = new DataTable();         DataColumn[] columns = { new DataColumn("StudentID"),                                  new DataColumn("FirstName"),                                  new DataColumn("LastName"),                                  new DataColumn("Gender")};         dt.Columns.AddRange(columns);         foreach (Student student in students)         {             DataRow dr = dt.NewRow();             dr["StudentID"] = student.StudentID;             dr["FirstName"] = student.FirstName;             dr["LastName"] = student.LastName;             dr["Gender"] = student.Gender;             dt.Rows.Add(dr);         }         return dt;     }     private DataTable FullDataWithContactDetails()     {         StudentDBContext studentDBContext = new StudentDBContext();         List<Student> students = studentDBContext.Student             .Include("StudentContactDetail").ToList();         DataTable dt = new DataTable();         DataColumn[] columns = { new DataColumn("StudentID"),                                  new DataColumn("FirstName"),                                  new DataColumn("LastName"),                                  new DataColumn("Gender"),                                  new DataColumn("Email"),                                  new DataColumn("Mobile"),                                           dt.Columns.AddRange(columns);         foreach (Student student in students)         {             DataRow dr = dt.NewRow();             dr["EmployeeID"] = student.StudentID;             dr["FirstName"] = student.FirstName;             dr["LastName"] = student.LastName;             dr["Gender"] = student.Gender;             dr["Email"] = student.StudentContactDetail.Email;             dr["Mobile"] = student.StudentContactDetail.Mobile;             dt.Rows.Add(dr);         }         return dt;     }     protected void Button1_Click(object sender, EventArgs e)     {                      GridView1.DataSource = FullDataWithContactDetails();                 GridView1.DataBind();     }   protected void Button2_Click(object sender, EventArgs e)     {         GetStudentData();      }  }

At this point, run the application. entitysplitting database and Student table should be created by the entity framework.

Step 8: Insert test data using the following SQL script

Insert into Students values ('Munesh', 'Sharma', Male',m@g.com,5555555555) Insert into Students values ('Rahul', 'Sharma', 'Male',R@g.com,333333333) Insert into Students values ('Sara', 'vilium', 'Female',S@g.com,111111111) Insert into Students values ('Mark', 'hash', 'Female',M1@g.com,2222222222) Insert into Students values ('ABC', 'EFG', 'Male',A@g.com,6666666666)

Step 9:  Run your application and check your data that it is working properly or not.