Create composite in multiple foreign key using entity framework

Question

In multi tenant application, we can force the ID that is in another tenant in dropdown for example

My models is:

public class Tenant
{
    public int Id { get; set; }
    public string Name { get; set; }
}
public class Customer
{
    public int Id { get; set; }
    public int TenantId { get; set; }
    public string Name { get; set;}
}
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int TenantId { get; set; }

    public float Price { get; set; }
}

this code should generate a table similar to this:

CREATE TABLE tenant (
    id INT NOT NULL,
    nome VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE customer (
    id INT  NOT NULL,
    id_tenant INT  NOT NULL,
    nome VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id_tenant) REFERENCES tenant(id),

    -- pra ter a FK composta, tem que ter chave composta na tabela de origem
    UNIQUE (Id,id_tenant)
);

CREATE TABLE [order]  (
    id INT  NOT NULL,
    id_customer INT  NOT NULL,
    id_tenant INT  NOT NULL,
    nome VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id_customer, id_tenant) REFERENCES customer (id, id_tenant),
    FOREIGN KEY (id_tenant) REFERENCES tenant(id)
);

But, how create composite using FLUENT API?

EDIT: I want to ensure that the CustomerID passed to the Order.cs has the same TenantId

That is, Customer and Order must have the same TenantId

Possible duplicate of https://stackoverflow.com/questions/15454696/entity-framework-multiple-column-as-primary-key-by-fluent-api — David Browne - Microsoft, Sep 09 '17 at 18:08

score 0 · Accepted Answer · answered Sep 10 '17 at 13:15

I want to ensure that the CustomerID passed to the Order.cs has the same TenantId

To declare a compound foreign key in the Fludent API is just like declaring a compound primary key. Use an anonymous type.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApp8
{

    public class Tenant
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class Customer
    {
        public int Id { get; set; }
        public int TenantId { get; set; }
        public virtual Tenant Tenant {get;set;}
        public string Name { get; set; }
    }
    public class Order
    {
        public int Id { get; set; }
        public int CustomerId { get; set; }
        public int TenantId { get; set; }

        public Customer Customer { get; set; }
        public Tenant Tenant { get; set; }

        public decimal Price { get; set; }
    }

    class Db : DbContext
    {


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Tenant>().HasKey(e => e.Id);
            modelBuilder.Entity<Customer>().HasKey(e => new { e.TenantId, e.Id });
            modelBuilder.Entity<Order>().HasKey(e => new { e.TenantId, e.CustomerId, e.Id });

            modelBuilder.Entity<Customer>()
                        .HasRequired<Tenant>(e => e.Tenant)
                        .WithMany()
                        .HasForeignKey(e => e.TenantId);

            modelBuilder.Entity<Order>()
                        .HasRequired<Customer>(e => e.Customer)
                        .WithMany()
                        .HasForeignKey(e => new { e.TenantId, e.CustomerId });

            modelBuilder.Entity<Order>()
                        .HasRequired<Tenant>(e => e.Tenant)
                        .WithMany()
                        .HasForeignKey(e => e.TenantId)
                        .WillCascadeOnDelete(false);

        }
    }



    class Program
    {      

        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);

                db.Database.Initialize(true);






            }


            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();


        }
    }
}

Why PK in all FK in Order Table? Composite need only unique(Id) — Marcelo Dias, Sep 11 '17 at 15:03
You could go either way with that. You need an index on starting with (CustomerID,TenantId) on Order to support the FK. So it's more efficient to just add OrderId to that and make it the PK. Of course that doesn't enable you to do efficient lookup by just OrderID or enforce the uniqueness of OrderID. If you want that then an PK on OrderID makes sense. — David Browne - Microsoft, Sep 11 '17 at 15:16

Create composite in multiple foreign key using entity framework

1 Answers1