Entity Framework 6 Code-First Tutorial

In this tutorial, using Entity Framework Code-First approach, we will create a small book information app which will:

1. Show a list of books
2. Show the details of selected book
3. Add new book
4. Delete Books
5. Update book information
6. Users should also be able to add review comments for any given book

The Models

Create the following POCO classes.

public class Book
{
    public int Id { get; set; }        
    public string Title { get; set; }
    public string ISBN { get; set; }
}

public class Review
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public string ReviewText { get; set; }
}

Install Entity Framework

Entity Framework is Microsoft's recommended data access technology for new applications. To install EntityFramework, run the following command in the Package Manager Console or use Nuget Package Manager and add a local package source. (No internet? Download the offline EF nuget packages here.)

PM> Install-Package EntityFramework

The above command will not work when using SharpDevelop. Use below instead.

PM> Install-Package EntityFramework.SharpDevelop

Config file

Make sure your App.config or Web.config has the following. Right below <configuration> above <startup>, add this

<configSections>
	<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
</configSections>

Don't add if the above entry already exists. Do not duplicate!

Right before </configuration>, add this

<entityFramework>
	<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
	    <parameters>
	        <parameter value="mssqllocaldb"/>
	    </parameters>
	</defaultConnectionFactory>
	<providers>
	    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
	</providers>
</entityFramework>

Don't add if the above entry already exists. Do not duplicate!

Setup Connection String

In Web.config or App.config, inside <configuration> right below </configSections> and/or </startup>, add this

<connectionStrings>
   <add name="BookDb" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=true;Initial Catalog=BookDb;" providerName="System.Data.SqlClient" />
   <add name="{name_of_dbconnection_string}" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=true;Initial Catalog={database_name};" providerName="System.Data.SqlClient" />
</connectionStrings>

Sign up for a free SQL web database in Somee.com or SmarterASP.com. Use the oldest available SQL Server version when using SharpDevelop.

The Context Class

DBContext object will be responsible for performing all the CRUD operations on these models.

using System.Data.Entity;

public class BookAppContext : DbContext
{
    public BookAppContext() : base("BookDb") // name_of_dbconnection_string
    {
    }

    // Map model classes to database tables
    public DbSet<Book> Books { get; set; }
    public DbSet<Review> Reviews { get; set; }
}

Enable Migrations

Delete Migration folder if it already exists.

PM> Enable-Migrations -EnableAutomaticMigrations
PM> Update-Database

In Configuration.cs, make sure AutomaticMigrationsEnabled is set to True.

Whenever there are changes in your Models, execute

PM> Update-Database

This allows data loss. (Force delete a column or table)

PM> Update-Database -Force

You can browse the database using SQL Server Management Studio (SSMS) or portable version of HeidiSQL or Database.NET. To enable (LocalDB)\MSSQLLocalDB, install SQL Server Express LocalDB.

Sample Book CRUD

A static class on doing Crud for Book.

public static class BookRepository
{
    private static BookAppContext _db = new BookAppContext();

    public static List<Book> GetAll()
    {
        var books = _db.Books.ToList();
        return books;
    }

    public static Book GetById(int Id)
    {
        var book = _db.Books.Find(Id);
        return book;
    }

    public static int Add(Book book)
    {
        _db.Books.Add(book);
        _db.SaveChanges();
        return book.Id;
    }

    public static Book Update(Book updatedBook)
    {
        var book = _db.Books.Find(updatedBook.Id);
        book.ISBN = updatedBook.ISBN;
        book.Title = updatedBook.Title;
        _db.Entry(book).State = System.Data.Entity.EntityState.Modified;
        _db.SaveChanges();
        return book;
    }

    public static bool Delete(int Id)
    {
        var book = _db.Books.Find(Id);
        if (book != null)
        {
            _db.Books.Remove(book);
            _db.SaveChanges();
            return true;
        }
        else
            return false;
    }
}

LINQ (Language Integrated Query)

Standard Query Operators in LINQ

That's it! 

Entity-Framework.pptx