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;
  9. namespace Table_Splitting_SelectData_CFA
  10. {
  11. public class Employee
  12. {
  13. public Employee()
  14. {
  15. }
  17. [Key]
  18. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  19. public int EmpId { get; set; }
  20. public string FirstName { get; set; }
  21. public string LastName { get; set; }
  23. //Navigation Properties
  24. public EmployeeDetails EmployeeDetails { get; set; }
  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;
  7. namespace Table_Splitting_SelectData_CFA
  8. {
  9. public class EmployeeDetails
  10. {
  11. public EmployeeDetails()
  12. {
  13. }
  15. public int EmpId { get; set; }
  16. public string Phone { get; set; }
  17. public string Email { get; set; }
  19. //Navigation Properties
  20. public Employee Employee { get; set; }
  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;
  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. }
  18. public DbSet<Employee> Employees { get; set; }
  20. protected override void OnModelCreating(DbModelBuilder modelBuilder)
  21. {
  22. modelBuilder.Entity<Employee>()
  23. .HasKey(pk => pk.EmpId)
  24. .ToTable("Employees");
  26. modelBuilder.Entity<EmployeeDetails>()
  27. .HasKey(pk => pk.EmpId)
  28. .ToTable("Employees");
  30. modelBuilder.Entity<Employee>()
  31. .HasRequired(p => p.EmployeeDetails)
  32. .WithRequiredPrincipal(c => c.Employee);
  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;
  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;
  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!