实体框架4.1无效列名 [英] entity framework 4.1 invalid column name
本文介绍了实体框架4.1无效列名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
结构NewsComments
public class NewsComment ：BaseComment
{
public int NewsId {get;组; }
public virtual News News {get;组; }
}
但查询返回异常列名News_Id无效。我知道在表不相关的列中创建的这个异常。
CREATE TABLE [dbo]。[NewsComments]（
[Id] [int] IDENTITY（1,1）NOT NULL，
[NewsId] [int] NOT NULL，
[Text] [varchar]（max）NOT NULL，
[ UserId] [int] NOT NULL，
[CommentDate] [datetime] NOT NULL，
[Ip] [varchar]（40）NOT NULL，CONSTRAINT [PK_NewsComments] PRIMARY KEY CLUSTERED（[Id] ASC） WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]）ON [PRIMARY]
BaseComment
public abstract class BaseComment：BasePersistentEntity，ICOMment
{
public int UserId {get;组; }
public virtual BaseUser User {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =CommentText）]
public string Text {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =CommentDate）]
public DateTime CommentDate {get;组; }
public string Ip {get;组;
}
新闻
public class新闻：BaseContent
{
[Display（ResourceType = typeof（NewsResurce），Name =NewsImage）]
public string NewsImage {get;组; }
public virtual ICollection< NewsCommentView> CommentViews {get;组;
}
BaseContent
public abstract class BaseContent：BasePersistentEntity
{
[Display（ResourceType = typeof（FrameworkResurce），Name =Keywords）]
public string关键字{get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =TitleTranslit）]
public string TitleTranslit {get;组; }
[必需（ErrorMessageResourceType = typeof（FrameworkResurce），ErrorMessageResourceName =IsTextEmpty）]
[Display（ResourceType = typeof（FrameworkResurce），Name =Title）]
public string标题{get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =Description）]
public string Description {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =Contents）]
public string Contents {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =DatePublish）]
public DateTime DatePublish {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =AuthorPublish）]
public string AuthorPublish {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =Author）]
public string Author {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =AuthorUrl）]
public string AuthorUrl {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =Views）]
public int Views {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =Comments）]
public int注释{get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =IsComment）]
public bool IsComment {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =SumVote）]
public int SumVote {get;组; }
[Display（ResourceType = typeof（FrameworkResurce），Name =VoteCount）]
public int VoteCount {get;组; }
[NotMapped]
[Display（ResourceType = typeof（FrameworkResurce），Name =Rating）]
public double评分
{
get
{
if（VoteCount> 0）
{
return Math.Round（（float）SumVote / VoteCount，2）;
}
return 0;
}
}
}
查询
private IEnumerable< NewsComment> GetComments（）
{
var news = NewsCommentRepository.AllIncluding（c => c.User，c => c.News）;
返回消息;
}
private DataRepository< NewsComment> NewsCommentRepository
{
get {return DataRepository< NewsComment> .Repository;
}
DataRepository
public class DataRepository< T>其中T：BasePersistentEntity
{
public static DataRepository< T>存储库
{
get
{
返回新的DataRepository< T>（）;
}
}
私有readonly SGNContext< T> context = new SGNContext< T>（）;
public IQueryable< T>全部
{
get {return this.context.Table; }
}
public IQueryable< T> AllIncluding（params Expression< Func< T，object>> [] includeProperties）
{
IQueryable< T> query = this.context.Table;
return includeProperties.Aggregate（query，（current，includeProperty）=> current.Include（includeProperty））;
}
public T Find（int id）
{
return this.context.Table.Find（id）;
}
public void InsertOrUpdate（T country）
{
if（country.Id == default（int））
{
//新实体
this.context.Table.Add（country）;
Save（）;
}
else
{
//现有实体
this.context.Entry（country）.State = EntityState.Modified;
Save（）;
}
}
public void Delete（int id）
{
var country = this.context.Table.Find（id）;
this.context.Table.Remove（country）;
this.Save（）;
}
private void Save（）
{
this.context.SaveChanges（）;
}
}
GetComments / p>
[GridAction]
public ActionResult AjaxCommentsBinding（）
{
return View（new GridModel& NewsComment>
{
Data = GetComments（）
}）;
}
NewsCommentViews
CREATE VIEW [dbo]。[NewsCommentViews]
AS
SELECT dbo.NewsComments.NewsId，dbo.NewsComments.Text，dbo.NewsComments .UserId，dbo.NewsComments.CommentDate，dbo.NewsComments.Ip，
dbo.Roles.RoleName，dbo.Users.UserName，dbo.Users.DateRegistered，dbo.NewsComments.Id，dbo.Users.Avatar
FROM dbo.NewsComments INNER JOIN
dbo.Users ON dbo.NewsComments.UserId = dbo.Users.Id INNER JOIN
dbo.Roles ON dbo.Users.RoleId = dbo.Roles.Id
NewsCommentViews
[Table（NewsCommentViews）]
public class NewsCommentView：NewsComment
{
public string RoleName {get;组; }
public string UserName {get;组; }
public DateTime DateRegistered {get;组; }
public string头像{get;组; }
}
解决方案
问题出在新闻和 NewsCommentView 之间的关系：关系的一端是 News.CommentViews 集合。但另一端则是不是 NewsCommentView.News 。为什么？因为新闻在 NewsCommentView 类中声明，而在基础类 NewsComment 。现在，EF不允许实体参与与该实体类本身但不在基类中的声明的导航属性的关系。
因此，由于您没有Fluent映射，EF仅通过约定来定义所有关系。发生了什么？
-
新闻具有导航属性CommentViews声明并指向NewsCommentView类。 - EF没有找到类型为<$
NewsCommentView中的新闻这是声明。 （有一个，但它在基类中，不计算。） - 所以，EF假定关系的另一端是不暴露 $
NewsCommentViewclass。 - 未公开表示：EF没有导航属性或外键属性，并假定数据库表/视图
NewsCommentViews中必要的外键列将具有标准常规名称。 - 此常规名称为
NameOfEntityClass_PKPropertyName- >News_Id
您的视图中的真实名称是 NewsId 。因此，EF查询不存在的列 News_Id ，因此是异常。
异常可能是当您的MVC视图访问 NewsComment.News.CommentViews 。
您可以修复此问题，因为触发延迟加载在Fluent API中明确指定FK列名称（据我所知，没有其他方式没有Fluent映射）：
public class MyContext：DbContext
{
// ...
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Entity< News> ）
.HasMany（n => n.CommentViews）
.WithRequired（）//< - 没有param，因为没有暴露的关系结束，
// nc => nc.News会抛出一个异常
//因为nc.News在基类
.Map（a => a.MapKey（NewsId））;
}
}
但警告意识到 NewsCommentView.News 是不属于 News.CommentViews 。这意味着如果您的 News.CommentViews 集合中的 NewsCommentView ，则 NewsCommentView.News 不指向新闻对象。另一端是隐形的，不会暴露在模型中。上面的映射只是修复了FK列名问题，但不会改变约定会建立的关系（除了可能将关系改为必需而不是可选）。
I have two table News and NewsComments. I followed the rules of naming
structure NewsComments
public class NewsComment : BaseComment
{
public int NewsId { get; set; }
public virtual News News { get; set; }
}
But query return exception Invalid column name "News_Id". I know what this exception created when in table not related column.
CREATE TABLE [dbo].[NewsComments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[NewsId] [int] NOT NULL,
[Text] [varchar](max) NOT NULL,
[UserId] [int] NOT NULL,
[CommentDate] [datetime] NOT NULL,
[Ip] [varchar](40) NOT NULL, CONSTRAINT [PK_NewsComments] PRIMARY KEY CLUSTERED([Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
BaseComment
public abstract class BaseComment : BasePersistentEntity, IComment
{
public int UserId { get; set; }
public virtual BaseUser User { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "CommentText")]
public string Text { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "CommentDate")]
public DateTime CommentDate { get; set; }
public string Ip { get; set; }
}
News
public class News : BaseContent
{
[Display(ResourceType = typeof(NewsResurce), Name = "NewsImage")]
public string NewsImage { get; set; }
public virtual ICollection<NewsCommentView> CommentViews { get; set; }
}
BaseContent
public abstract class BaseContent : BasePersistentEntity
{
[Display(ResourceType = typeof(FrameworkResurce), Name = "Keywords")]
public string Keywords { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "TitleTranslit")]
public string TitleTranslit { get; set; }
[Required(ErrorMessageResourceType = typeof(FrameworkResurce), ErrorMessageResourceName = "IsTextEmpty")]
[Display(ResourceType = typeof(FrameworkResurce), Name = "Title")]
public string Title { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "Description")]
public string Description { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "Contents")]
public string Contents { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "DatePublish")]
public DateTime DatePublish { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "AuthorPublish")]
public string AuthorPublish { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "Author")]
public string Author { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "AuthorUrl")]
public string AuthorUrl { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "Views")]
public int Views { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "Comments")]
public int Comments { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "IsComment")]
public bool IsComment { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "SumVote")]
public int SumVote { get; set; }
[Display(ResourceType = typeof(FrameworkResurce), Name = "VoteCount")]
public int VoteCount { get; set; }
[NotMapped]
[Display(ResourceType = typeof(FrameworkResurce), Name = "Rating")]
public double Rating
{
get
{
if (VoteCount > 0)
{
return Math.Round((float)SumVote/VoteCount, 2);
}
return 0;
}
}
}
Query
private IEnumerable<NewsComment> GetComments()
{
var news = NewsCommentRepository.AllIncluding(c=>c.User,c=>c.News);
return news;
}
private DataRepository<NewsComment> NewsCommentRepository
{
get { return DataRepository<NewsComment>.Repository; }
}
DataRepository
public class DataRepository<T> where T : BasePersistentEntity
{
public static DataRepository<T> Repository
{
get
{
return new DataRepository<T>();
}
}
private readonly SGNContext<T> context = new SGNContext<T>();
public IQueryable<T> All
{
get { return this.context.Table; }
}
public IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
{
IQueryable<T> query = this.context.Table;
return includeProperties.Aggregate(query, (current, includeProperty) => current.Include(includeProperty));
}
public T Find(int id)
{
return this.context.Table.Find(id);
}
public void InsertOrUpdate(T country)
{
if (country.Id == default(int))
{
// New entity
this.context.Table.Add(country);
Save();
}
else
{
// Existing entity
this.context.Entry(country).State = EntityState.Modified;
Save();
}
}
public void Delete(int id)
{
var country = this.context.Table.Find(id);
this.context.Table.Remove(country);
this.Save();
}
private void Save()
{
this.context.SaveChanges();
}
}
Where used GetComments
[GridAction]
public ActionResult AjaxCommentsBinding()
{
return View(new GridModel<NewsComment>
{
Data = GetComments()
});
}
NewsCommentViews
CREATE VIEW [dbo].[NewsCommentViews]
AS
SELECT dbo.NewsComments.NewsId, dbo.NewsComments.Text, dbo.NewsComments.UserId, dbo.NewsComments.CommentDate, dbo.NewsComments.Ip,
dbo.Roles.RoleName, dbo.Users.UserName, dbo.Users.DateRegistered, dbo.NewsComments.Id, dbo.Users.Avatar
FROM dbo.NewsComments INNER JOIN
dbo.Users ON dbo.NewsComments.UserId = dbo.Users.Id INNER JOIN
dbo.Roles ON dbo.Users.RoleId = dbo.Roles.Id
NewsCommentViews
[Table("NewsCommentViews")]
public class NewsCommentView : NewsComment
{
public string RoleName { get; set; }
public string UserName { get; set; }
public DateTime DateRegistered { get; set; }
public string Avatar { get; set; }
}
解决方案
The problem is in the relationship between News and NewsCommentView: One end of the relationship is the News.CommentViews collection. But the other end is not NewsCommentView.News as you perhaps expect. Why? Because the property News is not declared on the NewsCommentView class but on the base class NewsComment. Now EF doesn't allow that an entity participates in a relationship with a navigation property which is not declared on that entity class itself but only in a base class.
So, because you don't have Fluent mapping EF defines all relationships only by conventions. What happens?
Newshas a navigation propertyCommentViewsdeclared and pointing to theNewsCommentViewclass.- EF does not find an inverse property of type
Newswhich is declared in theNewsCommentViewclass. (There is one but it's in the base class, which doesn't count.) - So, EF assumes the other end of the relationship is not exposed in the
NewsCommentViewclass. - Not exposed means: EF doesn't have a navigation property nor a foreign key property and will assume that the necessary foreign key columns in the database table/view
NewsCommentViewswill have a standard conventional name. - This conventional name is
NameOfEntityClass_PKPropertyName->News_Id
Your real name in the view is NewsId though. So, EF queries for a column News_Id which doesn't exist, hence the exception.
The exception is probably triggered due to lazy loading when your MVC-View accesses NewsComment.News.CommentViews.
You can fix this problem by specifying the FK column name explicitely in Fluent API (as far as I know there no other way without Fluent mapping):
public class MyContext : DbContext
{
// ...
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<News>()
.HasMany(n => n.CommentViews)
.WithRequired() // <- no param because not exposed end of relation,
// nc => nc.News would throw an exception
// because nc.News is in the base class
.Map(a => a.MapKey("NewsId"));
}
}
But caution: Be aware that NewsCommentView.News is not the other end of the relationship belonging to News.CommentViews. It means that if you have a NewsCommentView in your News.CommentViews collection then NewsCommentView.News does not point back to that News object. The other end is invisible and not exposed in the model. The mapping above just fixes the FK column name problem but doesn't change the relationships which conventions would create anyway (except maybe changing the relationship to required instead of optional).
这篇关于实体框架4.1无效列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
