EF6 Code First - Configure One-to-One relationship with unmatched foreign keys

Question

I'm using Code First to define the schema of an existing database. The problem I'm having is that they key names in the tables don't quite align. This works fine with one-to-many relationships because I can use the HasForeignKey() method, but there does't seem to be an equivalent for one-to-one relationships. My table definitions are:

namespace Data.Mappings {

    internal class DocumentTypeConfiguration : EntityTypeConfiguration<Document> {

        public DocumentTypeConfiguration() {
            ToTable("ProsDocs");

            HasKey(m => m.Id);

            Property(m => m.Id)
                .HasColumnName("ProsDocId")
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            // ---- This is the foreign key ----
            Property(m => m.TypeId)
                .HasColumnName("ProsDocTypeId")
                .HasMaxLength(3);

            HasRequired(d => d.DocType)
                .WithRequiredDependent(dt => dt.Document);
                // I need to specify here that the foreign key is named "DocTypeId" and not "DocType_Id"
        }
    }

    internal class DocTypeTypeConfiguration : EntityTypeConfiguration<DocType> {

        public DocTypeTypeConfiguration() {
            ToTable("DocType");

            HasKey(m => m.Id);

            // ---- This is the "child" end of the foreign key ----
            Property(m => m.Id)
                .HasColumnName("DocTypeId")
                .HasMaxLength(4);

            Property(m => m.FullName)
                .HasColumnName("DocTypeDesc")
                .HasMaxLength(255);

            Property(m => m.Priority)
                .HasColumnName("DocPriority");

            // Or would it be easier to define it here?
//            HasRequired(m => m.Document)
//                .WithRequiredDependent(d => d.DocType);
        }
    }
}

To clarify the model, each Document has a DocType, with the foreign key relationship of Document.ProsDocTypeId --> DocType.Id.

Is there a way to define what the column names are for keys for one-to-one relationships?

EDIT: I guess my schema isn't clear.

dbo.DocType
-----------
DocTypeId char(4) (PK)
DocTypeDesc varchar(255)

dbo.ProsDocs
------------
ProsDocId int (PK)
ProsDocTypeId char(4)

Basically, I need the resulting query to look like this:

SELECT 
    [Extent1].[ProsDocId] AS [ProsDocId], 
    [Extent2].[DocTypeId] AS [DocTypeId]
    FROM  [dbo].[ProsDocs] AS [Extent1]
    LEFT OUTER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocTypeId] = [Extent2].[DocTypeId]
    WHERE [Extent1].[ProsId] = @EntityKeyValue1

But instead, because EF assumes I want to use the primary key (dbo.ProsDocs.ProsDocId) instead of a foreign key (dbo.ProsDocs.DocTypeId), the query it generates is this:

SELECT 
    [Extent1].[ProsDocId] AS [ProsDocId], 
    [Extent2].[DocTypeId] AS [DocTypeId]
    FROM  [dbo].[ProsDocs] AS [Extent1]
    LEFT OUTER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocId] = [Extent2].[ProsDocTypeId]
    WHERE [Extent1].[ProsId] = @EntityKeyValue1

The difference lies here:

Ideal query:

LEFT OUTER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocTypeId] = [Extent2].[DocTypeId]

Current query:

LEFT OUTER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocId] = [Extent2].[ProsDocTypeId]

I need to create a one-to-one relationship between dbo.ProsDocs.ProsDocTypeId and dbo.DocType.DocTypeId. The problem EF is having with it is that it wants to create the relationship using only the primary keys, not foreign keys. How do I specify the column name of the foreign key such that each Document has exactly one DocType?

Answer 1

If the Document is the principal and DocType is the dependent, then you either need to

• Have this configuration on Document

  HasRequired(d => d.DocType).WithRequiredPrincipal(dt => dt.Document);
  

• Or this configuration on DocType

  HasRequired(dt => dt.Document).WithRequiredDependent(d => d.DocType);
  

And remove TypeId / ProsDocTypeId property from Document as the principal can't have foreign key id to dependent, unless it's not a constraint, just a normal column.

update

Entity:

public class Document
{
    public string Id { get; set; }
    // This entity is a principal. It can't have foreign key id to dependent.
    // public string TypeId { get; set; }
    public DocType DocType { get; set; }
}

Linq:

db.Documents.Include(d => d.DocType)

Query:

 SELECT
    1 AS [C1],
    [Extent1].[ProsDocId] AS [ProsDocId],
    [Extent2].[DocTypeId] AS [DocTypeId],
    FROM  [dbo].[ProsDocs] AS [Extent1]
    LEFT OUTER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocId] = [Extent2].[DocTypeId]

update 2

To achieve your ideal query, the relationship that you needed is one to many. One DocType can have many Document.

public class Document
{
    public string Id { get; set; }
    public string TypeId { get; set; }
    public DocType DocType { get; set; }
}
public class DocType
{
    public string Id { get; set; }
    public string FullName { get; set; }
    public string Priority { get; set; }
    public ICollection<Document> Documents { get; set; }
}

The configuration on Document.

Change this:

HasRequired(d => d.DocType).WithRequiredDependent(dt => dt.Document);

Into:

HasRequired(d => d.DocType).WithMany(dt => dt.Documents).HasForeignKey(d => d.TypeId);

Linq:

db.Documents.Include(d => d.DocType)

Query:

SELECT
    1 AS [C1],
    [Extent1].[ProsDocId] AS [ProsDocId],
    [Extent1].[ProsDocTypeId] AS [ProsDocTypeId],
    [Extent2].[DocTypeId] AS [DocTypeId],
    FROM  [dbo].[ProsDocs] AS [Extent1]
    INNER JOIN [dbo].[DocType] AS [Extent2] ON [Extent1].[ProsDocTypeId] = [Extent2].DocTypeId]