Getting Started with Entity Framework Database First

This tutorial shows you how to get started with entity framework database first using c# code.

Entity framework is an ORM to help you easily access your sql database. It's developed by Microsoft and fully supports view, stored procedure in SQL Server and is easy to use. You can use EF with database first or code fisrt, but in this post i'm only show you how to use entity framework with database first.

To practice, you should create a new project and download the northwind database. You can view How to Download and Restore Northwind database to SQL Server

Right click on your project->Add->New Item

Enter your model name->Add

Select EF designer from database->Next

Click New Connection button to enter your sql connection

Click Test Connection to verify your sql connection, then click OK button

Select entity framework version->Next

Select the table you want to use, in this tutorial i only use the Customers table to play demo->Next

Click OK button to finish

Rebuild your project, then create a CustomerService class to access customer data from Customers table as shown below. As you can see, the Entity framework automatically generates a customer class corresponding to a Customers table in your database. You can find the Customer class in Model.edmx by clicking arrow button->Model.tt->Customer.cs

using System.Collections.Generic;
using System.Linq;

namespace WebAppDemo
{
    public class CustomerService
    {
        private NorthwindEntities db;

        public CustomerService()
        {
            db = new NorthwindEntities();
        }

        public List<Customer> GetAll()
        {
            return db.Customers.ToList();
        }
    }
}

To filter your data, you can use LINQ to query. The Entity Framework automatically parses LINQ to the SQL statement and executes it

public List<Customer> GetByCustomerId(string id)
{
    var sql = from c in db.Customers
              where c.CustomerID == id
              select c;
    return sql.ToList();
}

To insert, update, delete data you can use the following snippet

public bool Insert(Customer customer)
{
    db.Customers.Add(customer);
    int result = db.SaveChanges();
    return result != 0;
}

public bool Update(Customer customer)
{
    Customer obj = db.Customers.Find(customer.CustomerID);
    if (obj != null)
    {
        obj.Country = customer.Country;
        obj.ContactTitle = customer.ContactTitle;
        obj.ContactName = customer.ContactName;
        obj.CompanyName = customer.CompanyName;
        obj.City = customer.City;
        obj.Address = customer.Address;
        obj.Fax = customer.Fax;
        obj.Phone = customer.Phone;
        obj.PostalCode = customer.PostalCode;
        obj.Region = customer.Region;
    }
    int result = db.SaveChanges();
    return result != 0;
}

public bool Delete(string id)
{
    Customer obj = db.Customers.Find(id);
    if (obj != null)
        db.Customers.Remove(obj);
    int result = db.SaveChanges();
    return result != 0;
}

The SaveChanges method returns the total amount of data that has been changed, such as add, update, delete