TRUNGTQ

Think Big, Act Small, Fail Fast and Learn Rapidly

NAVIGATION - SEARCH

Using ASP.NET Core, Entity Framework Core and ASP.NET Boilerplate to Create NLayered Web Application (Part I)

A step by step guide to create a web application based on ASP.NET Core, Entity Framework Core and ASP.NET Boilerplate frameworks with automated tests.

Contents

  • Introduction
    • Prerequirements
  • Create the Application
  • Developing the Application
    • Creating a Task Entity
    • Adding Task to DbContext
    • Creating the First Database Migration
    • Creating the Database
    • Task Application Service
    • Testing the TaskAppService
    • Task List View
      • Adding a New Menu Item
      • Creating the TaskController and ViewModel
      • Task List Page
      • Localization
      • Filtering Tasks
    • Automated Testing Task List Page
  • More
  • Article History

Introduction

This is first part of the "Using ASP.NET Core, Entity Framework Core and ASP.NET Boilerplate to Create NLayered Web Application" article series. See other parts:

In this article, I'll show to create a simple layered web application using the following tools:

We will also use Log4Net and AutoMapper which are included in ABP startup template by default. We will use the following techniques:

The project will be developed here is a simple task management application where tasks can be assigned to people. Instead of developing the application layer by layer, I will go to vertical and change the layers as the application grows. While the application grows, I will introduce some features of ABP and other frameworks as needed.

Prerequirements

Following tools should be installed in your machine to be able to run/develop the sample application:

Create the Application

I used ABP's startup template (http://www.aspnetboilerplate.com/Templates) to create a new web application named "Acme.SimpleTaskApp" (company name, "Acme" here, is optional while creating templates):

ABP startup template creation

It creates a layered solution as shown below: 

Startup template projects

It includes 6 projects starting with the name that I entered as the project name:

  • .Core project is for domain/business layer (entities, domain services...)
  • .Application project is for application layer (DTOs, application services...)
  • .EntityFramework project is for EF Core integration (abstracts EF Core from other layers).
  • .Web project is for ASP.NET MVC layer.
  • .Tests project is for unit and integration tests (up to application layer, excluding web layer)
  • .Web.Tests project is for ASP.NET Core integrated tests (complete integration test for server side).

When you run the application, you can see the user interface of the template:

Template Home Page

It contains a top menu, empty Home and About pages and a language switch dropdown.

Developing the Application

Creating a Task Entity

I want to start with a simple Task entity. Since an entity is part of the domain layer, I added it into the .Coreproject:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Abp.Domain.Entities;
using Abp.Domain.Entities.Auditing;
using Abp.Timing;

namespace Acme.SimpleTaskApp.Tasks
{
    [Table("AppTasks")]
    public class Task : Entity, IHasCreationTime
    {
        public const int MaxTitleLength = 256;
        public const int MaxDescriptionLength = 64 * 1024; //64KB

        [Required]
        [MaxLength(MaxTitleLength)]
        public string Title { get; set; }

        [MaxLength(MaxDescriptionLength)]
        public string Description { get; set; }

        public DateTime CreationTime { get; set; }

        public TaskState State { get; set; }

        public Task()
        {
            CreationTime = Clock.Now;
            State = TaskState.Open;
        }

        public Task(string title, string description = null)
            : this()
        {
            Title = title;
            Description = description;
        }
    }

    public enum TaskState : byte
    {
        Open = 0,
        Completed = 1
    }
}


  • I derived from ABP's base Entity class, which includes Id property as int by default. We can use the generic version, Entity<TPrimaryKey>, to choice a different PK type.
  • IHasCreationTime is a simple interface just defines CreationTime property (it's good to use a standard name for CreationTime).
  • Task entity defines a required Title and an optional Description.
  • TaskState is a simple enum to define states of a Task.
  • Clock.Now returns DateTime.Now by default. But it provides an abstraction, so we can easily switch to DateTime.UtcNow in the feature if it's needed. Always use Clock.Now instead of DateTime.Now while working with ABP framework.
  • I wanted to store Task entities into AppTasks table in the database.

Adding Task to DbContext

.EntityFrameworkCore project includes a pre-defined DbContext. I should add a DbSet for the Task entity into the DbContext:

public class SimpleTaskAppDbContext : AbpDbContext
{
    public DbSet<Task> Tasks { get; set; }

    public SimpleTaskAppDbContext(DbContextOptions<SimpleTaskAppDbContext> options) 
        : base(options)
    {

    }
}


Now, EF Core knows that we have a Task entity. 

Creating the First Database Migration 

We will create an initial migration to create database and the AppTasks table. I open the Windows Command Line and run the following command (active directory should be the root directory of .EntityFrameworkCore project):

EF Core Initial Migration

This command creates a Migrations folder in the .EntityFrameworkCore project which includes a migration class and a snapshot of our database model:

EntityFrameworkCore Project Migrations 

Automatically generated "Initial" migration class is shown below:

public partial class Initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "AppTasks",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                CreationTime = table.Column<DateTime>(nullable: false),
                Description = table.Column<string>(maxLength: 65536, nullable: true),
                State = table.Column<byte>(nullable: false),
                Title = table.Column<string>(maxLength: 256, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AppTasks", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "AppTasks");
    }
}


This code is used to create AppTasks table when we execute the migrations to the database (see entity framework documentation for more information on migrations).

Creating the Database

To create the database, I run the following command in the command line:

dotnet ef database update

This command created a database named SimpleTaskAppDb in the local SQL Server and executed migrations (currently, there is a single, "Initial", migration):

Created Database

Now, I have a Task entity and corresponding table in the database.  I entered a few sample Tasks to the table:

AppTasks table

Note that, the database connection string is defined in appsettings.json in the .Web application.

Task Application Service

Application Services are used to expose domain logic to the presentation layer. An Application Service is called from presentation layer with a Data Transfer Object (DTO) as parameter (if needed), uses domain objects to perform some specific business logic and returns a DTO back to the presentation layer (if needed).

I'm creating the first application service, TaskAppService, into the .Application project to perform task related application logic. First, I wanted to define an interface for the app service:

public interface ITaskAppService : IApplicationService
{
    Task<ListResultOutput<TaskListDto>> GetAll(GetAllTasksInput input);
}

Defining an interface is not required, but suggested. By convention, all app services should implement IApplicationService interface in ABP. I just created a GetAll method to query tasks. To do that, I also defined the following DTOs:

public class GetAllTasksInput
{
    public TaskState? State { get; set; }
}

[AutoMapFrom(typeof(Task))] public class TaskListDto : EntityDto, IHasCreationTime { public string Title { get; set; } public string Description { get; set; } public DateTime CreationTime { get; set; } public TaskState State { get; set; } }
  • GetAllTasksInput DTO defines input parameters of the GetAll app service method. Instead of directly defining the state as method parameter, I added it into a DTO object. Thus, I can add other parameters into this DTO later without breaking my existing clients (we could directly add a state parameter to the method).
  • TaskListDto is used to return a Task data. It's derived from EntityDto, which just defines an Idproperty (we could add Id to our Dto and not derive from EntityDto). We defined [AutoMapFrom] attribute to create AutoMapper mapping from Task entity to TaskListDto. This attribute is defined in Abp.AutoMapper nuget package.
  • Lastly, ListResultOutput is a simple class contains a list of items (we could directly return a List<TaskListDto>).

Now, we can implement the ITaskAppService as shown below:

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Abp.Application.Services.Dto;
using Abp.Domain.Repositories;
using Abp.Linq.Extensions;
using Acme.SimpleTaskApp.Tasks.Dtos;
using Microsoft.EntityFrameworkCore;

namespace Acme.SimpleTaskApp.Tasks
{
    public class TaskAppService : SimpleTaskAppAppServiceBase, ITaskAppService
    {
        private readonly IRepository<Task> _taskRepository;

        public TaskAppService(IRepository<Task> taskRepository)
        {
            _taskRepository = taskRepository;
        }

        public async Task<ListResultOutput<TaskListDto>> GetAll(GetAllTasksInput input)
        {
            var tasks = await _taskRepository
                .GetAll()
                .WhereIf(input.State.HasValue, t => t.State == input.State.Value)
                .OrderByDescending(t => t.CreationTime)
                .ToListAsync();

            return new ListResultOutput<TaskListDto>(
                ObjectMapper.Map<List<TaskListDto>>(tasks)
            );
        }
    }
}


  • TaskAppService is derived from SimpleTaskAppAppServiceBase included in the startup template (which is derived from ABP's ApplicationService class). This is not required, app services can be plain classes. But ApplicationService base class has some pre-injected services (like ObjectMapper used here).
  • I used dependency injection to get a repository.
  • Repositories are used to abstract database operations for entities. ABP creates a pre-defined repository (like IRepository<Task> here) for each entity to perform common tasks. IRepository.GetAll()used here returns an IQueryable to query entities.
  • WhereIf is an extension method of ABP to simplify conditional usage of IQueryable.Where method.
  • ObjectMapper (which somes from the ApplicationService base class and implemented via AutoMapper by default) is used to map list of Task objects to list of TaskListDtos objects.

Testing the TaskAppService

Before going further to create user interface, I want to test TaskAppService. You can skip this section if you don't interest in automated testing.

Startup template contains .Tests project to test our code. It uses EF Core's InMemory database provider instead of SQL Server. Thus, our unit tests can work without a real database. It creates a separated database for each test. Thus, tests are isolated from each other. We can use TestDataBuilder class to add some initial test data to InMemory database before running tests. I changed TestDataBuilder as shown below:

public class TestDataBuilder
{
    private readonly SimpleTaskAppDbContext _context;

    public TestDataBuilder(SimpleTaskAppDbContext context)
    {
        _context = context;
    }

    public void Build()
    {
        _context.Tasks.AddRange(
            new Task("Follow the white rabbit", "Follow the white rabbit in order to know the reality."),
            new Task("Clean your room") { State = TaskState.Completed }
            );
    }
}


You can see the sample project's source code to understand where and how TestDataBuilder is used. I added two tasks (one of them is completed) to the dbcontext. So, I can write my tests assuming that there are two Tasks in the database. My first integration test tests the TaskAppService.GetAll method we created above.

public class TaskAppService_Tests : SimpleTaskAppTestBase
{
    private readonly ITaskAppService _taskAppService;

    public TaskAppService_Tests()
    {
        _taskAppService = Resolve<ITaskAppService>();
    }

    [Fact]
    public async System.Threading.Tasks.Task Should_Get_All_Tasks()
    {
        //Act
        var output = await _taskAppService.GetAll(new GetAllTasksInput());

        //Assert
        output.Items.Count.ShouldBe(2);
    }

    [Fact]
    public async System.Threading.Tasks.Task Should_Get_Filtered_Tasks()
    {
        //Act
        var output = await _taskAppService.GetAll(new GetAllTasksInput { State = TaskState.Open });

        //Assert
        output.Items.ShouldAllBe(t => t.State == TaskState.Open);
    }
}


I created two different tests to test GetAll method as shown above. Now, I can open Test Explorer (from Test\Windows\Test Explorer in the main menu of VS) and run the unit tests:

Test explorer

All of them succeed. The last one was a pre-built test in the startup template, which we can ignore for now.

Notice that; ABP startup template comes with xUnit and Shouldly installed by default. So, we used them to write our tests.

Task List View

Now, I know that TaskAppService is properly working. I can start to create a page to list all tasks.

Adding a New Menu Item

 First, I'm adding a new item to the top menu:

public class SimpleTaskAppNavigationProvider : NavigationProvider
{
    public override void SetNavigation(INavigationProviderContext context)
    {
        context.Manager.MainMenu
            .AddItem(
                new MenuItemDefinition(
                    "Home",
                    L("HomePage"),
                    url: "",
                    icon: "fa fa-home"
                    )
            ).AddItem(
                new MenuItemDefinition(
                    "About",
                    L("About"),
                    url: "Home/About",
                    icon: "fa fa-info"
                    )
            ).AddItem(
                new MenuItemDefinition(
                    "TaskList",
                    L("TaskList"),
                    url: "Tasks",
                    icon: "fa fa-tasks"
                    )
            );
    }

    private static ILocalizableString L(string name)
    {
        return new LocalizableString(name, SimpleTaskAppConsts.LocalizationSourceName);
    }
}


Startup template comes with two pages: Home and About, as shown above. We can change them or create new pages. I prefered to leave them for now and create a new menu item.

Creating the TaskController and ViewModel

I'm creating a new controller class, TasksController, in the .Web project as shown below:

public class TasksController : SimpleTaskAppControllerBase
{
    private readonly ITaskAppService _taskAppService;

    public TasksController(ITaskAppService taskAppService)
    {
        _taskAppService = taskAppService;
    }

    public async Task<ActionResult> Index(GetAllTasksInput input)
    {
        var output = await _taskAppService.GetAll(input);
        var model = new IndexViewModel(output.Items);
        return View(model);
    }
}


  • I derived from SimpleTaskAppControllerBase (which is derived from AbpController) that contains common base code for Controllers in this application.
  • I injected ITaskAppService in order to get list of tasks.
  • Instead of directly passing result of the GetAll method to the view, I created an IndexViewModel class in the .Web project which is shown below:
public class IndexViewModel
{
    public IReadOnlyList<TaskListDto> Tasks { get; }

    public IndexViewModel(IReadOnlyList<TaskListDto> tasks)
    {
        Tasks = tasks;
    }

    public string GetTaskLabel(TaskListDto task)
    {
        switch (task.State)
        {
            case TaskState.Open:
                return "label-success";
            default:
                return "label-default";
        }
    }
}


This simple view model gets a list of tasks (which is provided from ITaskAppService) in it's constructor. It also has GetTaskLabel method that will be used in the view to select a Bootstrap label class for given task.

Task List Page

And finally, the Index view is shown below:

@model Acme.SimpleTaskApp.Web.Models.Tasks.IndexViewModel

@{
    ViewBag.Title = L("TaskList");
    ViewBag.ActiveMenu = "TaskList"; //Matches with the menu name in SimpleTaskAppNavigationProvider to highlight the menu item
}

<h2>@L("TaskList")</h2>

<div class="row">
    <div>
        <ul class="list-group">
            @foreach (var task in Model.Tasks)
            {
                <li class="list-group-item">
                    <span class="pull-right label @Model.GetTaskLabel(task)">@L($"TaskState_{task.State}")</span>
                    <h4 class="list-group-item-heading">@task.Title</h4>
                    <div class="list-group-item-text">
                        @task.CreationTime.ToString("yyyy-MM-dd HH:mm:ss")
                    </div>
                </li>
            }
        </ul>
    </div>
</div>


We simply used given model to render the view using Bootstrap's list group component. Here, we used IndexViewModel.GetTaskLabel() method to get label types for tasks. Rendered page will be like that:

Task list

Localization

We used L method in the view which comes from ABP framework. It's used to localize strings. We have define localized strings in Localization/Source folder in the .Core project as .json files. English localization is shown below:

{
  "culture": "en",
  "texts": {
    "HelloWorld": "Hello World!",
    "ChangeLanguage": "Change language",
    "HomePage": "HomePage",
    "About": "About",
    "Home_Description": "Welcome to SimpleTaskApp...",
    "About_Description": "This is a simple startup template to use ASP.NET Core with ABP framework.",
    "TaskList": "Task List",
    "TaskState_Open": "Open",
    "TaskState_Completed": "Completed"
  }
}


Most of the texts are coming from startup template and can be deleted. I just added the last 3 lines and used in the view above. While using ABP's localization is pretty simple, you can see localization document for more information on the localization system.

Filtering Tasks

As shown above, TaskController actually gets a GetAllTasksInput that can be used to filter tasks. So, we can add a dropdown to task list view to filter tasks. First, I added the dropdown to the view (I added inside the header):

<h2>
    @L("TaskList")
    <span class="pull-right">
        @Html.DropDownListFor(
           model => model.SelectedTaskState,
           Model.GetTasksStateSelectListItems(LocalizationManager),
           new
           {
               @class = "form-control",
               id = "TaskStateCombobox"
           })
    </span>
</h2>


Then I changed IndexViewModel to add SelectedTaskState property and GetTasksStateSelectListItems method:

public class IndexViewModel
{
    //...

    public TaskState? SelectedTaskState { get; set; }

    public List<SelectListItem> GetTasksStateSelectListItems(ILocalizationManager localizationManager)
    {
        var list = new List<SelectListItem>
        {
            new SelectListItem
            {
                Text = localizationManager.GetString(SimpleTaskAppConsts.LocalizationSourceName, "AllTasks"),
                Value = "",
                Selected = SelectedTaskState == null
            }
        };

        list.AddRange(Enum.GetValues(typeof(TaskState))
                .Cast<TaskState>()
                .Select(state =>
                    new SelectListItem
                    {
                        Text = localizationManager.GetString(SimpleTaskAppConsts.LocalizationSourceName, $"TaskState_{state}"),
                        Value = state.ToString(),
                        Selected = state == SelectedTaskState
                    })
        );

        return list;
    }
}


We should set SelectedTaskState in the controller:

public async Task<ActionResult> Index(GetAllTasksInput input)
{
    var output = await _taskAppService.GetAll(input);
    var model = new IndexViewModel(output.Items)
    {
        SelectedTaskState = input.State
    };
    return View(model);
}


Now, we can run the application to see the combobox at the top right of the view:

Task list

I added the combobox but it can not work yet. I'll write a simple javascript code to re-request/refresh task list page when combobox value changes. So, I'm creating wwwroot\js\views\tasks\index.js file in the .Web project:

(function ($) {
    $(function () {

        var _$taskStateCombobox = $('#TaskStateCombobox');

        _$taskStateCombobox.change(function() {
            location.href = '/Tasks?state=' + _$taskStateCombobox.val();
        });

    });
})(jQuery);


Before including this javascript file into my view, I used Bundler & Minifier VS extension (which is default way of minifying files in ASP.NET Core projects) to minify the script:

Minify js

This adds the following lines into bundleconfig.json file in the .Web project:

{
  "outputFileName": "wwwroot/js/views/tasks/index.min.js",
  "inputFiles": [
    "wwwroot/js/views/tasks/index.js"
  ]
}


And creates a minified version of the script:

Minified js file

Whenever I change the index.js, index.min.js is automatically re-generated. Now, I can include the javascript file into my page:

@section scripts
{
    <environment names="Development">
        <script src="~/js/views/tasks/index.js"></script>
    </environment>

    <environment names="Staging,Production">
        <script src="~/js/views/tasks/index.min.js"></script>
    </environment>
}


With this code, our view will use index.js in development and index.min.js (minified version) in production. This is a common approach in ASP.NET Core MVC projects.

Automated Testing Task List Page

We can create integration tests that is also integrated to ASP.NET Core MVC infrastructure. Thus, we can completely test our server side code. You can skip this section if you don't interest in automated testing.

ABP startup template includes a .Web.Tests project to do that. I created a simple test to request to TaskController.Index and check the response:

public class TasksController_Tests : SimpleTaskAppWebTestBase
{
    [Fact]
    public async System.Threading.Tasks.Task Should_Get_Tasks_By_State()
    {
        //Act

        var response = await GetResponseAsStringAsync(
            GetUrl<TasksController>(nameof(TasksController.Index), new
                {
                    state = TaskState.Open
                }
            )
        );

        //Assert

        response.ShouldNotBeNullOrWhiteSpace();
    }
}


GetResponseAsStringAsync and GetUrl methods are some helper methods provided by AbpAspNetCoreIntegratedTestBase class of ABP. We can instead directly use the Client (an instance of HttpClient) property to make requests. But using these shortcut methods makes it easier. See integration testing documentation of ASP.NET Core for more.

When I debug the test, I can see the response HTML:

Web test

That shows the Index page returned a response without any exception. But... we may want to go more and check if returned HTML is what we expect. There are some libraries can be used to parse HTML. AngleSharp is one of them and comes as pre-installed in ABP startup template's .Web.Tests project. So, I used it to check the created HTML code:

public class TasksController_Tests : SimpleTaskAppWebTestBase
{
    [Fact]
    public async System.Threading.Tasks.Task Should_Get_Tasks_By_State()
    {
        //Act

        var response = await GetResponseAsStringAsync(
            GetUrl<TasksController>(nameof(TasksController.Index), new
                {
                    state = TaskState.Open
                }
            )
        );

        //Assert

        response.ShouldNotBeNullOrWhiteSpace();

        //Get tasks from database
        var tasksInDatabase = await UsingDbContextAsync(async dbContext =>
        {
            return await dbContext.Tasks
                .Where(t => t.State == TaskState.Open)
                .ToListAsync();
        });

        //Parse HTML response to check if tasks in the database are returned
        var document = new HtmlParser().Parse(response);
        var listItems = document.QuerySelectorAll("#TaskList li");
            
        //Check task count
        listItems.Length.ShouldBe(tasksInDatabase.Count);

        //Check if returned list items are same those in the database
        foreach (var listItem in listItems)
        {
            var header = listItem.QuerySelector(".list-group-item-heading");
            var taskTitle = header.InnerHtml.Trim();
            tasksInDatabase.Any(t => t.Title == taskTitle).ShouldBeTrue();
        }
    }
}


You can check the HTML deeper and in more detailed. But in most cases, checking the fundamental tags will be enough.

LINK: https://www.codeproject.com/articles/1115763/using-asp-net-core-entity-framework-core-and-asp-n

Using AngularJs, ASP.NET MVC, Web API and EntityFramework to build NLayered Single Page Web Applications

Building an NLayered, localized, well-structured Single-Page Web Application using AngularJs, ASP.NET MVC, Web API, EntityFramework and ASP.NET Boilerplate.

Simple Task System Screenshot
A screenshot of the sample application.

Contents

  • Introduction
  • Create the application from boilerplate template
  • Create entities
  • Create DbContext
  • Create Database Migrations
  • Define repositories
  • Implement repositories
  • Build application services
  • Build Web API services
  • Develop the SPA
  • Localization
  • Unit testing
  • Summary
  • Article history
  • References

Introduction

In this article, I'll show you how to develop a Single-Page Web Application (SPA) from ground to up using the following tools:

  • ASP.NET MVC and ASP.NET Web API as web framework.
  • Angularjs as SPA framework.
  • EntityFramework as ORM (Object-Relational Mapping) framework
  • Castle Windsor as Dependency Injection framework.
  • Twitter Bootstrap as HTML/CSS framework.
  • Log4Net for logging, AutoMapper for object-to-object mapping.
  • And ASP.NET Boilerplate as startup template and application framework.

ASP.NET Boilerplate [1] is an open source application framework that combines all of these frameworks and libraries to make you start easily to develop your application. It provides us an infrastructure to develop applications in best practices. It naturally supports Dependency Injection, Domain Driven Designand Layered Architecture. The sample application also implements validation, exception handling, localization andresponsive design.

Create the application from boilerplate template

ASP.NET Boilerplate saves our time while starting a new application by providing templates that combinesandconfiguresbest tools to build enterprise level web applications.

Let's go to aspnetboilerplate.com/Templates to build our application from template...

Create template by ASP.NET Boilerplate

Here, I selected SPA (Single Page Application) with AngularJs and EntityFramework. Also entered SimpleTaskSystem for my project name. It created and downloaded my solution.

There are 5 projects included in the solution. Core project for domain (business) layer, Application project for application layer, WebApi project to implement Web Api controllers, Web project for presentation layer and finally EntityFramework project for EntityFramework implementation.

Note: If you download sample solution for this acticle, you will see 7 projects in the solution. I improved template to support NHibernate and Durandal also for same application. If you don't interest in NHibernate or Durandal, just ignore these 2 projects.

Create entities

I'm creating a simple application to create tasks and assing these tasks to people. So, I need Task and Person entities.

Task entity simply defines a Description, CreationTime and a State for a Task. It also has an optional reference to a Person (AssignedPerson):

public class Task : Entity<long>
{
    [ForeignKey("AssignedPersonId")]
    public virtual Person AssignedPerson { get; set; }

    public virtual int? AssignedPersonId { get; set; }

    public virtual string Description { get; set; }

    public virtual DateTime CreationTime { get; set; }

    public virtual TaskState State { get; set; }

    public Task()
    {
        CreationTime = DateTime.Now;
        State = TaskState.Active;
    }
}


Person entity is simpler and just defines Name of the person:

public class Person : Entity
{
    public virtual string Name { get; set; }
}


ASP.NET Boilerplate provides Entity class that defines Id poperty. I derived entities from this Entity class. Task class has an Id of type long since I derived from Entity<long>. Person class has an Id of type int. Since int is the default primary key type, I did not specified it.

I defined entities in the Core project since Entities are parts of domain/business layer.

Create DbContext

As you know, EntityFramework works with DbContext class. We should first define it. ASP.NET Boilerplate template creates a DbContext template for us. I just added IDbSets for Task and Person. This is my DbContext class:

public class SimpleTaskSystemDbContext : AbpDbContext
{
    public virtual IDbSet<Task> Tasks { get; set; }

    public virtual IDbSet<Person> People { get; set; }

    public SimpleTaskSystemDbContext()
        : base("Default")
    {

    }

    public SimpleTaskSystemDbContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
            
    }
}


It uses Default connection string in web.config. It's defined as shown below:

<add name="Default" connectionString="Server=localhost; Database=SimpleTaskSystem; Trusted_Connection=True;" providerName="System.Data.SqlClient" />

Create Database Migrations

We'll use EntityFramework's Code First Migrations to create and maintain the database schema. ASP.NET Boilerplate template has enabled migrations by default and added a Configurationclass as shown below:

internalinternal sealed class Configuration : DbMigrationsConfiguration<SimpleTaskSystem.EntityFramework.SimpleTaskSystemDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(SimpleTaskSystem.EntityFramework.SimpleTaskSystemDbContext context)
    {
        context.People.AddOrUpdate(
            p => p.Name,
            new Person {Name = "Isaac Asimov"},
            new Person {Name = "Thomas More"},
            new Person {Name = "George Orwell"},
            new Person {Name = "Douglas Adams"}
            );
    }
}


In the Seed method, I added four people for initial data. Now, I'll create the initial migration. I opened Package Manager Console and typed the following command:

Visual studio Package manager console

Add-Migration "InitialCreate" command creates a class named InitialCreate as shown below:

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.StsPeople",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);
            
        CreateTable(
            "dbo.StsTasks",
            c => new
                {
                    Id = c.Long(nullable: false, identity: true),
                    AssignedPersonId = c.Int(),
                    Description = c.String(),
                    CreationTime = c.DateTime(nullable: false),
                    State = c.Byte(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.StsPeople", t => t.AssignedPersonId)
            .Index(t => t.AssignedPersonId);            
    }
        
    public override void Down()
    {
        DropForeignKey("dbo.StsTasks", "AssignedPersonId", "dbo.StsPeople");
        DropIndex("dbo.StsTasks", new[] { "AssignedPersonId" });
        DropTable("dbo.StsTasks");
        DropTable("dbo.StsPeople");
    }
}


We did create needed classes to create the database, but not created the database yet. To do it, I'll run the following command:

PM> Update-Database

This command runs migrations, creates the database and populates the initial data for us:

Database created by EntityFramework Migrations

When we change Entitiy classes, we can easily create new migration classes using Add-Migration command and update the database with Update-Database command. To learn more about database migrations, see entity framework's documentation.

Define repositories

In domain driven design, repositories used to implement database-specific codes. ASP.NET Boilerplate creates an automatic repository for each entity using generic IRepository interface. IRepository defines common methods for select, insert, update, delete and a few more:

IRepository interface

We can extend these repository upon our needs. I will extend it to create a Task repository. As I want to seperate interface from implementation, I declare interfaces for repositories first. Here, is the Task repository interface:

public interface ITaskRepository : IRepository<Task, long>
{
    List<Task> GetAllWithPeople(int? assignedPersonId, TaskState? state);
}


It extends generic IRepository interface of ASP.NET Boilerplate. So, ITaskRepository inherently defines all these methods as default. It can also add it's own methods as I defined GetAllWithPeople(...).

No need to create a repository for Person since default methods are enough for me. ASP.NET Boilerplate provides a way of injecting generic repositories without creating a repository class. We will see it in TaskAppService class in 'Build application services' section..

I defined repository interfaces in the Core project since they are parts of domain/business layer.

Implement repositories

We should implement the ITaskRepository interface defined above. I'm implementing repositories in EntityFramework project. Thus, domain layer becomes completely independent from EntityFramework.

When we created the project template, ASP.NET Boilerplate defined a generic base class for repositories in our project: SimpleTaskSystemRepositoryBase. It's a good practice to have such a base class since we can later add some common methods for our repositories. You can see definition of this class in the code. I just derive from it for TaskRepository implementation:

public class TaskRepository : SimpleTaskSystemRepositoryBase<Task, long>, ITaskRepository
{
    public List<Task> GetAllWithPeople(int? assignedPersonId, TaskState? state)
    {
        //In repository methods, we do not deal with create/dispose DB connections, DbContexes and transactions. ABP handles it.
            
        var query = GetAll(); //GetAll() returns IQueryable<T>, so we can query over it.
        //var query = Context.Tasks.AsQueryable(); //Alternatively, we can directly use EF's DbContext object.
        //var query = Table.AsQueryable(); //Another alternative: We can directly use 'Table' property instead of 'Context.Tasks', they are identical.
            
        //Add some Where conditions...

        if (assignedPersonId.HasValue)
        {
            query = query.Where(task => task.AssignedPerson.Id == assignedPersonId.Value);
        }

        if (state.HasValue)
        {
            query = query.Where(task => task.State == state);
        }

        return query
            .OrderByDescending(task => task.CreationTime)
            .Include(task => task.AssignedPerson) //Include assigned person in a single query
            .ToList();
    }
}


TaskRepository is derived from SimpleTaskSystemRepositoryBase and implements ITaskRepository we defined above.

GetAllWithPeople is our specific method to get tasks where AssignedPerson included (pre-fetched) and optionally filtered by some conditions. We can freely use Context (EF's DBContext) object and database in repositories. ASP.NET Boilerplate manages database connection, transaction, creating and disposing the DbContext for us (See documentation for more information)

Build application services

Application services is used to seperate presentation layer from domain layer by providing façade style methods. I defined application services in the Application assembly in the project. First, I define interface for task application service:

public interface ITaskAppService : IApplicationService
{
    GetTasksOutput GetTasks(GetTasksInput input);
    void UpdateTask(UpdateTaskInput input);
    void CreateTask(CreateTaskInput input);
}


ITaskAppService extends IApplicationService. Thus, ASP.NET Boilerplate automatically provides some features for this class (like dependency injection and validation). Now, let's implement ITaskAppService:

public class TaskAppService : ApplicationService, ITaskAppService
{
    //These members set in constructor using constructor injection.
        
    private readonly ITaskRepository _taskRepository;
    private readonly IRepository<Person> _personRepository;
        
    /// <summary>
    ///In constructor, we can get needed classes/interfaces.
    ///They are sent here by dependency injection system automatically.
    /// </summary>
    public TaskAppService(ITaskRepository taskRepository, IRepository<Person> personRepository)
    {
        _taskRepository = taskRepository;
        _personRepository = personRepository;
    }
        
    public GetTasksOutput GetTasks(GetTasksInput input)
    {
        //Called specific GetAllWithPeople method of task repository.
        var tasks = _taskRepository.GetAllWithPeople(input.AssignedPersonId, input.State);

        //Used AutoMapper to automatically convert List<Task> to List<TaskDto>.
        return new GetTasksOutput
                {
                    Tasks = Mapper.Map<List<TaskDto>>(tasks)
                };
    }
        
    public void UpdateTask(UpdateTaskInput input)
    {
        //We can use Logger, it's defined in ApplicationService base class.
        Logger.Info("Updating a task for input: " + input);

        //Retrieving a task entity with given id using standard Get method of repositories.
        var task = _taskRepository.Get(input.TaskId);

        //Updating changed properties of the retrieved task entity.

        if (input.State.HasValue)
        {
            task.State = input.State.Value;
        }

        if (input.AssignedPersonId.HasValue)
        {
            task.AssignedPerson = _personRepository.Load(input.AssignedPersonId.Value);
        }

        //We even do not call Update method of the repository.
        //Because an application service method is a 'unit of work' scope as default.
        //ABP automatically saves all changes when a 'unit of work' scope ends (without any exception).
    }

    public void CreateTask(CreateTaskInput input)
    {
        //We can use Logger, it's defined in ApplicationService class.
        Logger.Info("Creating a task for input: " + input);

        //Creating a new Task entity with given input's properties
        var task = new Task { Description = input.Description };

        if (input.AssignedPersonId.HasValue)
        {
            task.AssignedPersonId = input.AssignedPersonId.Value;
        }

        //Saving entity with standard Insert method of repositories.
        _taskRepository.Insert(task);
    }
}

TaskAppService
uses repositories for database operations. It gets references in it's constructor via constructor injection pattern. ASP.NET Boilerplate naturally implements dependency injection, so we can use constructor injection or property injection freely (See more on dependency injection in ASP.NET Boilerplate documentation).

Notice that we're using PersonRepository by injecting IRepository<Person>. ASP.NET Boilerplate automatically creates repositories for our entities. If default methods of IRepository enough for us, we don't have to create repository classes.

Application service methods works with Data Transfer Objects (DTOs). It's a best practice and I definitely suggest to use this pattern. But you don't have to do it as long as you can deal with problems of exposing Entities to presentation layer.

In the GetTasks method, I used the GetAllWithPeople method that I implemented before. It returns a List<Task> but I need to return a List<TaskDto> to presentation layer. AutoMapper helps us here to automatically convert Task objects to TaskDto objects. GetTasksInput and GetTasksOutput are special DTOs defined for GetTasks method.

In the UpdateTask method, I retrived the Task from database (using IRepository's Get method) and changed peoperties of the Task. Notice that I did not even called Update method of the repository. ASP.NET Boilerplate implements UnitOfWork pattern. So, all changes in an application service method are a unit of work (atomic) and applied to database at the end of the method automatically.

In the CreateTask method, I simply created a new Task and inserted to database using the IRepository's Insert method.

ASP.NET Boilerplate's ApplicationService class has some properties to make developing application services easier. For example, it defines a Logger property for logging. So, we derived TaskAppService from ApplicationService and used it's Logger property here. It's optional to derive from this class but required to implement IApplicationService (notice that ITaskAppService extends IApplicationService).

Validation

ASP.NET Boilerplate automatically validates inputs of application service methods. CreateTask method gets CreateTaskInput as parameter:

public class CreateTaskInput
{
    public int? AssignedPersonId { get; set; }

    [Required]
    public string Description { get; set; }
}


Here, Description is marked as Required. You can use any Data Annotation attributes here. If you want to make some custom validation, you can implement ICustomValidate as I implemented in UpdateTaskInput:

public class UpdateTaskInput : ICustomValidate
{
    [Range(1, long.MaxValue)]
    public long TaskId { get; set; }

    public int? AssignedPersonId { get; set; }

    public TaskState? State { get; set; }

    public void AddValidationErrors(List<ValidationResult> results)
    {
        if (AssignedPersonId == null && State == null)
        {
            results.Add(new ValidationResult("Both of AssignedPersonId and State can not be null in order to update a Task!", new[] { "AssignedPersonId", "State" }));
        }
    }

    public override string ToString()
    {
        return string.Format("[UpdateTask > TaskId = {0}, AssignedPersonId = {1}, State = {2}]", TaskId, AssignedPersonId, State);
    }
}


AddValidationErrors method is the place you can write your custom validation code.

Handling exceptions

Note that we did not handled any exception. ASP.NET Boilerplate automatically handles exceptions, logs and returns an appropriate error message to the client. Also, in client side, handles these error messages and show to the user. Actually, this is true for ASP.NET MVC and Web API Controller actions. Since we will expose the TaskAppService using Web API, we don't need to handle exceptions. See exception handling document for details.

Build Web API services

I want to expose my application services to remote clients. Thus, my AngularJs application can easily call these service methods using AJAX.

ASP.NET Boilerplate provides an automatic way of exposing application service methods as ASP.NET Web API. I just use DynamicApiControllerBuilder as shown below:

DynamicApiControllerBuilder
    .ForAll<IApplicationService>(Assembly.GetAssembly(typeof (SimpleTaskSystemApplicationModule)), "tasksystem")
    .Build();


For this example, ASP.NET Boilerplate finds all interfaces inherits IApplicationService in Application layer assembly and creates a web api controller for each application service class. There are alternative syntaxes for fine control. We'll see how to call these services via AJAX.

Develop the SPA

I'll implement a Single-Page Web Application as user interface of my project. AngularJs (by Google) is one (propably the top one) of the most used SPA frameworks.

ASP.NET Boilerplate provides a template that makes easy to start with AngularJs. The template has two pages (Home and About) with smooth transition between pages. Uses Twitter Bootstrap as HTML/CSS framework (thus, it's responsive). It's also localized into English and Turkish with ASP.NET Boilerplate's localization system (You can easly add other languages or remove one of them).

We first change route of the template. ASP.NET Boilerplate template uses AngularUI-Router, the de-facto standard router of AngularJs. It provides state based routing modal. We will have two views: task list and new task. So, we will change route definition in app.js as shown below:

app.config([
    '$stateProvider', '$urlRouterProvider',
    function ($stateProvider, $urlRouterProvider) {
        $urlRouterProvider.otherwise('/');
        $stateProvider
            .state('tasklist', {
                url: '/',
                templateUrl: '/App/Main/views/task/list.cshtml',
                menu: 'TaskList' //Matches to name of 'TaskList' menu in SimpleTaskSystemNavigationProvider
            })
            .state('newtask', {
                url: '/new',
                templateUrl: '/App/Main/views/task/new.cshtml',
                menu: 'NewTask' //Matches to name of 'NewTask' menu in SimpleTaskSystemNavigationProvider
            });
    }
]);


app.js is the main javascript file to configure and start our SPA. Notice that we're using cshtml files as views! Normally, html files are used as views in AngularJs. ASP.NET Boilerplate makes it possible to use cshtml files. Thus we will have the power of razor engine to generate HTML.

ASP.NET Boilerplate provides an infrastructure to create and show menus in an application. It allows to define menu in C# and use same menu both in C# and javascript. See SimpleTaskSystemNavigationProvider class for creating menu and see header.js/header.cshtml for showing menu in the angular way.

First, I'm creating an Angular controller for the task list view:

(function() {
    var app = angular.module('app');

    var controllerId = 'sts.views.task.list';
    app.controller(controllerId, [
        '$scope', 'abp.services.tasksystem.task',
        function($scope, taskService) {
            var vm = this;

            vm.localize = abp.localization.getSource('SimpleTaskSystem');

            vm.tasks = [];

            $scope.selectedTaskState = 0;

            $scope.$watch('selectedTaskState', function(value) {
                vm.refreshTasks();
            });

            vm.refreshTasks = function() {
                abp.ui.setBusy( //Set whole page busy until getTasks complete
                    null,
                    taskService.getTasks({ //Call application service method directly from javascript
                        state: $scope.selectedTaskState > 0 ? $scope.selectedTaskState : null
                    }).success(function(data) {
                        vm.tasks = data.tasks;
                    })
                );
            };

            vm.changeTaskState = function(task) {
                var newState;
                if (task.state == 1) {
                    newState = 2; //Completed
                } else {
                    newState = 1; //Active
                }

                taskService.updateTask({
                    taskId: task.id,
                    state: newState
                }).success(function() {
                    task.state = newState;
                    abp.notify.info(vm.localize('TaskUpdatedMessage'));
                });
            };

            vm.getTaskCountText = function() {
                return abp.utils.formatString(vm.localize('Xtasks'), vm.tasks.length);
            };
        }
    ]);
})();


I defined name of the controller as 'sts.views.task.list'. This my convention (for scalable code-base) but you can simply name it as 'ListController'. AngularJs also uses dependency injection. We're injecting '$scope' and 'abp.services.tasksystem.task' here. First one is Angular's scope variable, second one is the automatically created javascript service proxy for ITaskAppService (we built it before in 'Build Web API services' section).

ASP.NET Boilerplate provides infrastructure to use same localization texts both in server and client (see it's documentation for details). 

vm.taks is the list of tasks that will be shown in the view. vm.refreshTasks method fills this array by getting tasks using taskService. It's called when selectedTaskState changes (observed using $scope.$watch).

As you see, calling an application service method is very easy and straightforward! This is a feature of ASP.NET Boilerplate. It generates Web API layer and Javascript proxy layer that talks with this Web API layer. Thus, we are calling the application service method as calling a simple javascript method. It is completely integrated with AngularJs (uses Angular's $http service).

Let's see the view side of task list:

<div class="panel panel-default" ng-controller="sts.views.task.list as vm">

    <div class="panel-heading" style="position: relative;">
        <div class="row">
            
            <!-- Title -->
            <h3 class="panel-title col-xs-6">
                @L("TaskList") - <span>{{vm.getTaskCountText()}}</span>
            </h3>
            
            <!-- Task state combobox -->
            <div class="col-xs-6 text-right">
                <select ng-model="selectedTaskState">
                    <option value="0">@L("AllTasks")</option>
                    <option value="1">@L("ActiveTasks")</option>
                    <option value="2">@L("CompletedTasks")</option>
                </select>
            </div>
        </div>
    </div>

    <!-- Task list -->
    <ul class="list-group" ng-repeat="task in vm.tasks">
        <div class="list-group-item">
            <span class="task-state-icon glyphicon" ng-click="vm.changeTaskState(task)" ng-class="{'glyphicon-minus': task.state == 1, 'glyphicon-ok': task.state == 2}"></span>
            <span ng-class="{'task-description-active': task.state == 1, 'task-description-completed': task.state == 2 }">{{task.description}}</span>
            <br />
            <span ng-show="task.assignedPersonId > 0">
                <span class="task-assignedto">{{task.assignedPersonName}}</span>
            </span>
            <span class="task-creationtime">{{task.creationTime}}</span>
        </div>
    </ul>

</div>


ng-controller attribute (in the first line) binds the controller to the view. @L("TaskList") gets localized text for "task list" (works on server while rendering HTML). It's possible since this is a cshtml file.

ng-model binds combobox and the javascript variable. When the variable changes, the combobox updated. When the combobox changes, the valiable is updated. This is two-way binding of AngularJs.

ng-repeat is another 'directive' of Angular that is used to render same HTML for each value in an array. When the array changes (an item is added for example), it's automatically reflected to the view. This is another powerful feature of AngularJs.

Note: When you add a javascript file (for example, for the 'task list' controller), you should add it to your page. This can be done by adding it to Home\Index.cshtml in the template.

Localization

ASP.NET Boilerplate provides a flexible and strong localization system. You can use XML files or Resource files as localization source. You can also define custom localization sources. See documentation for more. In this sample application, I used XML files (it's under Localization folder in web application):

<?xml version="1.0" encoding="utf-8" ?>
<localizationDictionary culture="en">
  <texts>
    <text name="TaskSystem" value="Task System" />
    <text name="TaskList" value="Task List" />
    <text name="NewTask" value="New Task" />
    <text name="Xtasks" value="{0} tasks" />
    <text name="AllTasks" value="All tasks" />
    <text name="ActiveTasks" value="Active tasks" />
    <text name="CompletedTasks" value="Completed tasks" />
    <text name="TaskDescription" value="Task description" />
    <text name="EnterDescriptionHere" value="Task description" />
    <text name="AssignTo" value="Assign to" />
    <text name="SelectPerson" value="Select person" />
    <text name="CreateTheTask" value="Create the task" />
    <text name="TaskUpdatedMessage" value="Task has been successfully updated." />
    <text name="TaskCreatedMessage" value="Task {0} has been created successfully." />
  </texts>
</localizationDictionary>
 

Unit testing

ASP.NET Boilerplate is designed to be testable. I authored an article to show unit and integration testing for ABP based projects. See the article: Unit testing in C# using xUnit, Entity Framework, Effort and ASP.NET Boilerplate.

Summary

In this article, I demonstrated how to develop an NLayered ASP.NET MVC web application with a SPA and responsive user interface. I used ASP.NET Boilerplate since it makes easy to develop such applications using best practices and saves our time. Use these links for moe:

LINK: https://www.codeproject.com/Articles/791740/Using-AngularJs-ASP-NET-MVC-Web-API-and-EntityFram

Execute Stored Procedure using Entity Framework Core in .NET Core Class Library Project

Execute stored procedure using Entity Framework Core in .NET Core Class library project

Introduction

In this article, I would like to show you how to execute the stored procedure in data access layer library core project.

Prerequisites

Make sure you have installed all the prerequisites in your computer. If not, then download and install all, one by one.

First, download and install Visual Studio 2015 with Update 3 from this link.

If you have Visual Studio 2015 and have not yet updated with update 3, download and install the Visual Studio 2015 Update 3 from this link.

Download and install .NET Core 1.0.1

Problem

Entity Framework Core 1.0.0 RTM still does not support migrations for class library projects. Please refer to the following link for the above problem:

Solution

Step 1

Create a sample ASP.NET core application project using .NET Core template after installing all the prerequisites component.

We can use our class library project in this web application. First, you need to create two .NET core library projects - one for business logic and another for data access layer.

Follow the same step for creating a data access layer library project. Finally, our project will be as follows:

Remove the existing code and add the following piece of code in your business library project‘s project.json file:

{
 "version": "1.0.0-*",

 "dependencies": {
   "NETStandard.Library": "1.6.0"
 },
 "frameworks": {
   "netcoreapp1.0": {}
 }
}


We need to create our class library as a .NET Core app project. For this, you need to the following code in your data access layer library’s project.json file.

{
 "buildOptions": {
   "emitEntryPoint": true
 },
 "frameworks": {
   "netcoreapp1.0": {}
 },
 "dependencies": {
   "Microsoft.NETCore.App": {
     "version": "1.0.1",
     "type": "platform"
   },
   "Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final",
   "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",
   "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.0",
   "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0"
 },
 "tools": {
   "Microsoft.EntityFrameworkCore.Tools": {
     "version": "1.0.0-preview2-final"
   }
 }
}


You’ll also need to add a static void main() to complete the .NET Core app. For this, you need to add an empty program.cs to your class library project. Please refer to the following code:

public class Program
   {
       public static void Main(string[] args)
       {

       }
   }


Step 2

Create a database and tables with stored procedure in SQL Server. Please run the following script in your SQL Server:

USE [master] 

 GO 

 /****** Object:  Database [ContactDB]    Script Date: 2/10/2017 12:22:09 PM ******/ 

 CREATE DATABASE [ContactDB]
CONTAINMENT = NONE
ON  PRIMARY 

 ( NAME = N'ContactDB', _
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ContactDB.mdf' , _
   SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 

 ( NAME = N'ContactDB_log', _
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ContactDB_log.ldf',_
   SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) 

 GO 

 ALTER DATABASE [ContactDB] SET COMPATIBILITY_LEVEL = 110 

 GO 

 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 

 begin 

 EXEC [ContactDB].[dbo].[sp_fulltext_database] @action = 'enable' 

 end 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_NULL_DEFAULT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_NULLS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_PADDING OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_WARNINGS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ARITHABORT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_CLOSE ON 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_SHRINK OFF 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_UPDATE_STATISTICS ON 

 GO 

 ALTER DATABASE [ContactDB] SET CURSOR_CLOSE_ON_COMMIT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET CURSOR_DEFAULT  GLOBAL 

 GO 

 ALTER DATABASE [ContactDB] SET CONCAT_NULL_YIELDS_NULL OFF 

 GO 

 ALTER DATABASE [ContactDB] SET NUMERIC_ROUNDABORT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET QUOTED_IDENTIFIER OFF 

 GO 

 ALTER DATABASE [ContactDB] SET RECURSIVE_TRIGGERS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET  ENABLE_BROKER 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

 GO 

 ALTER DATABASE [ContactDB] SET DATE_CORRELATION_OPTIMIZATION OFF 

 GO 

 ALTER DATABASE [ContactDB] SET TRUSTWORTHY OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 

 GO 

 ALTER DATABASE [ContactDB] SET PARAMETERIZATION SIMPLE 

 GO 

 ALTER DATABASE [ContactDB] SET READ_COMMITTED_SNAPSHOT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET HONOR_BROKER_PRIORITY OFF 

 GO 

 ALTER DATABASE [ContactDB] SET RECOVERY SIMPLE 

 GO 

 ALTER DATABASE [ContactDB] SET  MULTI_USER 

 GO 

 ALTER DATABASE [ContactDB] SET PAGE_VERIFY CHECKSUM  

 GO 

 ALTER DATABASE [ContactDB] SET DB_CHAINING OFF 

 GO 

 ALTER DATABASE [ContactDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 

 GO 

 ALTER DATABASE [ContactDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 

 GO 

 USE [ContactDB] 

 GO 

 /****** Object:  Table [dbo].[Contacts]    Script Date: 2/10/2017 12:22:09 PM ******/ 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 SET ANSI_PADDING ON 

 GO 

 CREATE TABLE [dbo].[Contacts](
               [ContactID] [int] IDENTITY(1,1) NOT NULL,
               [FirstName] [varchar](100) NOT NULL,
               [LastName] [varchar](100) NULL,
               [ContactNo1] [varchar](20) NOT NULL,
               [ContactNo2] [varchar](20) NULL,
               [EmailID] [varchar](200) NULL,
               [Address] [varchar](300) NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 

 (
               [ContactID] ASC 

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

 ) ON [PRIMARY]
 

 GO 

 SET ANSI_PADDING OFF 

 GO 

 /****** Object:  StoredProcedure [dbo].[GetContactList]    Script Date: 2/10/2017 12:22:10 PM ******/ 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 CREATE PROCEDURE [dbo].[GetContactList]
             

 AS                

 SET NOCOUNT ON;                       
BEGIN  
                 SELECT
                                                [ContactID]
                                               ,[FirstName]
                                               ,[LastName]
                                               ,[ContactNo1]
                                               ,[ContactNo2]
                                               ,[EmailID]
                                               ,[Address]
                FROM    Contacts
END 

 GO 

 USE [master] 

 GO 

 ALTER DATABASE [ContactDB] SET  READ_WRITE 

 GO
 

Step 3

Now we can generate the entities using the following dotnet ef command.

ef command now no longer exists, you need to use the following dotnet ef command:

dotnet ef dbcontext scaffold -c ContactDbContext -o Models -f "Data Source=yourdatasource; 
Initial Catalog=ContactDB;User ID=youruserid;Password=yourpassword" 
Microsoft.EntityFrameworkCore.SqlServer


where Models - Folder name that you want to create in data layer project which contains all the entities.

To execute the above command, you need open your command prompt. Go to your data access layer project path and use the above command. Please see the following screenshot for more details:

When you see the “Done” in the above command prompt after executing the above command, then one folder name “Models” is created in our data access layer project which contains the database entities. See the following screenshot:

Open the “ContactDbContext.cs” file, you will see your SQL connection string that you specified in the above efcommand prompt and relevant contact entity:

Ok, we are done with our data access layer.

Step 4

Add your data access reference in business layer project and add your business layer reference in your web project.

Step 5

Now I have created one extension class which is used to map the database object to our entity framework entities.

Step 6

Create an interface name “IContactRepository.cs” in business layer class library:

Step 7

Create a repository name “ContactRepository.cs” and implement the above interface:

That’s it! We are now going to use our stored procedure using the .NET core class library project.

Hope you liked the article.

LINK: https://www.codeproject.com/Articles/1170018/Execute-Stored-Procedure-using-Entity-Framework-Co