EF代码首先为模型中不存在的查询添加额外的列 [英] EF Code First adds extra column to query that doesn't exist in model anymore
问题描述
这是我的第一个问题。
我有一个ASP.NET 4.0 MVC3项目，它使用EF Code First作为ORM和FluentMigrator进行版本迁移。在那里我有Message实体类，如下所示：
public class Message
{
[Key ]
[Column（Message_Id）]
public int Id {get;组; }
public DateTime CreatedTime {get;组;
[必需]
[StringLength（MaxSubjectLength）]
public string Subject {get;组;
[必需]
[StringLength（MaxBodyLength）]
public string Body {get;组; }
public Link Link {get; set;
}
没有定义自定义映射，MSSQL Server 2012数据库表消息： / p>
CREATE TABLE [dbo]。[messages]（
[Message_Id] [int] IDENTITY（1,1）NOT NULL，
[CreatedTime] [datetime] NOT NULL，
[主题] [nvarchar]（78）NOT NULL，
[BodyHtml] [nvarchar]（2000）NOT NULL，
[Link_Id] [int] NULL，
[Collection_Id] [int] NULL，
[MessageType] [nvarchar]（30）NOT NULL，
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
（
[Message_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 ]
GO
ALTER TABLE [dbo]。[消息] WITH CHECK ADD CONSTRAINT [FK_Messages_Collection] FOREIGN KEY（[Collection_Id]）
参考文献[dbo]。 [集合]（[Id]）
GO
ALTER TABLE [dbo]。[Messages] CHECK CONSTRAINT [FK_Messages_Collection]
GO
ALTER TABLE [dbo]。[消息] WITH CHECK ADD CONSTRAINT [FK_Messages_Link] FOREIGN KEY（[Link_Id]）
参考[dbo]。[Links]（[Id ]）
GO
ALTER TABLE [dbo]。[Messages]检查约束[FK_Messages_Link]
GO
Link_Id，Collection_Id和MessageType列在该表中，因为我想做一个TPH继承（实际上我想做一个TPT继承，然后切换到TPH，我猜这会解决我的问题，但没有）。
现在我想要有LinkMessage和CollectionMessage类应该是这样的：
public class LinkMessage：Message
{
public int？ Link_Id {get; set; }
}
public class CollectionMessage：Message
{
public int？ Collection_Id {get; set; }
}
所以问题是：即使没有当我删除Link导航属性，重建我的项目（甚至重新创建数据库等）并执行简单的查询 var a = DBContext.Messages.ToList（）时，继承被定义（即只存在Message实体） ;
EF生成以下查询：
SELECT/ pre>
[Extent1] 。[Message_Id] AS [Message_Id]，
[Extent1]。[CreatedTime] AS [CreatedTime]，
[Extent1]。[Subject] AS [Subject]，
[Extent1] [BodyHtml] AS [BodyHtml]，
[Extent1]。[Link_Id] AS [Link_Id]
FROM [dbo]。[Messages] AS [Extent1]
为什么还包括Link_Id？它不再是模型了。这将导致继承问题 - 当我使用上面的子类进行TPH（或TPT）继承时，应用程序失败，错误'列名无效'Link_Id1''。为什么是Link_Id1？怎么会这样呢？我很困惑'Collection_Id'列的行为正常。这两列是相同的。 我尝试重新创建数据库，杀死所有进程的应用程序（甚至重新启动我的电脑），尝试加载以前的版本，并删除了Message.Link_Id属性，尝试使用数据和没有相同的行为。我试图google的东西，但最终没有，因为实际上我甚至不知道如何使正确的搜索查询和搜索，我已经给了我什么，我正在花费第二天...
以下是它们生成的查询和SQL：
var b = DBContext.Messages。 OfType< CollectionMessage>（）;
SELECT
[Extent1]。[Message_Id] AS [Message_Id]，
'0X0X'AS [C1]，
[Extent1]。[CreatedTime] AS [创建时间]
[Extent1]。[主题] AS [主题]，
[Extent1]。[BodyHtml] AS [BodyHtml]，
[Extent1]。[Collection_Id] AS [Collection_Id] ，
[Extent1]。[Link_Id1] AS [Link_Id1]
FROM [dbo]。[Messages] AS [Extent1]
WHERE [Extent1]。[MessageType] = N'CollectionMessage'
var b = DBContext.Messages.OfType< LinkMessage>（）;
SELECT
[Extent1]。[Message_Id] AS [Message_Id]，
'0X0X'AS [C1]，
[Extent1]。[CreatedTime] AS [创建时间]
[Extent1]。[主题] AS [主题]，
[Extent1]。[BodyHtml] AS [BodyHtml]，
[Extent1]。[Link_Id] AS [Link_Id] ，
[Extent1]。[Link_Id1] AS [Link_Id1]
FROM [dbo]。[Messages] AS [Extent1]
WHERE [Extent1]。[MessageType] = N'LinkMessage'
var b = DBContext.Messages;
SELECT
[Extent1]。[Message_Id] AS [Message_Id]，
CASE WHEN（（（CASE WHEN（[Extent1]。[MessageType] = N'LinkMessage'） THEN（1作为位）ELSE转换（0作为位）END）> cast（1作为位））AND（（CASE WHEN（[Extent1]。[MessageType] = N'CollectionMessage'）THEN cast作为位）ELSE转换（0作为位）END）> cast（1作为位）））THEN'0X'WHEN（[Extent1]。[MessageType] = N'LinkMessage'）THEN'0X0X'ELSE'0X1X 'END AS [C1]，
[Extent1]。[CreatedTime] AS [CreatedTime]，
[Extent1]。[主题] AS [主题]，
[Extent1]。[BodyHtml] AS [BodyHtml]，
CASE WHEN（（（CASE WHEN（[Extent1]。[MessageType] = N'LinkMessage'）THEN cast（1 as bit）ELSE cast（0 as bit）END）<转换（1作为位））AND（（CASE WHEN（[Extent1]。[MessageType] = N'CollectionMessage'）THEN cast（1 as bit）ELSE cast（0作为位）END）< bit）））THEN CAST（NULL AS int）WHEN（[Extent1]。[MessageType] = N'LinkMessage'）THEN [Extent1]。[Link_Id] END AS [C2]，
CASE WHEN（（CASE WHEN（[Extent1]。[MessageType] = N'LinkMessage'）THEN cast（1 as bit）ELSE cast（0 as bit）END）<转换（1作为位））AND（（CASE WHEN（[Extent1]。[MessageType] = N'CollectionMessage'）THEN cast（1 as bit）ELSE cast（0作为位）END）<位[]）[END] [END] [END] [END] [END] [END] [END] [C] b [Extent1]。[Link_Id1] AS [Link_Id1]
FROM [dbo]。[Messages] AS [Extent1]
为什么它会查询Link_Id和Link_Id1列？有人帮助我，我失踪了什么？
UPD：当我删除自定义DBInitializer并使Code Code为我创建DB在消息表中创建额外的列Link_Id1。
解决方案对不起，我知道应该是一些小愚蠢的错误没想到会是那么愚蠢。实际上，
链接实体收集了消息。这个属性没有被使用太多，所以我们没有注意到这个属性。当我删除它 - 所有的开始工作。
所以它开始像非常有趣的问题，但结束了非常明显的答案 - 人为因素
对于所有新来的人，并且都落在同一个洞穴中，我会留下一个注释：EF不能记住一些属性，除非有引用给它所以如果你遇到类似的麻烦，请仔细检查你的代码，并请别人做，没有魔法！
我现在不能upvote，所以 soadyp ， Gert Arnold - 感谢您的意愿帮助！
This is my first question on SO.
I have an ASP.NET 4.0 MVC3 project that is using EF Code First as ORM and FluentMigrator for version migrations. And there I have Message entity class that looks like this:
public class Message { [Key] [Column("Message_Id")] public int Id { get; set; } public DateTime CreatedTime { get; set; } [Required] [StringLength(MaxSubjectLength)] public string Subject { get; set; } [Required] [StringLength(MaxBodyLength)] public string Body { get; set; } public Link Link { get;set; } }with no custom mappings defined, and MSSQL Server 2012 database table Messages:
CREATE TABLE [dbo].[Messages]( [Message_Id] [int] IDENTITY(1,1) NOT NULL, [CreatedTime] [datetime] NOT NULL, [Subject] [nvarchar](78) NOT NULL, [BodyHtml] [nvarchar](2000) NOT NULL, [Link_Id] [int] NULL, [Collection_Id] [int] NULL, [MessageType] [nvarchar](30) NOT NULL, CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ( [Message_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] GO ALTER TABLE [dbo].[Messages] WITH CHECK ADD CONSTRAINT [FK_Messages_Collection] FOREIGN KEY([Collection_Id]) REFERENCES [dbo].[Collections] ([Id]) GO ALTER TABLE [dbo].[Messages] CHECK CONSTRAINT [FK_Messages_Collection] GO ALTER TABLE [dbo].[Messages] WITH CHECK ADD CONSTRAINT [FK_Messages_Link] FOREIGN KEY([Link_Id]) REFERENCES [dbo].[Links] ([Id]) GO ALTER TABLE [dbo].[Messages] CHECK CONSTRAINT [FK_Messages_Link] GOLink_Id, Collection_Id and MessageType columns are in that table because I want to do a TPH inheritance (actually I wanted to do a TPT inheritance and then switched to TPH, I guessed it would fix my problem but it didn't).
For now I want to have LinkMessage and CollectionMessage classes that should look like this:
public class LinkMessage : Message { public int? Link_Id { get;set; } } public class CollectionMessage : Message { public int? Collection_Id { get;set; } }So the problem is: Even when no inheritance is defined (i.e. just Message entity exists) when I remove the Link navigation property, rebuild my project, (even recreate database, etc.) and do simple query
var a = DBContext.Messages.ToList();EF generates the following query:SELECT [Extent1].[Message_Id] AS [Message_Id], [Extent1].[CreatedTime] AS [CreatedTime], [Extent1].[Subject] AS [Subject], [Extent1].[BodyHtml] AS [BodyHtml], [Extent1].[Link_Id] AS [Link_Id] FROM [dbo].[Messages] AS [Extent1]Why does it still include Link_Id? It is not in model anymore. This will cause problems with inheritance - when I make TPH (or TPT) inheritance with subclasses above, app fails with error 'Invalid column name 'Link_Id1''. Why Link_Id1? How can this happen? I'm totally confused. 'Collection_Id' column behaves normally. These two columns are identical. I tried to recreate database, killed all processes of app (even rebooted my pc), tried to load previous revision and deleted Message.Link_Id property there, tried with data and without - same behaviour. I tried to google something but end up with nothing because actually I don't even know how to make proper search query and searches I've made gave me nothing, I am spending second day with it...
Here are the queries and SQL they are producing:
var b = DBContext.Messages.OfType<CollectionMessage>(); SELECT [Extent1].[Message_Id] AS [Message_Id], '0X0X' AS [C1], [Extent1].[CreatedTime] AS [CreatedTime], [Extent1].[Subject] AS [Subject], [Extent1].[BodyHtml] AS [BodyHtml], [Extent1].[Collection_Id] AS [Collection_Id], [Extent1].[Link_Id1] AS [Link_Id1] FROM [dbo].[Messages] AS [Extent1] WHERE [Extent1].[MessageType] = N'CollectionMessage' var b = DBContext.Messages.OfType<LinkMessage>(); SELECT [Extent1].[Message_Id] AS [Message_Id], '0X0X' AS [C1], [Extent1].[CreatedTime] AS [CreatedTime], [Extent1].[Subject] AS [Subject], [Extent1].[BodyHtml] AS [BodyHtml], [Extent1].[Link_Id] AS [Link_Id], [Extent1].[Link_Id1] AS [Link_Id1] FROM [dbo].[Messages] AS [Extent1] WHERE [Extent1].[MessageType] = N'LinkMessage' var b = DBContext.Messages; SELECT [Extent1].[Message_Id] AS [Message_Id], CASE WHEN (((CASE WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit)) AND ((CASE WHEN ([Extent1].[MessageType] = N'CollectionMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit))) THEN '0X' WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN '0X0X' ELSE '0X1X' END AS [C1], [Extent1].[CreatedTime] AS [CreatedTime], [Extent1].[Subject] AS [Subject], [Extent1].[BodyHtml] AS [BodyHtml], CASE WHEN (((CASE WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit)) AND ((CASE WHEN ([Extent1].[MessageType] = N'CollectionMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit))) THEN CAST(NULL AS int) WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN [Extent1].[Link_Id] END AS [C2], CASE WHEN (((CASE WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit)) AND ((CASE WHEN ([Extent1].[MessageType] = N'CollectionMessage') THEN cast(1 as bit) ELSE cast(0 as bit) END) <> cast(1 as bit))) THEN CAST(NULL AS int) WHEN ([Extent1].[MessageType] = N'LinkMessage') THEN CAST(NULL AS int) ELSE [Extent1].[Collection_Id] END AS [C3], [Extent1].[Link_Id1] AS [Link_Id1] FROM [dbo].[Messages] AS [Extent1]Why does it querying Link_Id and Link_Id1 columns? Someone help me please, what am I missing?
UPD: When I remove custom DBInitializer and make Code First to create DB for me, it creates extra column 'Link_Id1' in 'Messages' table.
解决方案Sorry guys, I knew it should be some small stupid mistake but didn't thought it'd be that stupid. Actually a
Linkentity had collection ofMessagesin it. This property wasn't used much so we didn't notice it amongst other properties. And when I removed it - all started to work.So it started like very interesting question but end up with very obvious answer - human factor.
To all who are new to EF and are falling in the same cave I'll leave a note: EF can't "remember" some properties unless it has reference to it. So if you do experience similar troubles, please double check your code and ask someone else to do it, there is no magic!
I can't upvote now, so soadyp, Gert Arnold - thank you for your will to help!
这篇关于EF代码首先为模型中不存在的查询添加额外的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
