Post
Ask Question

Select Data from Complex Type using Code First Approach

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;  
  5.   
  6. namespace ComplexType_CFA_SelectApp  
  7. {  
  8.     public class Employee  
  9.     {  
  10.         public Employee()  
  11.         {  
  12.         }  
  13.   
  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;  
  6.   
  7. namespace ComplexType_CFA_SelectApp  
  8. {  
  9.     [ComplexType]  
  10.     public class EmployeeDetails  
  11.     {  
  12.         public EmployeeDetails()  
  13.         {  
  14.         }  
  15.   
  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;  
  6.   
  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.         }  
  16.   
  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();  
  21.   
  22.             //First Name is required and Max Length is 50  
  23.             modelBuilder.Entity<Employee>().Property(p => p.FirstName).IsRequired().HasMaxLength(50);  
  24.   
  25.             //Last Name is required and Max Length is 50  
  26.             modelBuilder.Entity<Employee>().Property(p => p.LastName).IsRequired().HasMaxLength(50);  
  27.   
  28.             //Age is required   
  29.             modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Age).IsRequired();  
  30.   
  31.             //Phone is required  
  32.             modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Phone).IsRequired();  
  33.   
  34.             //Email is required  
  35.             modelBuilder.ComplexType<EmployeeDetails>().Property(p => p.Email).IsRequired();  
  36.         }  
  37.   
  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" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  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;  
  7.   
  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!
Next Recommended Reading Select Query on DataTable for filtering Data Using LINQ

MVC Corporation
MVC Corporation is consulting and IT services based company.