Does the Database First, Microsoft Entity Framework architecture mean not using dynamic table names in a join table as a best practice?

Question

I have four tables. One of them is a many-to-many join table where the child tables are dynamically named in the child table row, instead of a foreign key:

Person
Address
Employment
(many other tables possible...)

TheJoinTable (A person may have multiple addresses, employers, etc.)

TheJoinTable
====================
the_join_table_data_id PK Identity, Auto-Incrementing
person_id FK non-unique
child_table_id
child_table_name_id (maybe the other table has yet another id so I know what it is, a lookup)
child_table_name

The join table rows might look like for a person with the id of 55:

    TheJoinTable
         ============
         pk     fk_person_id   child_table_id   child_table_name_id   child_table_name
         1      55             12                  6                  Address
         2      55             13                  7                  Employment
         3      55             14                  8                  SomeFutureTable
         4      55             12                  6                  Address
         5      55             12                  6                  Address
       975      55             12                  6                  Address

When I created my Visual Studio 2017 Project and generated my classes from the Database First design (I did not do Code First) using DbContext-Scaffold in the Package Manager Console, I could not make these last two columns foreign keys because of the way I structured the database. This join table has lots of possible keys and tables it could have, Address, Employer, Orders, any Entity you could name.

Those other table ids can be many different things, in other words.

Since these are not Foreign Keys, as I understand them, but they are logical relations, the generated classes were not scaffolded with ICollection except the join table. It would be impossible to generate any other class in this manner because there is no way to know what the other tables could be, and maybe I can add new tables in the future.

I can't have something like this with my present database structure, unless I start creating more classes that didn't get generated (I realize the tutorial was based on Code First):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ContosoUniversity.Models.SchoolViewModels
{
    public class InstructorIndexData
    {
        public IEnumerable<Instructor> Instructors { get; set; }
        public IEnumerable<Course> Courses { get; set; }
        public IEnumerable<Enrollment> Enrollments { get; set; }
    }
}

In SQL I would simply do LEFT JOINs, but here, since I used EF and Database First, I'm not sure what I am supposed to do.

This is what I have, and it works, but I do not believe I am doing "EF" the way EF was intended to be.

 var myData = from mp in _context.Person.Where(m=>m.PersonId==id)
                   from mj  in mp.TheJoinTable //ICollection<Person> in Person Class
                   from madd in _context.Address.Where(l=>l.AddressId == mj.ChildId).DefaultIfEmpty() //regular Where clause in SQL?
                   from memp in _context.Employment.Where(e => e.EmploymentId == me.ChildId).DefaultIfEmpty() //same
                   select new { Person = mp, TheJoinTable = mj, Address = madd, Employment = memp};

I think am using The Entity Framework Proper in the first join because I have the ICollection and the framework wires it up. However, the rest of the Linq is simply a lot of Where clauses, and I'm not sure where these wheres are taking place (in memory or at the db).

It seems to me because of my database design, I should be using Dapper or other micro-ORM and using straight up SQL. I feel like I am losing the benefit of EF's intention and have some hybrid, so I can say I used "EF."

Am I correct here on my analysis?

Am I correct in surmising that if I really wanted to use EF, the way intended (anomalies excepted), I should have done Code First and let all my classes have the ICollection entities and possibly build ViewModels to group them all together?

My question about dynamic join tables simply means a join table that names other tables like I have above, instead of having Foreign Keys for a Collection.

Does the Database First Entity Framework architecture mean not using dynamic table names in a join table as a best practice (when using the EF)?

The join table design is similar to this,

Left join with dynamic table name derived from column

If it matters I am using SQL Server and ASP.NET MVC Core 2.x.

Does the Database First, Microsoft Entity Framework architecture mean not using dynamic table names in a join table as a best practice?

0 Answers0