CODEWITHSUNDEEP
c#databaseentity-framework
tmesser
tmesser

Reputation: 7656

Entity Framework: Where the heck is it getting these columns from?

We are trying to get Entity framework working at our shop with an existing database (and therefore, changing the database schema is NOT an option), and the unit tests we created to test things are showing some really strange behavior.

This is the SQL it spits out for a specific object we have:

SELECT 
[Extent1].[CommentTypeId] AS [CommentTypeId], 
[Extent1].[DataPartId] AS [DataPartId], 
[Extent1].[CommentId] AS [CommentId], 
[Extent1].[CreatedTime] AS [CreatedTime], 
[Extent1].[Message] AS [Message], 
[Extent1].[From] AS [From], 
[Extent1].[Likes] AS [Likes], 
[Extent1].[SourceTypeId] AS [SourceTypeId], 
[Extent1].[StatusMessage_DataPartId] AS [StatusMessage_DataPartId], 
[Extent1].[Album_DataPartId] AS [Album_DataPartId]
FROM [dbo].[Comments] AS [Extent1]

The last two columns requested, as you might notice, are not like the others. That's because they don't actually exist, and we have no idea why Entity is requesting them! Neither our configuration files nor our POCOs make any mention of them at all. In fact, as far as our database goes, they're completely separate concepts and aren't directly related at all.

Where is it getting these columns from, and how do I tell it to cut it out?

EDIT: To respond to some of the questions below, 1) We are using Entity Framework 4.2. We are using fluent mapping.

2) The POCO itself looks like this, with the equality mess cut out for the sake of brevity:

public long DataPartId { get; set; }
public string CommentId { get; set; }
public DateTime? CreatedTime { get; set; }
public string Message { get; set; }
public string From { get; set; }
public int? Likes { get; set; }
public string SourceTypeId { get; set; }
public int CommentTypeId { get; set; }

public virtual DataPart DataPart { get; set; }
public virtual CommentType CommentType { get; set; }

3) We are not using edmx. We have a custom DbContext. There are not too many lines that are terribly interesting. These two are probably of interest:

    Configuration.LazyLoadingEnabled = true;
    Configuration.ProxyCreationEnabled = true;

Beyond that, the Context file is a lot of 

modelBuilder.Configurations.Add(new WhateverConfiguration())

and 

public IDbSet<WhateverPoco> PocoDatabaseTableAccessor { get; set; }

4) We started with db-first, but that didn't work, so we're currently doing code-first.

5) This is the guts of the config for that specific POCO:

    HasRequired (x => x.DataPart)
        .WithRequiredDependent (x => x.Comment);

    HasRequired (x => x.CommentType)
        .WithMany (x => x.Comments)
        .HasForeignKey (x => x.CommentTypeId);

    HasKey (x => x.DataPartId);
    ToTable ("Comments", "dbo");

Upvotes: 7

Views: 2482

Answers (3)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

The problem is not in the mapping or class you showed. Check your Album and StatusMessage classes. Are they entities? Are they mapped? Do they have collection navigation properties to comments? If yes EF expects that Comment must have FK to these tables. If the table doesn't have such column you cannot have these navigation properties mapped in those entities.

Btw. Shouldn't the id in Comments table be CommentId instead of DataPartId?

Upvotes: 4

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

Entity Framework, like MVC, uses a lot of convention over configuration. That means it assumes certain things unless you tell it not to.

However, something is really strange here based on the information you supplied. According to the SQL query, this is coming from the Comments table, however your fluent mapping says that DataPartId is the primary key. Do you have additional primary key fluent mappings? If not, your mappings may be wrong. Have you checked the actual database generated to see if the data model matches what you are trying to do?

My guess is that your StatusMessage and Album classes have navigational properties to Comment, but since you have only defined DataPartId as your primary key, that is the value it is using to look up the comments, not CommentId.

Upvotes: 1

slfan
slfan

Reputation: 9129

Open the .edmx in a XML-Editor and search for these columns. They must be somewhere in your model.

EDIT: your original question didn't mention that you are using code first. I wonder what your trouble was with Database first, that usually works fine well. With code first or model first, you normally create the database after creating the model (using generated SQL scripts).

You declared the last two properties as virtual, that's why the generated SQL looks different. From the code you are showing us we cannot see where the reference to Album comes from.

Because you have the database, I would generate the .edmx from the model in one project. Then you can use a POCO code generator or a Self-tracking entity generator to generate the entities and store them in a different project. Or you can write them manually as you already have. The property names must correspond with the columns in the database.

Upvotes: 0

Related Questions