时间戳

首页 > 解决方案 > 未填充 EF Core 导航属性（再次！）

c# - 未填充 EF Core 导航属性（再次！）

问题描述

设置非常简单。Endpoints 和 Deployments 这两个表是多对多的，并由一个名为 Services 的表连接。

var query = from endpoint in db.Endpoints
    from deployment in endpoint.Service.Deployments
    select new Endpoint(
            deployment.Host,
            endpoint.Method,
            endpoint.Path,
            new Cost(endpoint.CostCredits, new PerRequest()));

var result = await query.ToListAsync();

当我运行它时，我得到 0 个结果。

db.Endpoints[0].Service在调试器中检查显示 null。从稍微不同的角度来看：

var query = db.Endpoints
    .Include(endpoint => endpoint.Service)
    .ThenInclude(s => s.Deployments) // out of desperation!
    .SelectMany(endpoint => endpoint.Service.Deployments.Select(deployment =>
    new Endpoint(
        deployment.Host,
        endpoint.Method,
        endpoint.Path,
        new Cost(endpoint.CostCredits, new PerRequest()))));

这会引发 NullReferenceException。我什至尝试在查询之前添加它：

await db.Endpoints.ToListAsync();
await db.Deployments.ToListAsync();
await db.Services.ToListAsync();

相同的行为。

我已经看到了许多 SO 问题，例如EF Core 在直接访问之前返回空关系，但显然它们不是我的情况——即使加载了所有三个表中的所有数据，导航属性也不会被填充。

如何使上述查询起作用？

这是一个完整的最小复制（与可运行的Dotnetfiddle相同）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;

namespace Repro
{
    class Program
    {
        static async Task Main()
        {
            var db = new MorpherDbContext();
            db.AddSomeData();

            var query = from endpoint in db.Endpoints
                from deployment in endpoint.Service.Deployments
                select new {
                    deployment.Host,
                    endpoint.Method,
                    endpoint.Path
                    };

            var result = await query.ToListAsync();

            Console.WriteLine(result.Count);
        }
    }

    public class Deployment
    {
        public int Id { get; set; }
        public virtual Service Service { get; set; }
        public int ServiceId { get; set; }

        public string Host { get; set; }
        public short? Port { get; set; }
        public string BasePath { get; set; }
    }
    public class Service
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public string UrlSlug { get; set; }

        public virtual ICollection<Endpoint> Endpoints { get; set; }

        public virtual ICollection<Deployment> Deployments { get; set; }
    }

    public class Endpoint
    {
        public int Id { get; set; }

        public virtual Service Service { get; set; }

        public int ServiceId { get; set; }
        public string Path { get; set; } 
        public string Method { get; set; } 
        public int CostCredits { get; set; } 
        public string CostType { get; set; } 
        public string CostTypeParameter { get; set; } 
    }

    public partial class MorpherDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseInMemoryDatabase("db1");
            //optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ReproDb;Trusted_Connection=True;MultipleActiveResultSets=true");
            base.OnConfiguring(optionsBuilder);
        }

        public virtual DbSet<Endpoint> Endpoints { get; set; }
        public virtual DbSet<Deployment> Deployments { get; set; }
        public virtual DbSet<Service> Services { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:DefaultSchema", "dbo");

            modelBuilder.Entity<Deployment>(entity =>
            {
                entity.Property(e => e.Host).IsRequired().HasMaxLength(256);
                entity.Property(e => e.BasePath).HasMaxLength(512);

                entity.HasOne(deployment => deployment.Service)
                    .WithMany(service => service.Deployments)
                    .HasForeignKey(d => d.ServiceId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_Deployments_Services");
            });

            modelBuilder.Entity<Service>(entity =>
            {
                entity.Property(e => e.Name).IsRequired().HasMaxLength(256);
                entity.Property(e => e.UrlSlug).IsRequired().HasMaxLength(256);
            });

            modelBuilder.Entity<Endpoint>(endpoint =>
            {
                endpoint.Property(e => e.Path).IsRequired();
                endpoint.Property(e => e.Method).IsRequired().HasMaxLength(6);
                endpoint.Property(e => e.CostCredits).IsRequired();
                endpoint.Property(e => e.CostType).IsRequired().HasMaxLength(50);
                endpoint.Property(e => e.CostTypeParameter).IsRequired().HasMaxLength(150);

                endpoint.HasOne(e => e.Service)
                    .WithMany(service => service.Endpoints)
                    .HasForeignKey(e => e.ServiceId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_Endpoints_Services");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);

        public void AddSomeData()
        {
            var ws3 = new Service { Name = "Веб-сервис «Морфер»", UrlSlug = "ws"};

            Services.Add(ws3);
            Deployments.Add(new Deployment {Service = ws3, Host = "ws3.morpher.ru"});

            Endpoints.AddRange(new []
            {
                new Endpoint {Method = "GET", Path = "russian/declension", CostCredits = 1, CostType = "PerRequest"},
                new Endpoint {Method = "POST", Path = "russian/declension", CostCredits = 1, CostType = "PerBodyLine"},
                new Endpoint {Method = "*", Path = "russian/userdict", CostCredits = 1, CostType = "PerRequest"},
            });

            ws3.Endpoints = Endpoints.ToList();

            SaveChanges();
        }
    }
}

namespace Repro.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: "dbo");

            migrationBuilder.CreateTable(
                name: "Services",
                schema: "dbo",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(maxLength: 256, nullable: false),
                    UrlSlug = table.Column<string>(maxLength: 256, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Services", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "Deployments",
                schema: "dbo",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    ServiceId = table.Column<int>(nullable: false),
                    Host = table.Column<string>(maxLength: 256, nullable: false),
                    Port = table.Column<short>(nullable: true),
                    BasePath = table.Column<string>(maxLength: 512, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Deployments", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Deployments_Services",
                        column: x => x.ServiceId,
                        principalSchema: "dbo",
                        principalTable: "Services",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "Endpoints",
                schema: "dbo",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    ServiceId = table.Column<int>(nullable: false),
                    Path = table.Column<string>(nullable: false),
                    Method = table.Column<string>(maxLength: 6, nullable: false),
                    CostCredits = table.Column<int>(nullable: false),
                    CostType = table.Column<string>(maxLength: 50, nullable: false),
                    CostTypeParameter = table.Column<string>(maxLength: 150, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Endpoints", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Endpoints_Services",
                        column: x => x.ServiceId,
                        principalSchema: "dbo",
                        principalTable: "Services",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_Deployments_ServiceId",
                schema: "dbo",
                table: "Deployments",
                column: "ServiceId");

            migrationBuilder.CreateIndex(
                name: "IX_Endpoints_ServiceId",
                schema: "dbo",
                table: "Endpoints",
                column: "ServiceId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Deployments",
                schema: "dbo");

            migrationBuilder.DropTable(
                name: "Endpoints",
                schema: "dbo");

            migrationBuilder.DropTable(
                name: "Services",
                schema: "dbo");
        }
    }
}

namespace Repro.Migrations
{
    [DbContext(typeof(MorpherDbContext))]
    partial class MorpherDbContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasDefaultSchema("dbo")
                .HasAnnotation("ProductVersion", "3.1.3")
                .HasAnnotation("Relational:MaxIdentifierLength", 128)
                .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

            modelBuilder.Entity("Repro.Deployment", b =>
                {
                    b.Property<int>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int")
                        .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

                    b.Property<string>("BasePath")
                        .HasColumnType("nvarchar(512)")
                        .HasMaxLength(512);

                    b.Property<string>("Host")
                        .IsRequired()
                        .HasColumnType("nvarchar(256)")
                        .HasMaxLength(256);

                    b.Property<short?>("Port")
                        .HasColumnType("smallint");

                    b.Property<int>("ServiceId")
                        .HasColumnType("int");

                    b.HasKey("Id");

                    b.HasIndex("ServiceId");

                    b.ToTable("Deployments");
                });

            modelBuilder.Entity("Repro.Endpoint", b =>
                {
                    b.Property<int>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int")
                        .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

                    b.Property<int>("CostCredits")
                        .HasColumnType("int");

                    b.Property<string>("CostType")
                        .IsRequired()
                        .HasColumnType("nvarchar(50)")
                        .HasMaxLength(50);

                    b.Property<string>("CostTypeParameter")
                        .IsRequired()
                        .HasColumnType("nvarchar(150)")
                        .HasMaxLength(150);

                    b.Property<string>("Method")
                        .IsRequired()
                        .HasColumnType("nvarchar(6)")
                        .HasMaxLength(6);

                    b.Property<string>("Path")
                        .IsRequired()
                        .HasColumnType("nvarchar(max)");

                    b.Property<int>("ServiceId")
                        .HasColumnType("int");

                    b.HasKey("Id");

                    b.HasIndex("ServiceId");

                    b.ToTable("Endpoints");
                });

            modelBuilder.Entity("Repro.Service", b =>
                {
                    b.Property<int>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int")
                        .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

                    b.Property<string>("Name")
                        .IsRequired()
                        .HasColumnType("nvarchar(256)")
                        .HasMaxLength(256);

                    b.Property<string>("UrlSlug")
                        .IsRequired()
                        .HasColumnType("nvarchar(256)")
                        .HasMaxLength(256);

                    b.HasKey("Id");

                    b.ToTable("Services");
                });

            modelBuilder.Entity("Repro.Deployment", b =>
                {
                    b.HasOne("Repro.Service", "Service")
                        .WithMany("Deployments")
                        .HasForeignKey("ServiceId")
                        .HasConstraintName("FK_Deployments_Services")
                        .OnDelete(DeleteBehavior.Restrict)
                        .IsRequired();
                });

            modelBuilder.Entity("Repro.Endpoint", b =>
                {
                    b.HasOne("Repro.Service", "Service")
                        .WithMany("Endpoints")
                        .HasForeignKey("ServiceId")
                        .HasConstraintName("FK_Endpoints_Services")
                        .OnDelete(DeleteBehavior.Restrict)
                        .IsRequired();
                });
#pragma warning restore 612, 618
        }
    }
}

标签： c#ef-core-3.1

解决方案

问题是以下行：

ws3.Endpoints = Endpoints.ToList();

此行将弄乱由实体框架的当前上下文维护的导航属性/集合。实际上，当您尝试在方法中设置Service = ws3对象Endpoint时，您AddSomeData()将收到以下异常消息：

实体 'Service' 和 'Endpoint' 与键值 '{ServiceId: 1}' 之间的关联已被切断，但该关系要么被标记为“必需”，要么由于外键不可为空而被隐式要求。如果在切断所需关系时应删除依赖/子实体，则设置关系以使用级联删除。

您正在Endpoint通过方法添加对象，但没有分配Add()/AddRange()任何引用（或值集）。结合上面的行，它会中断（不知何故）。ServiceServiceId

要解决此问题，您应该删除该ws3.Endpoints = Endpoints.ToList()行并设置Service导航属性或删除对对象的Add()/AddRange()调用。Endpoint该AddSomeData()方法可能如下所示：

public void AddSomeData()
{
    var ws3 = new Service { Name = "Веб-сервис «Морфер»", UrlSlug = "ws"};

    Services.Add(ws3);
    Deployments.Add(new Deployment {Service = ws3, Host = "ws3.morpher.ru"});
    var endpointsToAdd = new []
    {
        new Endpoint {Method = "GET", Path = "russian/declension", CostCredits = 1, CostType = "PerRequest"},
        new Endpoint {Method = "POST", Path = "russian/declension", CostCredits = 1, CostType = "PerBodyLine"},
        new Endpoint {Method = "*", Path = "russian/userdict", CostCredits = 1, CostType = "PerRequest"},
    };

    ws3.Endpoints = endpointsToAdd.ToList();

    SaveChanges();
}

另一个解决方案是：

public void AddSomeData()
{
    var ws3 = new Service { Name = "Веб-сервис «Морфер»", UrlSlug = "ws"};

    Services.Add(ws3);
    Deployments.Add(new Deployment {Service = ws3, Host = "ws3.morpher.ru"});
    Endpoints.Add(new Endpoint {Service = ws3, Method = "GET", Path = "russian/declension", CostCredits = 1, CostType = "PerRequest"});
    Endpoints.Add(new Endpoint {Service = ws3, Method = "POST", Path = "russian/declension", CostCredits = 1, CostType = "PerBodyLine"});
    Endpoints.Add(new Endpoint {Service = ws3, Method = "*", Path = "russian/userdict", CostCredits = 1, CostType = "PerRequest"});

    SaveChanges();
}

推荐阅读