Entity Framework code first foreign key select query issue

Question

I have a POCO which has a foreign key that looks like this

public partial class DataFileInformation
{
    public DataFileInformation()
    {
    }

    public int ID { get; set; }
    public int? HistoryID { get; set; }
    public string FileName { get; set; }
    public int NumberOfLines { get; set; }
    public string UpdatedByPersonnelNumber { get; set; }
    public DateTime UpdatedDateTime { get; set; }    
    public virtual JobHistory History { get; set; }
}

public partial class JobHistory
{    
    public int ID { get; set; }
    public Nullable<DateTime> StartDate { get; set; }
    public Nullable<DateTime> EndDate { get; set; }
    public Nullable<DateTime> UpdatedDateTime { get; set; }    
}

Mappings:

class DataFileUploadInformationEntityMap : EntityTypeConfiguration<DataFileInformation>
{
    public DataFileUploadInformationEntityMap ()
    {
        ToTable("DataFileInformationsView");
        HasKey(fileMetaData => fileMetaData.ID);
        HasOptional(file => file.History)
            .WithMany().HasForeignKey(file => file.HistoryID);
    }
}

public class JobHistoryEntityMap : EntityTypeConfiguration<JobHistory>
{
    public JobHistoryEntityMap()
    {
        ToTable("JobHistoryView");
        HasKey(job => job.ID);
    }
}

Even with the configuration the dynamic query getting fired looks like this

exec sp_executesql N'SELECT  
    [Extent1].[ID] AS [ID],
    [Extent1].[DataFileInformation_ID] AS [DataFileInformation_ID], 
    [Extent1].[FileName] AS [FileName], 
    [Extent1].[UpdatedByPersonnelNumber] AS [UpdatedByPersonnelNumber],
    [Extent1].[UpdatedDateTime] AS [UpdatedDateTime],
    [Extent1].[JobID] AS [LoaderJobID],
    [Extent1].[JobHistory_ID] AS [JobHistory_ID],
    [Extent2].[ID] AS [ID1],
    [Extent2].[StartDate] AS [StartDate],
    [Extent2].[EndDate] AS [EndDate],
    [Extent2].[UpdatedByPersonnelNumber] AS [UpdatedByPersonnelNumber1],
    [Extent2].[CreateDate] AS [CreateDate],
    FROM   [DataFileInformationsView] AS [Extent1]
    LEFT OUTER JOIN [JobHistoryView] AS [Extent2] 
        ON [Extent1].[LoaderJobID] = [Extent2].[ID] 
    WHERE [Extent1].[ID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=146

The dynamic query getting generated is unable to map the JobHistory_ID and the DataFileInformation_ID to the POCO and EF throws an exception unable to find columns

Sergey Berezovskiy · Accepted Answer · 2013-03-27 09:06:43Z

1

You have incorrect class name for DataFileInformation mapping, and property name for optional foreign key:

class FileUploadInformationEntityMap :
    EntityTypeConfiguration<DataFileInformation>
{
    public FileUploadInformationEntityMap()
    {
        ToTable("DataFileUploadInformationEntity");
        HasKey(f => f.ID);
        HasOptional(f => f.History); // here you have LoaderJobHistory
    }
}

Also make sure you are looking at correct SQL, because from SQL I see names DataFileInformationsView and JobHistoryView, but mapping provides table names DataFileUploadInformationEntity and JobHistoryEntity. And you don't need to specify HasOptional - FK will be nullable by default.

After you updated your question, your mapping should work. You can also add cascade deletion configuration (but that is not necessary)

HasOptional(f => f.History)
    .WithMany()
    .HasForeignKey(f => f.HistoryID)
    .WillCascadeOnDelete(false);

edited Mar 27, 2013 at 9:06

answered Mar 27, 2013 at 7:41

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arshad Badar Khan Over a year ago

Sorry i had written the the wrong code. I've edited it o show the problem

Arshad Badar Khan Over a year ago

I tried removing the FK but it still throws the same exception. Am i missing something else

Sergey Berezovskiy Over a year ago

@user761728 be careful when writing questions. Now your code works just fine for me. It generates table with HistoryID (FK, int, null) column. BTW where are you getting your 'dynamic' query from?

Collectives™ on Stack Overflow

Entity Framework code first foreign key select query issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related