Using EF Core Code First with no foreign keys

Question

I am trying to design an application using EF Core 2.1, using code-first approach but I do not want Foreign Keys in my database. I find FKs a real pain to work with, especially when trying to run dotnet ef database drop. I get errors running this command because I have foreign keys in my database.

I would like to just not worry about foreign keys and just make tables with Id properties available for related items. I will go and grab the related item from the database if/when I need that related information.

   public class Employer : BaseEntity
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public ICollection<Employee> Employees { get; set; }
        public ICollection<Client> Clients { get; set; }
    }

public class Employee : BaseEntity
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public EmployeeType EmployeeType { get; set; }
    public Guid EmployerId { get; set; }
    public Employer Employer { get; set; }
}
public class Client : BaseEntity
    {
        public string Name { get; set; }
        public int EmployerId { get; set; }
        public Employer Employer { get; set; }

    }

Answer 1

When using a relational database, every object in the database will need an Id, and if the object 'belongs' to another object in a different table, you'll need a foreign key. You can't go without it.

Luckily, entity framework is smart enough, that if you design it properly, you will seldom use the foreign keys to perform (group)joins. You usually use the virtual properties.

In entity framework the classes represent the tables of your database. The columns in the tables are represented by the non-virtual properties of the class. The virtual properties represent the relations between the classes.

So you have some BaseEntity and three special types of BaseEntities: Employers, Employees and Clients.

Every Employer has zero or more Employees; every Employee has exactly one Employer: a simple one-to-many relation. For this every Employee needs a foreign key to the Employer it belongs to, whether you like it or not. Luckily you won't have to use it.

Similarly there is a one-to-many relation between Employer and Client: every Employer has zero or more Clients, every Client belongs to exactly one Employer, using a foreign key.

Using the entity framework code first conventions your classes would need a slight change: the relations between the tables should be marked virtual.

class Employer : BaseEntity
{
    public int Id {get; set;}

    // every Employer has zero or more Employees:
    public virtual ICollection<Employee> Employees { get; set; }

    // every Employer has zero or more Clients:
    public virtual ICollection<Client> Clients { get; set; }

    ...
}

class Employee : BaseEntity
{
    public int Id {get; set;}

    // every Employee belongs to exactly one Employer, using a foreign key:
    public int EmployerId {get; set;}
    public virtual Employer Employer { get; set; }

    ...
}

class Client : BaseEntity
{
    public int Id {get; set;}

     // Every Client belongs to exactly one Employer, using foreign key:
     public int EmployerId { get; set; }
     public virtual  Employer Employer { get; set; }

     ...
}

The important change is that the relations between the tables are marked virtual

Note: it might be that you don't want integers for primary keys. That won't change the idea dramatically.

To perform queries that (group)join the tables, you won't need to use the foreign keys,

GroupJoin

Give me all (or some) Employers with all (or some) of his Clients and Employees.

For this you would have used a GroupJoin: every fetched Employer would have a sub-collection of Clients and Employees. With entity framework you don't have to do the join yourself:

var result = dbContext.Employers    // From every employer in the sequence ofall Employers
    .Where(employer => ...)         // or only some of them
    .Select(employer => new         // make one new object
    {
        // select only the properties you actually plan to use
        Id = employer.Id,
        Name = employer.Name,

        Employees = employer.Employees
            .Where(employee => ...)         // only if you do not want all Employees
            .Select(employee => new
            {    
                 // again, select only the properties you plan to use
                 Id = employee.Id,
                 Name = employee.Name,
                 ...
            })
            .ToList(),
        Clients = employer.Clients
            .Where(client=> ...)         // only if you do not want all Clients
            .Select(client => new
            {
                 ...
            })
            .ToList(),
    });

Entity framework knows that you designed a one-to-many and will do the proper join for you. Although you didn't mention any foreign key, entity framework knows what foreign keys are involved.

Note that this way you also get the Employers that have no Clients or Employees

Inner Join

If you don't want 'objects with its sub-objects' (GroupJoin), but a flat result (more like a Join), start with the sub-object:

Give me all (or some) Clients with their Employer

var result = dbContext.Clients.Select(client => new
{
     Id = client.Id,
     Name = client.Name,
     ...

     Employer = new
     {
          Id = client.Employer.Id,
          ...
     },
});

Entity framework knows that you designed a one-to-many and will do the proper join for you.

Note that you won't get the employers without clients.