Summary Files Commits Branches @commit @diff @stats
path: root / src / tutorials / connect-to-mysql-in-csharp-ef / article.md
blob: f2784e22dd85353475a781440afa0a7179b4208f (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
# Connect to MySql in C# and Entity Framework 

**Article Created: 21 May 2020** || **Last Edited: 25 November 2020**

## [*Due for rework*](/rework.html)

---

In my experience, connecting a `C#` application to a `MySQL` database with Entity Framework can be quite tricky. Here, I share what works for me.

## Contents
[![🔗](/img/link-tabler.svg)](#Contents)

1. [Preparation](#Preparation)
2. [Connecting](#Connecting)

## Preparation
[![🔗](/img/link-tabler.svg)](#Preparation)

<span></span>

### MySql
[![🔗](/img/link-tabler.svg)](#MySql)

This tutorial doesn't cover it, but you'll need to have properly [installed and configured](https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/) `MySQL`.

### NuGet packages
[![🔗](/img/link-tabler.svg)](#NuGet%20packages)

You'll need to install two NuGet packages: [Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore) and [MySql.Data.EntityFrameworkCore](https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/).

[Microsoft.EntityFrameworkCore](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore) is the [object-relational mapping](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) implementation we'll be using. It doesn't offer the specifics for connecting to a `MySQL` database, but it does everything else.

[MySql.Data.EntityFrameworkCore](https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/) is what enables Microsoft.EntityFrameworkCore to connect to a `MySQL` database.

## Connecting
[![🔗](/img/link-tabler.svg)](#Connecting)

<span></span>

### Connection string
[![🔗](/img/link-tabler.svg)](#Connection%20string)

You can find a truckload of ways to make your connection string from places like [this](https://www.connectionstrings.com/mysql/) but in this case, I've found out that most don't work.
After a lot of trial and error, I've found that this one works the best:

```                
"server=localhost; port=YOUR-PORT; user id=YOUR-USER-ID; password=YOUR-PASSWORD; database=NAME-OF-DATABASE; SslMode=none"
```

If you are using `MySQL Workbench` and you don't know your port and id, you can look at the server information from the `MySQL Connections` page.

![MyServer root 127.0.0.1:3306](./img/connections.png) In this example, the user id is _root_ and the port is _3306_.

### ASP.NET Startup
[![🔗](/img/link-tabler.svg)](#ASP.NET%20Startup)

If you are making an `ASP.NET` application, you'll need to put the connection string in the `ConfigureServices` method of the `Startup.cs` file.

You'll also need to modify your `ConfigureServices` method, by replacing `.UseSQL` with `.UseMySQL`. 

The result should look something like this:

```
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{                    
	services.AddControllersWithViews();
	services.AddTransient<UserServices, UserServices>();
	services.AddDbContext<AppDbContext>(opt =>
	{
		opt.UseMySQL(CONNECTION_STRING);
	}); 
}
```

### DBContext child class
[![🔗](/img/link-tabler.svg)](#DBContext%20child%20class)

If you want to connect a `DBContext` child class to a `MySQL` database, one of the easiest ways to do it, is to override the `OnConfiguring` method. 

In the override, you'll need to call the `optionsBuilder.UseMySQL` method (`optionsBuilder` is a parameter of the method) with the connection string.

```
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	optionsBuilder.UseMySQL(CONNECTION_STRING);
}
```

Et voilà! You should now be connected.