EF Code First adds extra column to query that does

Question

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]
GO

Link_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.

Answer 1

If you only have Message and (necessarily Link) in your model there will always be a column Link_Id to accommodate Message.Link.

If you've also got the derived classes off Message in your model the Link_Id that is always generated is for the propertyLinkMessage.Link_Id. You can't have Link in the base class and the foreign key value in a derived class. Now EF just notices that there is a property LinkMessage.Link_Id and besides that a navigation property Link. For the latter, Link_Id is not available any more, so EF created Link_Id1.

You have to put both Link and Link_Id in the same class to make EF see them as two parts of one foreign key (a foreign key association). It could look like this:

public class LinkMessage : Message
{
    [ForeignKey("Link")]
    public int? Link_Id { get; set; }
    public Link Link { get; set; }
}

Answer 2

start here http://msdn.microsoft.com/en-us/data/jj591583.aspx Any chance the

public Link Link { get;set; }

is dragging in Link_Id

Answer 3

Sorry guys, I knew it should be some small stupid mistake but didn't thought it'd be that stupid. Actually a Link entity had collection of Messages in 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!