Entity Framework Core with SQL Server on Mac

Hi All mac and microsoft tech lovers,

There was a time where we love to use our macs and microsoft technologies but since there were not quite compatible with each we have to use windows os to work with microsoft technologies.
But now microsoft has developed tools and compatibility to do this on any os. So here I would like to show you how to work with SQL Server and Entity Framework Core in your mac.
Lets get started...

You have to have docker in order to use SQL Server on Mac.
So if you are new to this and would like to learn how to do that please read my blog from here and come back to this.

Ok.. I think you have set up Docker, SQL Server and Azure Data Studio.

I'm going to use Visual Studio for Mac as my IDE. If you like to use that you can download it from: https://visualstudio.microsoft.com/vs/mac/.
Lets continue then..

1. Open Visual Studio for Mac (VSM) and create a Web API project. I call it MyAPI
I'm going to create a project with N-Tier architecture, so you can have better idea of how things to be done in a proper way.

2. Add NuGet package Microsoft.EntityFrameworkCore.SqlServer for MyAPI

3. Add another library project to the solution. I'll name it as BusinessLayer because it will act as the logic layer of the solution.

4. Then I'll add another library project to the solution. I'll name it as DataLayer because it will act as the layer which manipulate the database of the solution.

5. I'll add another library project to the solution. I'll name it as Models because it will hold the entity models of the solution.

6. Add NuGet package Microsoft.EntityFrameworkCore.SqlServer for DataLayer.

7. Add NuGet package Microsoft.EntityFrameworkCore.Design for DataLayer.

8. Add dependency as bellow:

• Models --> DataLayer
• Models --> BusinessLayer
• DataLayer --> BusinessLayer 
• BusinessLayer --> MyAPI 

9. Create the following model class

using System;
using System.ComponentModel.DataAnnotations;

namespace Models
{
    public class User
    {
        [Key]
        public Guid UserId { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
}  

10. Go to DataLayer and create a new cs file for DbContext. I'll name it as MyTestDbContext

Then add the your connection string into that. 

using Microsoft.EntityFrameworkCore;
using Models;

namespace DataLayer
{
    public class MyTestDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("server=127.0.0.1,1433;database=MyTestWebDB;user=sa; Password=your_password");
        }
    }
}  

10. Inorder to work Entity Framework database automatic updates and migrations you have to add Microsoft.EntityFrameworkCore.Tools.DotNet.
But you cannot add this in VSM because it still doesn't has a package manager console (In future we hope it will come)
Therefore, go to your project folder in finder window and open the .csproj with an another text editor.
I opened it with VS Code.
And then add the following to that file (between <project> tags).

<ItemGroup>
    <DotNetCliToolReference Include = "Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0"/>
</ItemGroup>

11. Then open up a terminal window and run the following command:

dotnet ef

Then it will show you the information of Entity Framework Core .NET Command-line Tools

12. Then go to your project location from finder and open terminal from your DataLayer folder.
This simply means you have to change the terminal folder path to your projects' DataLayer folder.

13. Then run following command

dotnet ef migrations add initial

dotnet ef migrations add [migration_name]

 14. Then you can see there is a folder called Migration has been added to your DataLayer project and it has some files like these (your names can be change)

20190403190158_initial.cs
20190403190158_initial.Designer.cs

15. Now you have added the migration but your database don't know about the changes. So its time to update the database. In order to do that run the following command:

dotnet ef database update

This will update the database and a table called Users.

You can go to Azure Data Studio and refresh in order to see these changes.

16. Now its all set. Lets try adding some data to our newly created table.
To do that I'll create a method in BusinessLayer and call it from MyAPI

BusinessLayer class logic:

using System;
using Models;
using DataLayer;

namespace BusinessLayer
{
    public class UserManageer
    {
        public void AddUser()
        {
            try
            {
                var u = new User
                {
                    UserId = Guid.NewGuid(),
                    Name = "Don",
                    Age = 23
                };
                var ctx = new MyTestDbContext();
                ctx.Users.Add(u);
                ctx.SaveChanges();
            }
            catch (Exception ex)
            {
                var a = ex;
            }
        }
    }
}

MyAPI controller logic:

using Microsoft.AspNetCore.Mvc;
using BusinessLayer;

namespace MyAPI1.Controllers
{
    [Route("api/[controller]")]
    public class UserController : Controller
    {
        [HttpGet("AddUser")]
        public void AddUser()
        {
            var um = new UserManageer();
            um.AddUser();
        }
    }
}


17. Run the project and go to your browser or use PostMan to invoke the AddUser API endpoint.
And

18. And then, if you run the following query in Azure Data Studio

SELECT * FROM [MyTestWebDB].[dbo].[Users]

Then you can see your newly entered data has been added to the table.

Now its up to you to do all the crazy research and have fun with this.

Cheers !!