Book App

with Entity Framework 6

The application that we will be creating is a small books information app which will:

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

The Models

    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     

Config file

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

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

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

Right before </configuration>, add this

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

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

Setup Connection String

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

       <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" />

Sign up for a free SQL web database in or

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; }

(Optional) Using JET/Microsoft Access EF Provider

If using Jet or Microsoft Access database, download this blank Access 2002/2003 MDB file and paste it in the App root folder. Then install this nuget package:

    PM> Install-Package JetEntityFrameworkProvider     

And add this to the <connectionStrings>

    <add name="DbConnJetAccess" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\access_database_filename.mdb" providerName="JetEntityFrameworkProvider" />     

And inside <entityFramework><providers> add this:

    <provider invariantName="JetEntityFrameworkProvider" type="JetEntityFrameworkProvider.JetProviderServices, JetEntityFrameworkProvider" />

And right after </entityFramework> add this:

            <remove invariant="JetEntityFrameworkProvider" />
            <add invariant="JetEntityFrameworkProvider" name="Jet Entity Framework Provider" description="Jet Entity Framework Provider" type="JetEntityFrameworkProvider.JetProviderFactory, JetEntityFrameworkProvider" />

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

Update the connection string name in the DbContext class

    public BookAppContext() : base("DbConnJetAccess") // name of db connection string

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 

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)
            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;
            return book;

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

LINQ (Language Integrated Query)

Standard Query Operators in LINQ

Classification Standard Query Operators
Filtering Where, OfType
Sorting OrderBy, OrderByDescending, ThenBy, ThenByDescending, Reverse
Grouping GroupBy, ToLookup
Join GroupJoin, Join
Projection Select, SelectMany
Aggregation Aggregate, Average, Count, LongCount, Max, Min, Sum
Quantifiers All, Any, Contains
Elements ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault
Set Distinct, Except, Intersect, Union
Partitioning Skip, SkipWhile, Take, TakeWhile
Concatenation Concat
Equality SequenceEqual
Generation DefaultEmpty, Empty, Range, Repeat
Conversion AsEnumerable, AsQueryable, Cast, ToArray, ToDictionary, ToList