Entity Framework Core: Including lookup data from an SQL view to display in Razor Page

Question

New to Entity Framework and am trying to include data from an SQL view, which is a list of site codes and names. I'm currently trying to list all the Incidents but display the site name instead of the site code. I started with Incident.Site being an int but then I wasn't sure how to then load the name from that.

Classes:

public class Incident
{
    public int Id { get; set; }
    public Site Site { get; set; }
    public string Title { get; set; }
    public string ReportedBy { get; set; }
}

public class Site
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Incident Incident { get; set; }
}

DB context:

modelBuilder.Entity<Incident>(entity =>
{
    entity.ToTable("Incidents");

    entity.Property(e => e.Id)
        .HasColumnName("ID");

    // Obviously incorrect
    entity.HasOne(e => e.Site)
        .WithOne(s => s.Incident)
        .HasForeignKey<Site>(s => s.Id);

    entity.Property(e => e.Title)
        .IsRequired()
        .HasMaxLength(50);

    entity.Property(e => e.ReportedBy)
        .IsRequired()
        .HasMaxLength(255);
});

modelBuilder.Entity<Site>(entity =>
{
    entity.HasNoKey();
    entity.ToView("vw_Locations");
    entity.Property(e => e.Id).HasColumnName("Code");
});

SQL view:

create view vw_Locations as
select Code, Name
from MyTable
where <some filters>

Razor .cshtml.cs:

public async Task<IActionResult> OnGetAsync()
{
    Incidents = await _context.Incidents.ToListAsync();

    return Page();
}

Razor .cshtml:

<table class="table table-sm table-striped">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.Incidents[0].Title)</th>
            <th>@Html.DisplayNameFor(model => model.Incidents[0].Site)</th>
            <th>@Html.DisplayNameFor(model => model.Incidents[0].ReportedBy)</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Incidents)
        {
            <tr>
                <td><a asp-page="./Incident" asp-route-id="@item.Id">@Html.DisplayFor(modelItem => item.Title)</a></td>
                <td>@Html.DisplayFor(modelItem => item.Site.Name)</td>
                <td>@Html.DisplayFor(modelItem => item.EnteredBy)</td>
            </tr>
        }
    </tbody>
</table>

When I run the page, an exception occurs on this line of code:

Incidents = await _context.Incidents.ToListAsync();

Exception:

InvalidOperationException: Unable to determine the relationship represented by navigation property 'Incident.Site' of type 'Site'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

What am I missing?

Answer 1

A relationship defines how two real entities relate to each other . They are mapped to corresponding tables in the database and use a foreign key to create relationships .

Try the following code：

Model

public class Incident
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string ReportedBy { get; set; }

    public int SiteId { get; set; }
    public Site Site{ get; set; }
}
public class Site
{
    public int Id { get; set; }
    public string Name { get; set; }

    public Incident Incident { get; set; }
}

DbContext

public DbSet<Incident> Incidents { get; set; }
    public DbSet<Site> Sites { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Incident>(entity =>
        {
            entity.ToTable("Incidents");

            entity.Property(e => e.Id)
                .HasColumnName("ID");

            entity.Property(e => e.Title)
                .IsRequired()
                .HasMaxLength(50);

            entity.Property(e => e.ReportedBy)
                .IsRequired()
                .HasMaxLength(255);
        });

        modelBuilder.Entity<Site>(entity =>
        {
            //entity.HasNoKey();
            entity.ToTable("Location");
            entity.Property(e => e.Id).HasColumnName("Code");

            entity.HasOne(s => s.Incident)
                  .WithOne(i=>i.Site)
                  .HasForeignKey<Incident>(s => s.SiteId);
        });
    }

PageModel: use Include to load related data

public async Task<IActionResult> OnGetAsync()
{
        Incidents = await _context.Incidents.Include(i=>i.Site).ToListAsync();

        return Page();
}

Result:

For the Keyless Entity Types and Relationships , you could refer to the following links:

https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types

https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key

Answer 2

Remove the below code:

// Obviously incorrect
entity.HasOne(e => e.Site)
.WithOne(s => s.Incident)
.HasForeignKey<Site>(s => s.Id);

You have already establish the one to one relation between Site and Incident in the following code. No need to do it twice:

public class Incident
{
     public int Id { get; set; }
     public Site Site { get; set; }  //Relationship
     public string Title { get; set; }
     public string ReportedBy { get; set; }
}

public class Site
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Incident Incident { get; set; }  //Relationship
} 

The following link is the absolute best entity framework core resource out there: https://www.learnentityframeworkcore.com/relationships I use it all the time for reference.