设置“扔类型的异常'System.Data.SqlClient.SqlException'

问题描述

我得到的异常并不能看着办吧。

I got the exception and can't figure it out.

设置='（（BandwidthRestriction.Models.SettingRespository）settingRespository） .Settings'扔类型的异常System.Data.SqlClient.SqlException

我有两个表。

namespace BandwidthRestriction.Models
{
    [Table("Settings")]
    public class Setting
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string DefaultValue { get; set; }
        public string Classification { get; set; }
        public virtual FacilitySettingOverride FacilitySettingOverride { get; set; }
    }
}

和

namespace BandwidthRestriction.Models
{
    [Table("FacilitySettingOverride")]
    public class FacilitySettingOverride
    {
        [Key]
        public int FacilityId { get; set; }
        public int SettingId { get; set; }
        public string Value { get; set; }
        public virtual ICollection<Setting> Settings { get; set; }
        public virtual ICollection<Facility> Facilities { get; set; }
    }
}

另一个表

Another table

namespace BandwidthRestriction.Models
{
    [Table("Facilities")]
    public class Facility
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<FacilitySettingOverride> FacilitySettingOverrides { get; set; }
    }
}

表的结构喜欢

The table's structure likes

[！[12] [1] [1]

[![12][1]][1]

我也有对应的DbContext为

Also I have the correspond dbcontext as

 public class SettingDbContext : DbContext
{
    public DbSet<Setting> Settings { get; set; }
    public DbSet<FacilitySettingOverride> FacilitySettingOverride { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=11.53.63.94;Initial Catalog=AAA;User ID=sa;password=password;Application Name=XXX");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

    }
}

在程序存储库，我有

namespace BandwidthRestriction.Models
{
    public class SettingRespository : ISettingRespository
    {
       public List<Setting> GetAllSettings()
       {
          return Settings.ToList();
       }

       public IEnumerable<Setting> Settings
       {
           get
           {
               List<Setting> settingList;
               using (SettingDbContext context = new SettingDbContext())
               {
                   settingList = context.Settings.ToList();
               }
               return settingList;
           }
        }

在控制器中，我通过了DI。

In the controller, I passed the DI.

 [Route("api/[controller]")]
 public class BandwidthController : Controller
 {
     private readonly ISettingRespository _settingRespository;

     public BandwidthController(ISettingRespository settingRespository)
     {
         _settingRespository = settingRespository;
     }

然而，当我将鼠标悬停在 _settingRespository 。我看到了异常：

设置='（（BandwidthRestriction.Models.SettingRespository）settingRespository）.Settings扔类型的异常系统.InvalidOperationException

编辑：

每评论，我固定的表名拼写错误的问题。错误是的SQLException：无效的列名称FacilitySettingOverrideSettingId ，但是我发现在的。也许我使用的代码错误第一次？

Per the comment, I fixed the table name misspelling issue. The error is SqlException: Invalid column name 'FacilitySettingOverrideSettingId', But I found a similar question at stackoverflow. Maybe I used code first wrongly?

在另一一句话，表的 FacilitySettingOverride ，它没有主键。它是原因？

In another word, the table FacilitySettingOverride, it doesn't have the primary key. Is it the cause?

修改-1

每评论。我重新设计数据库。我认为，设置 - FacilitySettingOverride为1：！[新] [3] 1
[3]

Per comments. I redesigned the DB. I think that Setting-FacilitySettingOverride to be 1:1[![new][3]][3]

和

 [Table("FacilitySettingOverride")]
 public class FacilitySettingOverride
 {
    [Key]
    public int FacilityId { get; set; }

    public string Value { get; set; }
    public int SettingId { get; set; }

    public virtual Facility Facility { get; set; }
    public virtual Setting Setting { get; set; }
}

新误差

The new error is

的SQLException：无效的列名称FacilityId1

中的代码：

    public int GetFacilityBandwidthSetting(int facilityId)
    {
        using (SettingDbContext context = new SettingDbContext())
        {
            var setting = context.Settings.Single(s => s.Name == SettingType.TotalBandwidth.ToString());
            var value = context.FacilitySettingOverride.SingleOrDefault(x => x.FacilityId == facilityId
                && x.SettingId == setting.Id);
            if (value == null)
                return int.Parse(setting.DefaultValue);
            return int.Parse(value.Value);
        }
    }

和

 [Table("Facilities")]
 public class Facility
 {
     [Key]
     public int Id { get; set; }
     public string Name { get; set; }
 }

我能获得设置但 FacilitySettingOverride 在上下文。

推荐答案

的

它们基本上是同样的问题。问题的关键是创建正确的POCO类。

Basically they are same question. The key issue is to create the correct POCO class.

这篇关于设置“扔类型的异常'System.Data.SqlClient.SqlException'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！