Entity Framework Core add relationship between view and standard model objects

Question

This is the scenario: I use SQL Server as backend, I have an instance with a database that has some common tables that I want to join with the tables in the database of my new MVC application.

We plan to have dozens of MVC applications, each one with its dedicated database, so we would like to avoid to replicate each common table to every new database. I thought of using views instead.

But I don't know how to add relationship between views and other standard model class, in a way that allows me to use EF with LINQ features.

Here's a demo1 app of what I've done so far - it's for ASP.NET Core 6.0 MVC, plus EF Core 6.0.

In the model folder, I have:

public class Pratica
{
    public int Id { get; set; }        
    public string Note { get; set; }
    public int UtenteId { get; set; }
    public Utente Utente { get; set; } = default!; 
    /* other fields removed for simplicity */   
}

public class Utente
{
     public int Id { get; set; } 
     public string Name { get; set; } = string.Empty;
     public int IdPalazzo     
     public List<Pratica>? Pratiche { get; set; }
    /* other fields removed for simplicity */
}

/* this one represent sthe view */
public class Palazzo
{
    public int IdPalazzo { get; set; }
    public string NomePalazzo { get; set; }
    public string Indirizzo { get; set; }
    /* other fields removed for simplicity */
}

Specific migration for creation of the view Palazzi inside demo1 DB

public partial class commonTabs : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE VIEW dbo.Palazzi as select IdPalazzo , NomePalazzo , Indirizzo  from  Common.dbo.Palazzi;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW dbo.Palazzi;");
    }
}

Now, inside demo1DbContext.cs added DBsets

public DbSet<Pratica> Pratiche { get; set; }
public DbSet<Utente> Utenti { get; set; }
public DbSet<Palazzo> Palazzi { get; set; }

but here I get lost. I saw some other post suggesting to use

protected override void OnModelCreating(ModelBuilder modelBuilder)

but I don't know how to implement it.

How can I join Utente with Palazzo using IdPalazzo. The point is that the view doesn't have a primary key.

I would like to use LINQ for including info about Palazzi starting from Utenti in a one-to-one relationship.

Answer 1

To achieve your requirement set up one to one relationship and use LINQ queries, you need to use OnModelCreating to configure relation as you have done. as EF Core doesn't inherently support relationships directly on database views because they don't have primary keys, you will need to manually configure relationship to make sure it behave as if it had primary key.

set up OnModelCreating(ModelBuilder modelBuilder) as below:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Palazzo>().HasKey(p => p.IdPalazzo);
    modelBuilder.Entity<Utente>()
        .HasOne(u => u.Palazzo) 
        .WithOne()
        .HasForeignKey<Utente>(u => u.IdPalazzo); 
}

Add navigation property to the Utente class for Palazzo:

public class Utente
{
      public int Id { get; set; } 
     public string Name { get; set; } = string.Empty;
     public int IdPalazzo     
     public List<Pratica>? Pratiche { get; set; }
     public Palazzo Palazzo { get; set; } // Navigation property
}

LINQ query:

var utentiWithPalazzo = dbContext.Utenti.Include(u => u.Palazzo).ToList();