ASP.NET MVCのDBファーストでのMySQLとの連携

#概要
ASP.NET MVC + MySQLを使用してDBファーストでWebアプリケーションを作成します。
今回はDBファーストでのMySQLとの連携とWebページの表示までを行います。

#環境
- MySQL 8.0.20
- Visual Studio 2019
- .Net Framework 4.8
- MySql.Data 8.0.20
- MySql.Data.EntityFramework 8.0.20

#DB
今回使用するDBは以下のようなテーブル構成です。
各データは下記リンク先から取得しました。
気象データ:http://www.data.jma.go.jp/obd/stats/etrn/index.php
都道府県:http://www007.upp.so-net.ne.jp/s124/jis.html
市区町村:https://www.soumu.go.jp/denshijiti/code.html
※市区町村は都道府県名をprefectureのidになるように加工しています。

##テーブル構成
|テーブル|内容|
|---|---|
|city|市区町村マスタ|
|prefecture|都道府県マスタ|
|weather|気象データ|

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/46f64133-4ecc-9d77-2b9a-a32be63e416a.png)

##SQL
```sql:スキーマ作成
CREATE DATABASE `weather` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
```
```sql:city.sql
CREATE TABLE `city` (
  `id` int NOT NULL,
  `prefecture_id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `prefecture_fkey_idx_idx` (`prefecture_id`),
  CONSTRAINT `prefecture_fkey_idx` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
```sql:prefecture.sql
CREATE TABLE `prefecture` (
  `id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
```sql:weather.sql
CREATE TABLE `weather` (
  `observational_day` datetime NOT NULL,
  `city_id` int NOT NULL,
  `temperature_ave` decimal(5,2) NOT NULL,
  `temperature_min` decimal(5,2) NOT NULL,
  `temperature_max` decimal(5,2) NOT NULL,
  `precipitation` decimal(5,2) NOT NULL,
  `wind_speed` decimal(5,2) NOT NULL,
  PRIMARY KEY (`observational_day`,`city_id`),
  KEY `city_fkey_idx_idx` (`city_id`),
  CONSTRAINT `city_fkey_idx` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```

#Web アプリの作成
Visual Studio から [新しいプロジェクトの作成] を選択します。
[ASP.NET Web アプリケーション] を選択し、 [次へ] をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/53dfcd54-58ff-f4a2-c3fa-bd069383cf86.png)
プロジェクト名等を設定し、[作成] をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/d31f8aa1-2702-919a-b104-6abdfaee2a2f.png)
MVCを選択し、[作成]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/40d2b1e1-dae4-f715-d072-22d120f9e599.png)


#MySQLとの連携
##コンポーネントのインストール
[NuGet パッケージの管理]から以下をインストールします。
- MySql.Data
- MySql.Data.EntityFramework
※MySql.Data.Entityは不要です。

##モデルの作成
[新しい項目の追加]から[ADO.NET Entry Data Model]を選択します。
今回は名前に「MySqlModel」を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/06353224-f6a6-5bad-2054-c3d4629884dd.png)
[データベースからEF Designer]を選択し、[次へ]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/36ea16a3-202a-8c9b-1827-a425d6cd92f5.png)
[Entry Data Modelウィザード]の[新しい接続]をクリックします。
[接続のプロパティ]のデータソースを[MySQL Database (MySQL Data Provider)]に変更し、DB接続情報を設定後、[OK]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/6e78426d-60d3-9946-7bb4-51f433aeeca2.png)
パスワードを接続文字列に含めるかどうかを選択し、[次へ] をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/632f78e4-62c9-b4b2-4d5c-d38d06dd6526.png)
モデルに含めるオブジェクトを選択し、[完了]をクリックします。
今回はテーブルを全て選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/5a16d4e5-cdb2-5467-d33f-540179b22e7d.png)

完了後、[Web.config]に接続文字列が追加されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/47040e81-bb5f-157e-2b5d-6cb10a56aae8.png)

完了後、DBの情報を元にしたモデルが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/e74bae1e-be2d-a240-10e6-008777a8afaf.png)

#コントローラー作成
ソリューションエクスプローラーのControllersディレクトリ上で右クリック→[追加]→[コントローラー]を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/18b7a1d8-6135-cf55-9619-bc646144216e.png)
[Entity Frameworkを使用した、ビューがあるMVC5コントローラー]を選択し、[追加]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/957deb6b-6418-0138-a806-256b989441ce.png)
[モデルクラス]で使用するモデル(weather)を選択する。
[データコンテキストクラス]は先ほど作成したweatherEntitiesを選択する。
[コントローラー名]のデフォルト値をweathersControllerからWeatherControllerに変更する。
※変えなくてもOK
入力が完了したら、[追加]ボタンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/4f2afb34-0fc0-a408-1ed4-e124bd498707.png)


完了後、コントローラーとビューが自動で作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/8c9565b3-5b65-128f-11e5-f3447328ecde.png)

「プロジェクトをビルドしなおしてください。」というエラーが出た場合は、ビルド後に再実行してください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/ebf828bf-ff34-15f8-19ad-fd3bf89ffc2e.png)

##自動生成されたコントローラーのIndex()
```c#:WeatherController.Index()
public ActionResult Index()
{
    var weather = db.weather.Include(w => w.city);
    return View(weather.ToList());
}
```
##自動作成されたIndexビュー

```c#:Weather/Index.cshtml

@model IEnumerable<MySqlTestWebApp.weather>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.temperature_ave)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_min)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_max)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.precipitation)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.wind_speed)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.city.name)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_ave)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_min)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_max)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.precipitation)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.wind_speed)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.city.name)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

</table>
```
##ページ
Index.cshtmlを表示した状態でCtrl + F5（またはデバッグを実行）でページを表示します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/4063908c-e6b2-1a25-f7b0-e6fcd5a18207.png)

##weatherテーブル
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/b40bcc77-80eb-a744-97d9-a982ff494f7b.png)

DBのweatherテーブルのデータを取得できていることが確認できます。
weatherテーブルの[city_id]はcityテーブルの[id]を外部キーに設定しているため、自動で名前が表示されます。

#ビュー(Index.cshtml)の修正
- 主キーである[observational_day]が表示されていないため追加します。
- 市区町村が2列目になるようにします。
- Edit、Details、Deleteのリンク部分のキーを設定します。   
※主キーが[id]であれば自動で設定されますが、今回は違うので修正します。

##修正後
```c#:Weather/Index.cshtml
@model IEnumerable<MySqlTestWebApp.weather>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.observational_day)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.city.name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_ave)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_min)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_max)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.precipitation)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.wind_speed)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.observational_day)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.city.name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_ave)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_min)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_max)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.precipitation)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.wind_speed)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { observationalDay = item.observational_day, cityID = item.city_id }) |
            @Html.ActionLink("Details", "Details", new { observationalDay = item.observational_day, cityID = item.city_id }) |
            @Html.ActionLink("Delete", "Delete", new { observationalDay = item.observational_day, cityID = item.city_id }) |
        </td>
    </tr>
}

</table>

```
#コントローラー(WeatherController)の修正
- 主キーである[observational_day]、[city_id]を使用するように各メソッドを修正します。   
※引数の変数名とビューのActionLinkに設定した**変数名**を揃える必要があります。

##修正前
```c#:WeatherController.cs(一部抜粋)
public ActionResult Details(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

public ActionResult Edit(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    ViewBag.city_id = new SelectList(db.city, "id", "name", weather.city_id);
    return View(weather);
}

public ActionResult Delete(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(DateTime id)
{
    weather weather = db.weather.Find(id);
    db.weather.Remove(weather);
    db.SaveChanges();
    return RedirectToAction("Index");
}
```
##修正後
```c#:WeatherController.cs(一部抜粋)
public ActionResult Details(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

public ActionResult Edit(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    ViewBag.city_id = new SelectList(db.city, "id", "name", weather.city_id);
    return View(weather);
}

public ActionResult Delete(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(DateTime observationalDay, int? cityID)
{
    weather weather = db.weather.Find(observationalDay, cityID);
    db.weather.Remove(weather);
    db.SaveChanges();
    return RedirectToAction("Index");
}
```

#モデルの修正
日本語の列名を表示させるため、[DisplayName]を設定します。
それから、[observational_day]には[DisplayFormat]を使用して日付のフォーマットを設定します。

##修正前
```c#:weather.cs
namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    
    public partial class weather
    {
        public System.DateTime observational_day { get; set; }
        public int city_id { get; set; }
        public decimal temperature_ave { get; set; }
        public decimal temperature_min { get; set; }
        public decimal temperature_max { get; set; }
        public decimal precipitation { get; set; }
        public decimal wind_speed { get; set; }
    
        public virtual city city { get; set; }
    }
}
```
```c#:city.cs
namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;

    public partial class city
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public city()
        {
            this.weather = new HashSet<weather>();
        }
    
        public int id { get; set; }
        public int prefecture_id { get; set; }
        public string name { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<weather> weather { get; set; }
        public virtual prefecture prefecture { get; set; }
    }
}
```
##修正後
```c#:weather.cs
namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;

    public partial class weather
    {
        [DisplayName("観測日")]
        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy/MM/dd}")]
        public System.DateTime observational_day { get; set; }
        public int city_id { get; set; }
        [DisplayName("平均気温 ℃")]
        public decimal temperature_ave { get; set; }
        [DisplayName("最低気温 ℃")]
        public decimal temperature_min { get; set; }
        [DisplayName("最高気温 ℃")]
        public decimal temperature_max { get; set; }
        [DisplayName("降水量 mm")]
        public decimal precipitation { get; set; }
        [DisplayName("最大瞬間風速 m/s")]
        public decimal wind_speed { get; set; }
    
        public virtual city city { get; set; }
    }
}
```
```c#:city.cs
namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;

    public partial class city
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public city()
        {
            this.weather = new HashSet<weather>();
        }
    
        public int id { get; set; }
        public int prefecture_id { get; set; }
        [DisplayName("市区町村")]
        public string name { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<weather> weather { get; set; }
        public virtual prefecture prefecture { get; set; }
    }
}
```
##修正後のページ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411151/20fda826-954e-3c1b-a93b-8414a7eb1bf2.png)

見た目も多少それらしくなったのではないかと思います。

#まとめ
今回はDBファーストでのMySQLとの連携とWebページの表示までを行いました。
普段はWeb系の開発はやらないので難しい印象がありましたが、DBの情報を使用してコントローラやビューを自動で作成してくれるので、かなり助かりました。

#参考
https://docs.microsoft.com/ja-jp/aspnet/mvc/overview/getting-started/database-first-development/
https://qiita.com/mrpero/items/607c31895d77815a77cb