设置'抛出一个异常类型'System.Data.SqlClient.SqlException' [英] Settings' threw an exception of type 'System.Data.SqlClient.SqlException'
问题描述
我有异常，无法弄清楚。
Settings ='（（BandwidthRestriction.Models.SettingRespository）settingRespository）我们有两个表。
命名空间BandwidthRestriction.Models
{
[表（设置）]
public class设置
{
[Key]
public int Id {get;组; }
public string Name {get;组; }
public string描述{get;组; }
public string DefaultValue {get;组; }
public string Classification {get;组; }
public virtual FacilitySettingOverride FacilitySettingOverride {get;组; }
}
}
和
命名空间BandwidthRestriction.Models
{
[Table（FacilitySettingOverride）]
public class FacilitySettingOverride
{
[Key]
public int FacilityId {get;组; }
public int SettingId {get;组; }
public string Value {get;组; }
public virtual ICollection< Setting>设置{get;组; }
public virtual ICollection< Facility>设施{get;组; }
}
}
另一个表
命名空间BandwidthRestriction.Models
{
[表（设施）]
public class Facility
{
[Key]
public int Id {get;组; }
public string Name {get;组; }
public ICollection< FacilitySettingOverride> FacilitySettingOverrides {get;组; }
}
}
表的结构喜欢
[！[12] [1]] [1]
另外我有对应的dbcontext作为
public class SettingDbContext：DbContext
{
public DbSet< Setting>设置{get;组; }
public DbSet< FacilitySettingOverride> FacilitySettingOverride {get;组;
protected override void OnConfiguring（DbContextOptionsBuilder optionsBuilder）
{
optionsBuilder.UseSqlServer（Data Source = 11.53.63.94; Initial Catalog = AAA; User ID = sa; password =密码;申请名称= XXX）;
}
protected override void OnModelCreating（ModelBuilder modelBuilder）
{
}
}
在呼吁中，我有
命名空间BandwidthRestriction.Models
{
public class SettingRespository：ISettingRespository
{
public List< Setting> GetAllSettings（）
{
return Settings.ToList（）;
}
public IEnumerable< Setting>设置
{
get
{
列表<设置> settingList;
using（SettingDbContext context = new SettingDbContext（））
{
settingList = context.Settings.ToList（）;
}
return settingList;
}
}
在控制器中，我通过了DI。 p>
[Route（api / [controller]）]
public class BandwidthController：Controller
{
private readonly ISettingRespository _settingRespository;
public BandwidthController（ISettingRespository settingRespository）
{
_settingRespository = settingRespository;
}
但是，当我悬停
_settingRespository。我看到例外：
Settings ='（（BandwidthRestriction.Models.SettingRespository）settingRespository）.Settings'抛出一个类型'System .InvalidOperationException'
编辑：
根据评论，我修复了表名拼写错误的问题。错误是
SqlException：无效的列名称FacilitySettingOverrideSettingId，但是我发现一个类似的问题在 stackoverflow。也许我先错了代码？
在另一个字，表 FacilitySettingOverride ，它没有主键。是原因吗？
EDIT-1
我重新设计了数据库。我认为
Setting-FacilitySettingOverride为1：1
[！[new] [3]] [3]
和
[表（FacilitySettingOverride）]
public class FacilitySettingOverride
{
[Key]
public int FacilityId {get;组; }
public string Value {get;组; }
public int SettingId {get;组; }
public virtual Facility Facility {get;组; }
public virtual设置设置{get;组;
}
新错误是
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（facility）]
public class Facility
{
[Key]
public int Id {get ;组; }
public string Name {get;组;
}
我可以获得
设置但FacilitySettingOverride在上下文中。解决方案
基本上他们是同样的问题。关键问题是创建正确的POCO类。
I got the exception and can't figure it out.
Settings = '((BandwidthRestriction.Models.SettingRespository)settingRespository).Settings' threw an exception of type 'System.Data.SqlClient.SqlException'
I have two tables.
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; } } }And
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]
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) { } }In the respository, I have
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; } }In the controller, I passed the DI.
[Route("api/[controller]")] public class BandwidthController : Controller { private readonly ISettingRespository _settingRespository; public BandwidthController(ISettingRespository settingRespository) { _settingRespository = settingRespository; }However when I hover the
_settingRespository. I see the exception:Settings = '((BandwidthRestriction.Models.SettingRespository)settingRespository).Settings' threw an exception of type 'System.InvalidOperationException'
EDIT:
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?In another word, the table FacilitySettingOverride, it doesn't have the primary key. Is it the cause?
EDIT-1
Per comments. I redesigned the DB. I think that
Setting-FacilitySettingOverride to be 1:1[![new][3]][3]And
[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: Invalid column name 'FacilityId1'.
in the code:
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); } }and
[Table("Facilities")] public class Facility { [Key] public int Id { get; set; } public string Name { get; set; } }I can getting
SettingbutFacilitySettingOverridein the context.解决方案Create POCO class in EF 7 code first
Basically they are same question. The key issue is to create the correct POCO class.
这篇关于设置'抛出一个异常类型'System.Data.SqlClient.SqlException'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
