EntityFramework: Model Configuration with Irregular Names in Plural

Question

I have a system written in ASP.NET MVC4, EntityFramework Code First and Razor. One of the models has the following statements:

public class Flour : IEntityBase
{
    [Key]
    public Guid FlourId { get; set; }
    public Guid ProcessId { get; set; }

    [Display(Name = "Timestamp", ResourceType = typeof(Resources.Language))]
    [Timestamp]
    public Byte[] Timestamp { get; set; }

    [Display(Name = "FlourAnalyzes", ResourceType = typeof(Resources.Language))]
    public virtual ICollection<FlourAnalysis> FlourAnalyzes { get; set; }
    [Display(Name = "Process", ResourceType = typeof(Resources.Language))]
    public virtual Process Process { get; set; }

    [Display(Name = "LastModified", ResourceType = typeof(Resources.Language))]
    public DateTime LastModified { get; set; }
    [Display(Name = "CreatedOn", ResourceType = typeof(Resources.Language))]
    public DateTime CreatedOn { get; set; }
}

As mentioned, Flour has a collection of FlourAnalysis. The model is described below:

[Table(name: "FlourAnalyzes")]
public class FlourAnalysis : IEntityBase
{
    [Key]
    public Guid FlourAnalysisId { get; set; }
    public Guid FlourId { get; set; }
    public Guid? MeshId { get; set; }

    [Display(Name = "Timestamp", ResourceType = typeof(Resources.Language))]
    [Timestamp]
    public Byte[] Timestamp { get; set; }

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd hh:mm}", ApplyFormatInEditMode = true)]
    [Display(Name = "StartTimestamp", ResourceType = typeof(Resources.Language))]
    public DateTime? StartTimestamp { get; set; }

    [Display(Name = "HumidityPercentage", ResourceType = typeof(Resources.Language))]
    [Range(0, 100)]
    public Double HumidityPercentage { get; set; }

    [Display(Name = "StarchPercentage", ResourceType = typeof(Resources.Language))]
    [Range(0, 100)]
    public Double StarchPercentage { get; set; }

    [DataType(DataType.MultilineText)]
    [Display(Name = "Comments", ResourceType = typeof(Resources.Language))]
    public String Comments { get; set; }

    [Display(Name = "Flour", ResourceType = typeof(Resources.Language))]
    public virtual Flour Flour { get; set; }
    [Display(Name = "Mesh", ResourceType = typeof(Resources.Language))]
    public virtual Mesh Mesh { get; set; }

    [Display(Name = "LastModified", ResourceType = typeof(Resources.Language))]
    public DateTime LastModified { get; set; }
    [Display(Name = "CreatedOn", ResourceType = typeof(Resources.Language))]
    public DateTime CreatedOn { get; set; }

    public FlourAnalysis() {
        this.HumidityPercentage = 0;
        this.StarchPercentage = 0;
    }

After generating the migration, EF created a table with name FlourAnalyzes (I need to force the table name, or else EF will create the table in singular). After inserting into it some data, EF doesn't bring FlourAnalysis data calling Flour objects via context:

[Authorize]
public ViewResult Details(System.Guid id)
{
    var flour = context.Flours
        .Include(f => f.FlourAnalyzes)
        .Single(x => x.FlourId == id);

    return View(flour);
}

EDIT:

After some suggestions, I changed the .Single() expression to .Where() and the generated SQL points to a column that should not even exist, Flour_ProcessId:

{SELECT 
[Project1].[C1] AS [C1], 
[Project1].[ProcessId] AS [ProcessId], 
[Project1].[FlourId] AS [FlourId], 
[Project1].[Timestamp] AS [Timestamp], 
[Project1].[LastModified] AS [LastModified], 
[Project1].[CreatedOn] AS [CreatedOn], 
[Project1].[Mesh_MeshId] AS [Mesh_MeshId], 
[Project1].[C2] AS [C2], 
[Project1].[FlourAnalysisId] AS [FlourAnalysisId], 
[Project1].[FlourId1] AS [FlourId1], 
[Project1].[MeshId] AS [MeshId], 
[Project1].[Timestamp1] AS [Timestamp1], 
[Project1].[StartTimestamp] AS [StartTimestamp], 
[Project1].[HumidityPercentage] AS [HumidityPercentage], 
[Project1].[StarchPercentage] AS [StarchPercentage], 
[Project1].[Comments] AS [Comments], 
[Project1].[LastModified1] AS [LastModified1], 
[Project1].[CreatedOn1] AS [CreatedOn1], 
[Project1].[Flour_ProcessId] AS [Flour_ProcessId]
FROM ( SELECT 
    [Extent1].[ProcessId] AS [ProcessId], 
    [Extent1].[FlourId] AS [FlourId], 
    [Extent1].[Timestamp] AS [Timestamp], 
    [Extent1].[LastModified] AS [LastModified], 
    [Extent1].[CreatedOn] AS [CreatedOn], 
    [Extent1].[Mesh_MeshId] AS [Mesh_MeshId], 
    1 AS [C1], 
    [Extent2].[FlourAnalysisId] AS [FlourAnalysisId], 
    [Extent2].[FlourId] AS [FlourId1], 
    [Extent2].[MeshId] AS [MeshId], 
    [Extent2].[Timestamp] AS [Timestamp1], 
    [Extent2].[StartTimestamp] AS [StartTimestamp], 
    [Extent2].[HumidityPercentage] AS [HumidityPercentage], 
    [Extent2].[StarchPercentage] AS [StarchPercentage], 
    [Extent2].[Comments] AS [Comments], 
    [Extent2].[LastModified] AS [LastModified1], 
    [Extent2].[CreatedOn] AS [CreatedOn1], 
    [Extent2].[Flour_ProcessId] AS [Flour_ProcessId], 
    CASE WHEN ([Extent2].[FlourAnalysisId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM  [dbo].[Flours] AS [Extent1]
    LEFT OUTER JOIN [dbo].[FlourAnalyzes] AS [Extent2] ON [Extent1].[ProcessId] = [Extent2].[Flour_ProcessId]
WHERE [Extent1].[FlourId] = @p__linq__0
)  AS [Project1]
ORDER BY [Project1].[ProcessId] ASC, [Project1].[C2] ASC}

What I'm doing wrong?

Answer 1

To avoid the error you're getting regarding Process_ProcessId you need to add an attribute:

[ForeignKey("ProcessId")]
public virtual Process Process { get; set; }

This is because the convention for generating foreign keys would use the wrong column name.

You probably also need something like this in your FlourAnalysis class:

[ForeignKey("FlourId")]
public virtual Flour Flour { get; set; }

[ForeignKey("MeshId")]
public virtual Mesh Mesh { get; set; }

Note that, in all cases, I've omitted the other attributes from your code just to highlight what I've added.

Here is an old-ish article that explains how you use the attribute.

A useful tip for working with pre-existing databases to see whether you've defined your mappings correctly is to install the EF Power Tools and use the View Entity Data Model DDL SQL option to see what EF thinks your database looks like. Where the generated SQL does not match your actual database, you know you need to modify your model annotations or configuration.

Answer 2

By default, the Entity Framework will assume that all of the names of your tables in your database are either pluralised, or in the case of code first, you would like them to be pluralised when created.

check this hope it will help

http://edspencer.me.uk/2012/03/13/entity-framework-plural-and-singular-table-names/