c# - Entity Framework Core：包括从 SQL 视图中查找数据以显示在 Razor 页面中

Question

Entity Framework 的新手，正在尝试包含来自 SQL 视图的数据，该视图是站点代码和名称的列表。我目前正在尝试列出所有Incidents 但显示站点名称而不是站点代码。我开始时Incident.Site是一个，int但后来我不确定如何从中加载名称。

课程：

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; }
}

数据库上下文：

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视图：

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

剃刀 .cshtml.cs：

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

    return Page();
}

剃刀.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>

当我运行页面时，这行代码出现异常：

Incidents = await _context.Incidents.ToListAsync();

例外：

InvalidOperationException：无法确定“Site”类型的导航属性“Incident.Site”表示的关系。手动配置关系，或使用“[NotMapped]”属性或使用“OnModelCreating”中的“EntityTypeBuilder.Ignore”忽略此属性。

我错过了什么？

标签： c#asp.net-coreentity-framework-corerazor-pages

Answer 1

关系定义了两个真实实体如何相互关联。它们被映射到数据库中的相应表并使用外键来创建关系。

试试下面的代码：</p>

模型

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; }
}

数据库上下文

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：用于Include加载相关数据

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

        return Page();
}

结果：

对于无密钥实体类型和关系，您可以参考以下链接：

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

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