Introduction

In this blog we will see how to access sql server database with entity framework code first approach and later we will also look at how we can perform select data operation using complex type.

Step 1: Create asp.net web application

Employee.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  6. namespace ComplexType_CFA_SelectApp
  7. {
  8. public class Employee
  9. {
  10. public Employee()
  11. {
  12. }
  14. public int Id { get; set; }
  15. public string FirstName { get; set; }
  16. public string LastName { get; set; }
  17. public EmployeeDetails EmployeeDetails { get; set; }
  18. }
  19. }

EmployeeDetails.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel.DataAnnotations.Schema;
  4. using System.Linq;
  5. using System.Web;
  7. namespace ComplexType_CFA_SelectApp
  8. {
  9. [ComplexType]
  10. public class EmployeeDetails
  11. {
  12. public EmployeeDetails()
  13. {
  14. }
  16. public int Phone { get; set; }
  17. public int Age { get; set; }
  18. public string Email { get; set; }
  19. }
  20. }

Employeecontext.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.Entity;
  4. using System.Linq;
  5. using System.Web;
  7. namespace ComplexType_CFA_SelectApp
  8. {
  9. public class EmployeeContext: DbContext
  10. {
  11. public EmployeeContext()
  12. : base("EmployeeConn")
  13. {
  14. Database.SetInitializer<EmployeeContext>(new CreateDatabaseIfNotExists<EmployeeContext>());
  15. }
  17. protected override void OnModelCreating(DbModelBuilder modelBuilder)
  18. {
  19. //Set primary key to Employee table
  20. modelBuilder.Entity<Employee>().HasKey(m => m.Id).Property(m => m.Id).IsRequired();
  22. //First Name is required and Max Length is 50
  23. modelBuilder.Entity<Employee>().Property(p => p.FirstName).IsRequired().HasMaxLength(50);
  25. //Last Name is required and Max Length is 50
  26. modelBuilder.Entity<Employee>().Property(p => p.LastName).IsRequired().HasMaxLength(50);
  28. //Age is required
  29. modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Age).IsRequired();
  31. //Phone is required
  32. modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Phone).IsRequired();
  34. //Email is required
  35. modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Email).IsRequired();
  36. }
  38. public DbSet<Employee> Employees { get; set; }
  39. }
  40. }

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>

Webform1.aspx

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ComplexType_CFA_SelectApp.WebForm1" %>
  3. <!DOCTYPE html>
  5. <html xmlns="http://www.w3.org/1999/xhtml">
  6. <head runat="server">
  7. <title></title>
  8. </head>
  9. <body>
  10. <form id="form1" runat="server">
  11. <div>
  12. <asp:GridView ID="GridView1" runat="server"></asp:GridView>
  13. </div>
  14. </form>
  15. </body>
  16. </html>

Webform1.aspx.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  8. namespace ComplexType_CFA_SelectApp
  9. {
  10. public partial class WebForm1 : System.Web.UI.Page
  11. {
  12. EmployeeContext entities = new EmployeeContext();
  13. protected void Page_Load(object sender, EventArgs e)
  14. {
  15. var data = from x in entities.Employees
  16. select new
  17. {
  18. EmpId = x.Id,
  19. Firstname = x.FirstName,
  20. Lastname = x.LastName,
  21. Age = x.EmployeeDetails.Age,
  22. Email = x.EmployeeDetails.Email,
  23. Phone= x.EmployeeDetails.Phone
  24. };
  25. GridView1.DataSource = data.ToList();
  26. GridView1.DataBind();
  27. }
  28. }
  29. }

Output of the application looks like this

Summary

In this blog we have seen how we can access sql server database with entity framework code first approach and how to perform select data operation using complex type. Happy coding!