mysql - 一对一关系 Mysql EF6

Question

你好，我已经绞尽脑汁两天了，一直没能解决这个问题:我有这个数据结构:

[
  {
    "LocalObservationDateTime": "2019-12-12T19:50:00+02:00",
    "EpochTime": 1576173000,
    "WeatherText": "Thunderstorm",
    "WeatherIcon": 15,
    "HasPrecipitation": true,
    "Reciprocation": "Rain",
    "IsDayTime": false,
    "Temperature": {
      "Metric": {
        "Value": 13.7,
        "Unit": "C",
        "UnitType": 17
      },
      "Imperial": {
        "Value": 57,
        "Unit": "F",
        "UnitType": 18
      }
    } 
  }
]

我这样设计我的数据:

表1:

Id,
LocalObservationDateTime,
EpochTime
WeatherText,
WeatherIcon,
HasPrecipitation,
PrecipitationType,
IsDayTime,
TemperatureId

表2

TemperatureId,
ImperialId,
MetricId

表3

Id,
Value,
Unit,
UnitType

我将表 3 中的 FK 表 3 Id ImperialId 和 MetricId 以及表 2 中的TemperatureId 连接到表 1 中的TemperatureId

这些是我的模型:

[Table("currentweather")]
    public class CurrentWeather
    {
        [Key, Column("id")]
        public int Id { get; set; }
       [Column("locationid")]
        public int LocationId { get; set; }
        [Column("localobservationdatetime")]
        public DateTime LocalObservationDateTime { get; set; }
         [Column("epochtime")]
        public long EpochTime { get; set; }
        [Column("weathertext")]
        public string WeatherText { get; set; }
        [Column("weathericon")]
        public int WeatherIcon { get; set; }
         [Column("hasprecipitation")]
        public bool HasPrecipitation { get; set; }
       [Column("precipitationtype")]
       public string PrecipitationType { get; set; }
        [Column("isdaytime")]
        public bool IsDayTime { get; set; }

        [ForeignKey("Temperature")]
        [Column("temperatureid")]
        public int TemperatureId { get; set; }       

        [Column("mobilelink")]
        public string MobileLink { get; set; }
        [Column("link")]
        public string Link { get; set; }

        [NotMapped]
        public Temperature Temperature { get; set; }

    }



[Table("temperature")]
    public class Temperature
    {
        [Key, Column("tepmeratureid")]
        public int TemperatureId { get; set; }
        [ForeignKey("Imperial")]
        [Column("imperialid")]
        public int ImperialId { get; set; }
        [ForeignKey("Metric")]
        [Column("metricid")]
        public int MetricId { get; set; }

        [NotMapped]
        public CurrentWeather CurrentWeather { get; set; }
        [NotMapped]
        public TemperatureUnit Imperial { get; set; }
        [NotMapped]
        public TemperatureUnit Metric { get; set; }
    }

    public class TemperatureUnit
    {

        public double Value { get; set; }
        public Unit Unit { get; set; }

        public int UnitType { get; set; }
        [NotMapped]
        public Temperature Temperature { get; set; }

    }

    public enum Unit { C,F}

我正在使用 Mysql 和 Net.core EF6

添加数据时，只有表 1 获取数据，但其他两个没有帮助!!!

Answer 1

您的 json 对象与您的数据库表不匹配，因此您必须对这些属性进行一些映射。

注意:下面的代码省略了几个属性，但您应该能够遵循此代码并根据需要添加它们。

首先，您需要将 json 字符串变成一个对象。如果从 Controller 传入，您可以使用模型绑定(bind)；否则你将不得不反序列化它。这是一个合适的 View 模型:

 public class CurrentWeatherViewModel
    {
        public DateTime LocalObservartionTime { get; set; }
        public int EpochTime { get; set; }
        public string WeatherText { get; set; }
        //additional props here


        public TemperatureViewModel Temperature { get; set; }
    }



    public class TemperatureViewModel
    {
        public MetricViewModel Metric { get; set; }
        public ImperialViewModel Imperial { get; set; }
    }

    public class MetricViewModel
    {
        public decimal Value { get; set; }
        public string Unit { get; set; }
        public int UnitType { get; set; }
    }
    public class ImperialViewModel
    {
        public decimal Value { get; set; }
        public string Unit { get; set; }
        public int UnitType { get; set; }
    }

您可以将 json 字符串反序列化为该对象，如下所示:

var jsonString = " {\"EpochTime\": 1576173000,\"WeatherText\": \"Thunderstorm\",\"WeatherIcon\": 15,\"HasPrecipitation\": true,\"Reciprocation\": \"Rain\",\"IsDayTime\": false,\"Temperature\": {\"Metric\": {\"Value\": 13.7,\"Unit\": \"C\",\"UnitType\": 17},\"Imperial\": {\"Value\": 57,\"Unit\": \"F\",\"UnitType\": 18}}}";

CurrentWeatherViewModel currentWeatherViewModel = Newtonsoft.Json.JsonConvert.DeserializeObject<CurrentWeatherViewModel>(jsonString);

一旦反序列化到 View 模型对象中，您就可以创建实体并将它们保存到数据库中:

 CurrentWeather currentWeather = new CurrentWeather
            {
                WeatherText = currentWeatherViewModel.WeatherText,
                Temperature = new Temperature
                {
                    Measurements = new Measurement[] {
                        new Measurement {
                            Unit = currentWeatherViewModel.Temperature.Metric.Unit,
                            UnitType = currentWeatherViewModel.Temperature.Metric.UnitType,
                            Value = currentWeatherViewModel.Temperature.Metric.Value,
                        },
                        new Measurement {
                            Unit = currentWeatherViewModel.Temperature.Imperial.Unit,
                            UnitType = currentWeatherViewModel.Temperature.Imperial.UnitType,
                            Value = currentWeatherViewModel.Temperature.Imperial.Value,
                        }
                    }
                }
            };

            db.CurrentWeathers.Add(currentWeather);
            db.SaveChanges();

这是实体类:

 public class CurrentWeather
    {
        public int Id { get; set; }
        public DateTime? LocalObservartionTime { get; set; }
        public int EpochTime { get; set; }
        public string WeatherText { get; set; }
        //additional props here

        public virtual Temperature Temperature { get; set; }
    }

    public class Temperature
    {
        public int Id { get; set; }

        //relationship
        public int CurrentWeatherId { get; set; }

        //relationship
        public virtual ICollection<Measurement> Measurements { get; set; }
    }

    public class Measurement
    {
        public int Id { get; set; }
        public decimal Value { get; set; }
        public string Unit { get; set; }
        public int UnitType { get; set; }

        //relationship
        public int TemperatureId { get; set; }
        public virtual Temperature Temperature { get; set; }

    }