1. Introduction

This article will walk us through on building a simple web API service using .NETCore. Service is extended to interact with a SQL server database in a windows PC and then modify the same service to work with a MYSQL database in a Linux server.

↑ Return to Top


2. Background

If we haven't installed .NETCore in a linux server, go through this article, http://social.technet.microsoft.com/wiki/contents/articles/36318.install-netcore-in-a-linux-server.aspx

If we are not familiar with visual studio code, haven't created any .NTCore applications on a linux server, refer to this article, http://social.technet.microsoft.com/wiki/contents/articles/36330.net-core-with-a-linux-server-hello-world.aspx

These articles describe how to create a .NETCore web application in linux server, if we haven't tried to create an application using Yeoman template generators go through these articles,

http://social.technet.microsoft.com/wiki/contents/articles/36333.create-a-web-application-in-netcore-with-a-linux-server.aspx

http://social.technet.microsoft.com/wiki/contents/articles/36334.net-core-create-web-application-with-yeoman-template-generator.aspx

↑ Return to Top


3. Prerequisites

In this article, we are working with two environments, Windows and a Linux environment. Check whether we have installed these prerequisites before we start.

In Windows environment,

In Linux environment,

I have used Entityframework Core to access data from databases, necessary references to EFCore is installed in next steps.

↑ Return to Top


4. Create a Web API application in .NETCore

Let’s try to create a .NETCore application from Visual Studio 2015

Create .NETCore web application from Visual Studio

Create a new project from Visual Studio 2015, In .NETCore tab, select ASP.NET Core Web Application (.NET Core) . Give it a name and click on OK.

Select Web API from ASP.NET Core templates

Select Web API from ASP.NET Core templates and click on OK.

build the project and see changes

Check folder structure of the application, It includes a Program.cs and Startup file. It includes a project.json file to define all required packages for our application. appsettings.json file maintains application settings same as web.config file in a ASP.NET Web application. Controllers folder has all Web API Controllers.

↑ Return to Top


4.1. Open service application from Visual Studio Code.

Open your application in Visual Studio Code

cd into the application directory and open Visual Studio Code using code . command. We can see folder structure of our application as above. Add assets to build and debug our application as the info dialog box suggests.

vscode folder is added

vscode folder is added into the solution with launch.json and tasks.json file.

↑ Return to Top


4.2. Dependencies in project.json file

Let’s see what are the dependencies required for a mvc application.

basic packages required for a mvc application

Since We create a Web API application, in project.json file, It shows Microsoft.AspNetCore.Mvc package dependency.

Return to Top


4.3. Run ValuesController and check service calls.

Let’s run Web API application and check available services.

Read service in ValuesController

Build (Ctrl + Shift + b) and run (Ctrl + F5) your application and ping to the Read service as above. Its shows return values in the browser.

↑ Return to Top


4.4. Let’s build the movie service

In this solution, Movie service is accessing a SQL Server database on windows, and later the same application with less modifications, going to access a MYSql server database in a Linux server

↑ Return to Top


4.4.1. Create Movie model.

namespace movieStore.Models
{
 public class Movie
  {
 
    public int ID { get; set; }
    public string Title { get; set; }
    public string Director { get; set; }
    public int Year { get; set; }
    public string Language { get; set; }
    }
}
Create a Model folder to define entities in our application. Then create Movie class and add properties into it. We follow Code First approach in this example. So when we create the database Movie table will be created.

↑ Return to Top


4.5. Create Movie service with SQL server on Windows.

4.5.1. Create Context class to build the database.

Create db context class

namespace movieStore.Models
{
    public class MovieDbContext : DbContext
    {
        public MovieDbContext(DbContextOptions<MovieDbContext> options) : base(options)
        {
 
        }
 
        public DbSet<Movie> Movies { get; set; }
 
    }
}

From context class, our database will be generated. Context class should be inherited from DbContext class. In order to do that, we have to add references from Entityframework core.

↑ Return to Top


4.5.2. Install Entityframework Core (EF Core)

Add entityframeworkcore references into project.json file

Add entityframeworkcore reference as a dependency into project.json file, “Microsoft.EntityFrameworkCore”: “1.0.0” and resolve reference error in MovieDbContext class by adding using Microsoft.EntityFrameworkCore statement.

↑ Return to Top


4.5.3. Define the connectionstring

Define the connectionstring in appsetings.json file

Define the connectionstring in appsettings.json file to connect to the Sql server,

“ConnectionStrings: { “DefaultConnection”: “Data Source=localhost;Initial Catalog=movieDB;Integrated Security= true” }

↑ Return to Top


4.5.4. Check MovieDbContext class

MovieDbContext class with constructor

In MovieDbContext class, define constructor with context options and call base class method.

↑ Return to Top


4.5.5. Add entities in moviecontext class

run EFCore migrations

Try to run migrations for MovieDbContext class,

 

 dotnet ef migrations add InitialMigration

It gives an error,

No executable found matching command “dotnet-ef”

We have to install entityframeworkcore tools to run migrations from .NET cli. Let’s try to do that.

↑ Return to Top


4.5.6. Add EFCore tools

Add EFCore tools and run migrations

Add EFCore tools in tools section of project.json,

 "Microsoft.EntityframeworkCore.Tools" : "1.0.0-preview2-final"

Try to run migration and it gives an error again!!,

Could not invoke this command with the startup project ‘demo’. Check that ‘Microsoft.EntityFrameworkCore.Design’ has been added to “dependencies” in the startup project and that the version of ‘Microsoft.EntityFrameworkCore.Tools’ in “tools” and ‘Microsoft.EntityFrameworkCore.Design` are the same. See http://go.microsoft.com/fwlink/?LinkId=798221 for more details,

We haven’t added EFCore Design as a reference, We have to add EFCore Design references to run migrations scripts. Let’s add it and check. And also it mentions ‘Microsoft.EntityFrameworkCore.Design’ and ‘Microsoft.EntityFrameworkCore.Tools’ version should be same.

↑ Return to Top


4.5.7. Add EFCore Design reference.

Add entityframeworkcore design reference

Add EFCore Design reference into project.json file. Note that, `Microsoft.EntityFrameworkCore.Design' and `Microsoft.EntityFrameworkCore.Tools' version are same.

 "Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final"

Then try to run migrations command again and it gives another error,

No parameterless constructor was found on ‘MovieDbContext’. Either add a parameterless constructor to ‘MovieDbContext’ or add an implementation of ‘IDbContextFactory‘ in the same assembly as ‘MovieDbContext’.

↑ Return to Top


4.5.8. Startup class implementation

Configure services in startup class

 public void ConfigureServices(IServiceCollection services)

   {

         services.AddDbContext<MovieDbContext>(options =>                                                                                                                            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
 
// Add framework services

services.AddMvc();

}

In Configure services method, it’s going to add some services into the application.

services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString(“DefaultConnection”)));

Add the database context and define the connectionstring of our database, in configure service method, it’s missing some references, since we haven't add any EntityframeworkCore sqlserver references.

↑ Return to Top


4.5.9. Add EFCore Sqlserver references

Add EntityFrameworkCore sqlserver reference

Add EFCore Sql server reference in project.json file and restore them.

 “Microsoft.EntityFrameworkCore.SqlServer”: “1.0.0”

Add EFCore sqlserver references

Now sqlserver reference is added. Configure service method looks fine.

↑ Return to Top


4.5.10. Run Migrations

Let’s create movieDB in sql server.

run migrations and check what happens

Run Sqlserver migrations, It doesn’t give any errors in .NET cli, Migrations folder is created with migrations scripts. In Initial Migration class, it defines Up and Down methods to create and drop movie table respectively.

↑ Return to Top


4.5.11. Update the database

movie database is created

run commands to update the database. In sqlserver, movieDB got created.

 dotnet ef database update

↑ Return to Top


4.5.12. Add Movie controller

In Visual studio code, We can’t use scaffolding. So i created a Movie Controller with all the CRUD operations of Movie entity in visual studio. Let’s add it.

Movie controller with all the CRUD operations

In Movie Controller, it works with application/json type data, and the route is api/Movies. Note that in .NET Core Api controllers inherit from Controller class, not from API Controller class.

ping to api/movies method

Go to api/movies method and check Network tab in developer tools. method returns 200 - OK. as a response. We don’t have data in movie database, Let’s try to add some data.

↑ Return to Top


4.5.13. Add data using Postman

ping to api/post a movie

Postman is a tool to test our service apis. Although we can test GET requests through browser, to test POST requests, we need to use Postman. Add a movie using Postman and retrieve it using web browser.

We have created a service API using .NETCore and SQLServer. Let’s try to run this same application on Ubuntu with MySQL.

↑ Return to Top


4.6. Change Movie service to work with MYSQL on Linux server.

4.6.1. Clone your application in Ubuntu

Let’s try to open our application in Ubuntu, We are going to connect this application to MySQL server,

Clone your application in Ubuntu

Type git clone … with repository url, Our project will be downloaded. Then cd into application directory and open it in visual studio code.

↑ Return to Top


4.6.2. Restore packages and build your application

Restore packages and build application

Restore packages defined in project.json file and Build the application. It all works fine.

↑ Return to Top


4.6.3. Let’s try to run the application

Run your application

Let’s try to run our application without any modifications, It seems working fine. Let’s navigate to api/values , It calls READ service of Values service. It returns 200 - OK as response.

Read service in movie controller

Ping to api/movies, it returns an empty array, since we don’t have a database.

Check the terminal and try to find any errors, It shows a fail error in red. It says 'Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[1] An exception occurred in the database while iterating the results of a query. System.NotSupportedException: The keyword ‘integrated security’ is not supported on this platform. at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) …'

It says an error as The keyword ‘integrated security’ is not supported on this platform. If we remember, where we have used integrated security , that’s in sql server connectionString. We can’t operate with Sql server on Ubuntu. We have to install My SQL server to store data in our application. Let’s do that.

↑ Return to Top


4.6.4. Add MySQL connectionString

Define mysql connectionString

Add mysql connectionString in appsetings.json file, movieDB will get created in mysql server.

 "DefaultConnection" : "server=localhost;database=movieDB;uid=root;pwd=hansamali;sslmode=none;"

↑ Return to Top


4.6.5. Add MySQL service in your application

Add mysql service into your application

If we remember, we added Sql service into the application when we run in Windows. In Ubuntu also same thing we have to do. Add MySQL service into our application with dbContext class. But it seems we are missing something in here.

Note that, Microsoft.EntityframeworkCore has been highlighted. Our application doesn’t use that reference anymore. It’s a reference from Sql server. We have to add a reference from MySQL to proceed with this. Let’s try to add it.

Add mysql EFCore reference

Add MySQL EFCore reference in project.json file and restore it.

“MySql.Data.EntityFrameworkCore” : “7.0.4-ir-191”

Add necessary changes in ConfigureServices method in Startup class.

Add mysql reference in Startup class

services.AddDbContext(options =>

options.UseMySQL(Configuration.GetConnectionString(“DefaultConnection”)));

Add necessary using statements in startup class.

using MySQL.Data.EntityFrameworkCore.Extensions;

In windows with Sql server, We run migration scripts and ensure database is created. But with Ubuntu, we can’t do that. Somehow we have to ensure our database is created. Let’s give it a try.

↑ Return to Top


4.6.6. Ensure MySQL database got created

Add this lines of code to ensure MySQL database got created.

var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseMySQL(Configuration.GetConnectionString(“DefaultConnection”));
 
var context = new MovieDbContext(optionsBuilder.Options);
context.Database.EnsureCreated();

↑ Return to Top


4.6.7. Run your application

run your application

Then build and run the application, navigate to movies read service and verify it works fine. read service returns empty array. Let’s check database is created in MySQL server.

↑ Return to Top


4.6.8. View MySQL databases

Check whether your database is created

run commands to access mysql shell, 

 mysql -u root -p

type command to view all the available databases, we can see movieDB is successfully created.

 show databases;

↑ Return to Top


4.6.9. Let’s add some data into Movie database in MySQL

Add a movie into database

Call POST service in movie controller and add a movie into the mysql database. Refresh read movie service, it shows available movies in our mysql database.

Connect to the mysql shell and try to view data in Movies table.

Go to mysql shell by typing this command, 

 

 mysql -u root -p

view available databases

 show database;

type following command to go inside a database and query it

 use movieDB;

We can see available tables in movieDB using this command

 show tables;

Type a select query to view data in movies table

 select * from Movies;

In this post, I described how to run a web api application in windows with Sqlserver. And then we tried to configure same service application on Ubuntu with MySql server, with less amount of coding we could achieve that. It was a really cool feature in .NETCore

↑ Return to Top


5. Download

5.1. TechNet Gallery

5.2. GitHub

↑ Return to Top


6. Conclusion

In this article as we saw, we could modify same .NETCore application in both environments, Windows and Linux. We worked with two different databases SQL server and MYSQL, with Entityframework Core. I hope EFCore will provide its features for Oracle soon. Since .NETCore is cross platform and open source building applications for multiple environments is possible today.

↑ Return to Top


7. References

↑ Return to Top