Entity Framework 7 creates an unneeded alternate key

Question

I have two simple classes using Entity Framework 7, everything is working fine on all classes, except one case where Entity Framework generates a new alternate key with same name as already existing column.

Invoice.cs

namespace Arinsys.Accounts
{
    [Table(name: "Invoice", Schema = "Arinsys_Accounts")]
    public class Invoice
    {
        public long ID { get; set; }
        public string InvoiceID { get; set; }
        public long? CustomerUID { get; set; }
        public long? EmployeeUID { get; set; }
        public DateTime BillDateTime { get; set; }
        public long Amount { get; set; }
        public long? Discount { get; set; }
        public string DiscountReason { get; set; }
        public string CouponID { get; set; }
        public long Charges { get; set; }
        public string ChargesReason { get; set; }
        public long Tax { get; set; }
        public string TaxType { get; set; }
        public long FinalAmount { get; set; }
        public string Status { get; set; }
        public string Remarks { get; set; }

        [ForeignKey("CustomerUID")]
        public virtual CRM.User Customer { get; set; }
        [ForeignKey("EmployeeUID")]
        public virtual CRM.User BilledBy { get; set; }

        public virtual ICollection<InvoiceItems> Items { get; set; }
        public virtual ICollection<Payments> Payments { get; set; }
    }
}

Generated Invoice table code

CREATE TABLE [Arinsys_Accounts].[Invoice] (
    [ID]             BIGINT         IDENTITY (1, 1) NOT NULL,
    [Amount]         BIGINT         NOT NULL,
    [BillDateTime]   DATETIME2 (7)  NOT NULL,
    [Charges]        BIGINT         NOT NULL,
    [ChargesReason]  NVARCHAR (MAX) NULL,
    [CouponID]       NVARCHAR (MAX) NULL,
    [CustomerUID]    BIGINT         NULL,
    [Discount]       BIGINT         NULL,
    [DiscountReason] NVARCHAR (MAX) NULL,
    [EmployeeUID]    BIGINT         NULL,
    [FinalAmount]    BIGINT         NOT NULL,
    [InvoiceID]      NVARCHAR (MAX) NOT NULL,
    [InvoiceID1]     NVARCHAR (450) NOT NULL,
    [Remarks]        NVARCHAR (MAX) NULL,
    [Status]         NVARCHAR (MAX) NULL,
    [Tax]            BIGINT         NOT NULL,
    [TaxType]        NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [AK_Invoice_InvoiceID1] UNIQUE NONCLUSTERED ([InvoiceID1] ASC),
    CONSTRAINT [FK_Invoice_User_CustomerUID] FOREIGN KEY ([CustomerUID]) REFERENCES [Arinsys_CRM].[User] ([ID]),
    CONSTRAINT [FK_Invoice_User_EmployeeUID] FOREIGN KEY ([EmployeeUID]) REFERENCES [Arinsys_CRM].[User] ([ID])
);

InvoiceItems.cs

namespace Arinsys.Accounts
{
    [Table(name: "InvoiceItems",Schema = "Arinsys_Accounts")]
    public class InvoiceItems
    {
        public long ID { get; set; }
        public string InvoiceID { get; set; }
        public string ItemID { get; set; }
        public int Quantity { get; set; }
        public int Price { get; set; }
        public int Discount { get; set; }
        public string Reason { get; set; }
        public int Amount { get; set; }
        public string Remarks { get; set; }

        [ForeignKey("InvoiceID")]
        public virtual Invoice Invoice { get; set; }

    }
}

Generated InvoiceItems table code

CREATE TABLE [Arinsys_Accounts].[InvoiceItems] (
    [ID]        BIGINT         IDENTITY (1, 1) NOT NULL,
    [Amount]    INT            NOT NULL,
    [Discount]  INT            NOT NULL,
    [InvoiceID] NVARCHAR (450) NULL,
    [ItemID]    NVARCHAR (MAX) NULL,
    [Price]     INT            NOT NULL,
    [Quantity]  INT            NOT NULL,
    [Reason]    NVARCHAR (MAX) NULL,
    [Remarks]   NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_InvoiceItems] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_InvoiceItems_Invoice_InvoiceID] FOREIGN KEY ([InvoiceID]) REFERENCES [Arinsys_Accounts].[Invoice] ([InvoiceID1])
);

Notice the InvoiceID1 column in Invoice Table, it's created automatically, and I don't require it. Since these models are in a library i.e. they are just like templates, actual dbcontext will be in the application and i don't want the end developer to be worried with these things, so fluent API in OnModelCreating is not an option.

As to the next obvious question, how will we manage the references, the overall structure and interfaces are designed in such a way that it's taken care of. Anyways that discussion will go out of scope of current question.

I agree, i can see the foreign Key set to InvoiceID1 in InvoiceItems Table, but i want it to link to ID column in Invoice Table, If i add an [InverseProperty("ID")] Attribute along with existing [ForeignKey("InvoiceID")] Attribute on Invoice object in InvoiceItems class an error shows up

ArgumentNullException: Value cannot be null. Parameter name: type

At the time of writing Entity Framework is still pretty new and documentation is still not complete, and all answers on Stackoverflow and other places tells the answer as to how to do it i.e. how to create an alternate/unique key, but unfortunately I can't find any which answers how to prevent it.

EDIT: Though this question was a programming mistake on my part as answered by @Jcl but we have received some good answers e.g. @Steve Greene posted about String Length Limit in EF SQL, which might be helpful to future reader, so i am leaving the question as is

Answer 1

The problem is that you are saying that your InvoiceItem has a reference (Invoice) which has a foreign key (InvoiceID, which is of type string).

However the ID of the Invoice is a long, not a string, so it's making its own string key to comply with your requirements.

My guess: if you change the public string InvoiceID { get; set; } in InvoiceItem to: public long InvoiceID { get; set; } it should stop creating the key, plus, you'll actually get the correct foreign key.

Answer 2

EF(SQL) can't index strings over 900 bytes (450 unicode) so it creates one for you. Change your model to:

[StringLength(450)]
public string InvoiceID { get; set; }

Or use a similar fluent command to limit it. Entity Framework code first unique column