Table Splitting Select Data Using Code First Approach

Introduction

This article shows how to do table splitting and select data operation using Code First Approach.

SQL Server table structure

table structure

Create a console application as in the following:

create new prject 

Employee.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4. using System.ComponentModel.DataAnnotations.Schema;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8.   
  9. namespace Table_Splitting_SelectData_CFA  
  10. {  
  11.     public class Employee  
  12.     {  
  13.         public Employee()  
  14.         {  
  15.         }  
  16.   
  17.         [Key]  
  18.         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
  19.         public int EmpId { getset; }  
  20.         public string FirstName { getset; }  
  21.         public string LastName { getset; }  
  22.           
  23.         //Navigation Properties  
  24.         public EmployeeDetails EmployeeDetails { getset; }  
  25.     }  
  26. }  

Employeedetails.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Table_Splitting_SelectData_CFA  
  8. {  
  9.     public class EmployeeDetails  
  10.     {  
  11.         public EmployeeDetails()  
  12.         {  
  13.         }  
  14.   
  15.         public int EmpId { getset; }  
  16.         public string Phone { getset; }  
  17.         public string Email { getset; }  
  18.   
  19.         //Navigation Properties  
  20.         public Employee Employee { getset; }  
  21.     }  
  22. }  

Employeecontext.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.Entity;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace Table_Splitting_SelectData_CFA  
  9. {  
  10.     public class EmployeeContext : DbContext  
  11.     {  
  12.         public EmployeeContext()  
  13.             : base("EmployeeConn")  
  14.         {  
  15.             Database.SetInitializer<EmployeeContext>(new CreateDatabaseIfNotExists<EmployeeContext>());  
  16.         }  
  17.   
  18.         public DbSet<Employee> Employees { getset; }  
  19.   
  20.         protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  21.         {  
  22.             modelBuilder.Entity<Employee>()  
  23.            .HasKey(pk => pk.EmpId)  
  24.            .ToTable("Employees");  
  25.   
  26.             modelBuilder.Entity<EmployeeDetails>()  
  27.                 .HasKey(pk => pk.EmpId)  
  28.                 .ToTable("Employees");  
  29.   
  30.             modelBuilder.Entity<Employee>()  
  31.                 .HasRequired(p => p.EmployeeDetails)  
  32.                 .WithRequiredPrincipal(c => c.Employee);  
  33.   
  34.             base.OnModelCreating(modelBuilder);  
  35.         }  
  36.     }  
  37. }  

Web.config

  1. <connectionStrings>  
  2.     <add name="EmployeeConn"  
  3.     connectionString="Data Source=WIN-B4KJ8JI75VF;Initial Catalog=EmployeeDB;Integrated Security=true"  
  4.     providerName="System.Data.SqlClient"/>  
  5.   </connectionStrings>  

Program.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Table_Splitting_SelectData_CFA  
  8. {  
  9.     class Program  
  10.     {  
  11.         static void Main(string[] args)  
  12.         {  
  13.             EmployeeContext empContext = new EmployeeContext();  
  14.             var query = from r in empContext.Employees select r;  
  15.   
  16.             foreach (var r in query)  
  17.             {  
  18.                 Console.WriteLine("FirstName: " + r.FirstName + " LastName: " + r.LastName + " Phone: " + r.EmployeeDetails.Phone + " Email: " + r.EmployeeDetails.Email);  
  19.             }  
  20.             Console.ReadKey();  
  21.         }  
  22.     }  
  23. }  

Summary

In this article we saw how to do table splitting and select data operations using Code First Approach. Happy coding!