Building an Entity Framework 7 Web Application App using Visual Studio 2013 – Part 1

Entity Framework 7 (EF7) development is in full stride (BETA6 right now) and one nice thing is the packages provide support for .NET Framework 4.5. If you take a look at the EntityFramework.Commands 7.0.0 package for instance you’ll see a net45 folder in the lib folder.
libraiessupported

The same is true for EntityFramework.SqlServer 7.0.0 which is a provider package for EF7. What this means is you can easily add EF7 to a MVC 5 or Web API 2 app in Visual Studio 2013. So if it’s that easy let’s do it. I’m on Windows 8.1, using Visual Studio 2013 Update 4 and have SQL Server 2014 SP1 installed locally on my machine. I’ll be using Web API 2 as the web application framework.

Some tools you might find useful are Fiddler, SQL Server Management Studio and Firefox with JsonView Plugin.

usingsqlexpress
Using Visual Studio 2013 create a C# ASP.NET Web Application called SightingsWebApp selecting the .NET Framework 4.5. Make sure .NET Framework 4.5 is selected.
Select the Empty template and check the box for Web API folders and references.

createWebAPIProject

 

Lets go ahead and update all nuget packages while we are at it. This will give us version 5.2.3 of the Web API package.

updateallnuget
Select the menu TOOLS -> NuGet Package Manager -> Package Manager Settings.
Make sure there is an entry for the nightly builds aspnetvnext package source.
nightlybuildssetup

 

Important: you need to make sure you have the most current version of Nuget extension for VS2013.

latestverofnuget

 

Now we need to add the packages for EF7. Open the Package Manager Console in VS2013. You should have the Nightly Builds (or AspNetVNext) selected as source. Enter the following commands:
Install-Package -Prerelease EntityFramework.SqlServer
Install-Package -Prerelease EntityFramework.Commands

Adding this package will also bring in quite a few dependency packages as well from the aspnetvnext nightly builds as seen in the packages.config file. Take a look at all the nuget packages added there.
n_installedpackages
Go ahead and build your project now. Make sure there are no errors.

By right clicking on the Project add two top-level folders named Migrations and DAL. I already have an empty Models folder from the project template.

projectstructure

The Migrations folder will hold all of the code generated by EF7 while the DAL folder will hold objects we use to connect to the data store (SQL Server in this case).
Add a class named ZooRepositories to the DAL folder then replace the content of the file with this.

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

namespace SightingsWebApp.DAL
{

     public class Animal
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public float Weight { get; set; }

        public AnimalGroup Group { get; set; }

        // Navigation
        public virtual Zoo Zoo { get; set; }

    }

public enum AnimalGroup
    {
        Invertebrate,
        Fish,
        Amphibian,
        Reptile,
        Mammal,
        Bird
    }

 public class AnimalRepository : ZooSightingDemoDb, IAnimalRepository
    {

        public Animal GetAnimal(int id)
        {
            return this.Animals.Include(a => a.Zoo)
                .Single(a => a.Id == id);
        }

        public IQueryable GetAnimalsForZoo(int zooId)
        {
            IQueryable entities = this.Animals
                .OrderBy(a => a.Name)
                .Include(a => a.Zoo)
                .Where(a => a.Zoo.Id == zooId);

            return entities;
        }

        public Animal Add(Animal entity)
        {
            var item = this.Animals.Add(entity);
            this.SaveChanges();
            return item;
        }

        public void Remove(Animal entity)
        {
            this.Animals.Remove(entity);
            this.SaveChanges();
        }

        public async void Remove(int id)
        {
            var entity = (from z in this.Animals
                          where z.Id == id
                          select z).Single();
            this.Animals.Remove(entity);
            await this.SaveChangesAsync();
        }

        public async Task Update(Animal entity)
        {
            this.Animals.AddOrUpdate(entity);
            await this.SaveChangesAsync();
            return entity;
        }

        public IQueryable Get()
        {
            return this.Animals;
        }

        public Animal Get(int id)
        {
            Animal result = this.Animals
                .Find(id);
            return result;
        }
    }

 public interface IAnimalRepository : IBaseRepository
    {
        IQueryable GetAnimalsForZoo(int zooId);
        Animal GetAnimal(int id);
    }

public interface IBaseRepository
    {
        T Add(T entity);
        void Remove(T entity);
        void Remove(int id);
        Task Update(T entity);
        IQueryable Get();
        T Get(int id);
    }

 public interface IDailyLogsRepository : IBaseRepository
    {

    }

public interface IZooRepository : IBaseRepository
    {
        IQueryable GetAllZoos();
        IQueryable GetAllZoosWithAnimals();

    }

 public class SightingDailyLog
    {
        public int Id { get; set; }

        public string Sighter { get; set; }
        public DateTime SightingDate { get; set; }

        // Navigation
        public virtual ICollection Sightings { get; set; }
    }


public class Zoo
    {
        public Zoo()
        {
            Animals = new HashSet();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public string City { get; set; }

        // Navigation
        public virtual ICollection Animals { get; set; }
    }

public class ZooAnimalSighting
    {
        public int Id { get; set; }

        public string Sighter { get; set; }
        public DateTime SightingDate { get; set; }
        public bool? IsSightingReliable { get; set; }

        // Navigation
        public virtual Animal Animal { get; set; }
    }
public class ZooRepository : ZooSightingDemoDb, IZooRepository
    {
        public IQueryable GetAllZoos()
        {
            return this.Zoos;
        }

        public IQueryable GetAllZoosWithAnimals()
        {
            return this.Zoos
                .Include(z => z.Animals);
        }

        public Zoo Add(Zoo entity)
        {
            var item = this.Zoos.Add(entity);
            this.SaveChanges();
            return item;
        }

        public void Remove(Zoo entity)
        {
            this.Zoos.Remove(entity);
            this.SaveChanges();
        }

        public void Remove(int id)
        {
            var entity = (from z in this.Zoos
                          where z.Id == id
                          select z).Single();
            this.Zoos.Remove(entity);
            this.SaveChanges();
        }

        public async Task Update(Zoo entity)
        {
            this.Zoos.AddOrUpdate(entity);
            await this.SaveChangesAsync();
            return entity;
        }

        public IQueryable Get()
        {
            throw new NotImplementedException();
        }

        public Zoo Get(int id)
        {
            return this.Zoos
                .Include(z => z.Animals)
                .Single(z => z.Id == id);
        }
    }


}

Notice we have navigation properties that allow use to navigate to related entities.

Now create a class in the DAL folder called ZooSightingDemoDb and replace file content with this.

using Microsoft.Data.Entity;
using SightingsWebApp.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace SightingsWebAppDemo.DAL
{
    public class ZooSightingDemoDb : DbContext
    {
        public DbSet Zoos { get; set; }
        public DbSet Animals { get; set; }
        public DbSet Sightings { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {

            builder.UseSqlServer(@"Server=Glenn-PC;Database=SightingsWebAppDemo2;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {


            modelBuilder.Entity(e =>
            {
                e.Property(z => z.Id)
                    .ForSqlServer()
                    .UseIdentity();
                e.Collection(a => a.Animals);

            });

            modelBuilder.Entity().Property(a => a.Id)
                .ForSqlServer()
                .UseIdentity();

            modelBuilder.Entity().Property(s => s.Id)
                .ForSqlServer()
                .UseIdentity();

        }
    }
}

We used the OnModelCreating method to add Code First Fluent API to specify some relationships.

Note I used Server=ServerName\Instance in the connection string as I have SQL Server Express running on this machine. Glenn-PC is my server name and SQLEXPRESS is the instance name of SQL Server.

Let’s bring up the Package Manager Console and put EF7 to work for us.
Enter the following command
Add-Migration  -Name  InitialSchemaCreate  -Context ZooSightingDemoDb  -Verbose  -Project  SightingsWebApp

You should see results like this.
addmigrationoutput

 

Let’s apply the migration by again using Package Manager Console and typing
Apply-Migration -Context ZooSightingDemoDb -Verbose -Project SightingsWebApp
You should see something like this output.
applymigrationoutput
If I now go and look at my SQL Server Express instance I see the database and tables that were just created.
dbcreated
EF7 adds data to the __MigrationHistory table but we need to add some seed data for our app to the Zoo and Animal table.
You should be able to use SQL Server data tools in Visual Studio or install Sql Server Management Studio to access the database and run the scripts. In my version of VS 2013 I’m able to connect by using the menu TOOLS -> Connect to Database
dbconnect
Now I can right click on my database in Server Explorer and Add Query.
serverexplorer
Here is the script to add the seed data.

USE [ZooSightingDemo]
GO

INSERT [dbo].[Zoo] ([City], [Name]) VALUES (N'Seattle', N'Seattle Zoo')
GO
INSERT [dbo].[Zoo] ([City], [Name]) VALUES (N'29 Palms', N'The Desert Zoo')
GO

INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (1, N'Salamander', 2.5, 1)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (1, N'Turtle, Musk', 6.5, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Whale', 1000, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Blackfish', 3, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Glassfish', 3, 1)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Catfish', 7, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Carp', 6, 1)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Trout', 3, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Herring', 2, 1)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Lungfish', 5, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Mackerel', 3, 1)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Cod', 4, 2)
GO
INSERT [dbo].[Animal] ([Group], [Name], [Weight], [ZooId]) VALUES (2, N'Prowfish', 3, 1)
GO

Ok, get that script run, in part 2 we will continue the EF7 story.