SQL Many-To-Many Relationship between a Table and a View |

Contents

Many-to-Many Relationships

A many-to-many relationship in SQL occurs when multiple records in one table are associated with multiple records in another table. To implement this, you typically use another table that connects the two.

This table is known by a few different names :

Junction table
Bridge table
Linking table
Associative table
Mapping table

Technical Requirements

Two database tables acting with a parent child relationship, enforced with a FOREIGN KEY relationship.
A table in another database on the same SQL Server, which needs to act as the grandparent in the above relationship.
A grandparent can be assigned to many parents.
A parent can have many grandparents.

Solution

Data in the parent and child tables is currently maintained using one of my Reference Data Web Apps

These apps are configured by me to be per database, so that I have only one connection string in the settings file, to keep things clean and simple.

Resisting the quick & easy approach

If I break convention and start to include tables from other databases, this will eventually lead to a bloated application as this quick and easy fix will always be more tempting than spinning up another reference data web app (despite my blog post above detailing how “quick and easy” it is).

Also, restricting the app to one database helps to manage authorised access to the data at the database level.

So to maintain this, I create a SQL VIEW to SELECT the records from the other database, and treat this as just another “table”/POCO in the dotnet web app.

;CREATE VIEW JobTypes AS
SELECT [JobTypeID], [Description] FROM OTHER_DB.dbo.JobTypes WHERE [Enabled] = 1

The scaffolded entity for the grandparent VIEW resulted in a null reference exception when accessing the parent.

        modelBuilder.Entity<JobType>(entity =>
        {
            entity
                .HasNoKey()
                .ToView("JobTypes");
            entity.Property(e => e.Description)
                .HasMaxLength(50)
                .IsUnicode(false);
            entity.Property(e => e.JobTypeID).ValueGeneratedOnAdd();
        });

Simplifying it to the below resolved the error :

        modelBuilder.Entity<JobType>();

I declared the linking table as so:

CREATE TABLE dbo.FaultJobTypes (
    FaultsFaultID INT NOT NULL,
    JobTypesJobTypeID INT NOT NULL,
    PRIMARY KEY (FaultsFaultID, JobTypesJobTypeID),
    FOREIGN KEY (FaultsFaultID) REFERENCES Faults(FaultID)
	--,FOREIGN KEY (JobTypeID) REFERENCES JobTypes(JobTypeID) ** Not Possible, because FK to VIEW not allowed
);

EF Core decides the column names

The column names were not as I originally declared.

Scaffolding the table resulted in SQL errors about missing column names.

So I re-created the table using the column names that EF core was expecting.

EF Core letting you know what to call the column names. — EF Core letting you know what to call the column names

And configure it within the EF model builder:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


        modelBuilder.Entity<FaultType>(entity =>
        {
            entity.HasKey(e => e.FaultTypeID).HasFillFactor(80);
            entity.Property(e => e.Enabled).HasDefaultValue(true);
            entity.Property(e => e.FaultTypeDescription)
                .HasMaxLength(50)
                .IsUnicode(false);
        });
        
        modelBuilder.Entity<Fault>(entity =>
        {
            entity.HasKey(e => e.FaultID).HasFillFactor(80);
            entity.Property(e => e.Enabled).HasDefaultValue(true);
            entity.Property(e => e.FaultDescription)
                .HasMaxLength(50)
                .IsUnicode(false);

            entity.HasOne(d => d.FaultType).WithMany(p => p.Faults)
                .HasForeignKey(d => d.FaultTypeID)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Faults_FaultTypes");

            entity
            .HasMany(p => p.JobTypes)
            .WithMany(t => t.Faults)
            .UsingEntity(j => j.ToTable("FaultJobTypes"));
        });

        modelBuilder.Entity<JobType>(entity =>
        {
            entity.HasKey(e => e.JobTypeID).HasFillFactor(80);
        });

I am well impressed with how EF Core handled this scenario.

SQL EF Core