c# - EF Core - 加载对象

Question

我有一个要在我的 C# 应用程序中使用的 SQL-Server 数据库。

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL
    [TestsId] INT NOT NULL,
    [WrittenId] INT NOT NULL,
)

我将数据库表转换为 C# 类:

public partial class Marks
{
    public int Id { get; set; }
    public int SubjectId { get; set; }
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public decimal Value { get; set; }

    public Categories Category { get; set; }
    public Subjects Subject { get; set; }
}

public partial class Subjects
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Teacher { get; set; }

    public ICollection<Marks> Marks { get; set; }
}

public partial class Categories
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Marks> Marks { get; set; }
}

我想获得一个主题，然后为每个类别获得相应的分数。对于每个主题，应该有多个类别。我如何使用 Entity Framework Core 2.0 实现这一点？

Answer 1

Mark 是你的多对多表，你说每个主题都应该有多个类别。

我使用内存中提供程序编写了代码。您可以像这样更改表结构。

首先，您所有的实体命名都必须是单数。

您的实体应如下所示；

public class Subject
{
    public Subject()
    {
        Marks = new List<Mark>();
        Categories = new List<Category>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Teacher { get; set; }

    public ICollection<Mark> Marks { get; set; }

    public ICollection<Category> Categories { get; set; }
}

public class Category
{
    public Category()
    {
        Marks = new List<Mark>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int SubjectId { get; set; }
    public Subject Subject { get; set; }
    public ICollection<Mark> Marks { get; set; }
}

public class Mark
{
    public int Id { get; set; }
    public int SubjectId { get; set; }
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public decimal Value { get; set; }

    public Category Category { get; set; }
    public Subject Subject { get; set; }
}

那么你的DataContex应该是这样的；

public class AppDataContext : DbContext
{
    public AppDataContext(DbContextOptions<AppDataContext> options)
        : base(options)
    {
    }

    public DbSet<Subject> Subjects { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Mark> Marks { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Category>().HasMany(c => c.Marks).WithOne(m => m.Category).HasForeignKey(m => m.CategoryId);
        modelBuilder.Entity<Subject>().HasMany(s => s.Marks).WithOne(m => m.Subject).HasForeignKey(m => m.SubjectId);
        modelBuilder.Entity<Subject>().HasMany(s => s.Categories).WithOne(c => c.Subject).HasForeignKey(c => c.SubjectId);
    }
}

所以示例代码如下；

static void Main(string[] args)
{
    var context = new AppDataContext(new DbContextOptionsBuilder<AppDataContext>().UseInMemoryDatabase("test")
        .Options);

    var category11 = new Category {Id = 11, Name = "Category-11"};

    var subject22 = new Subject {Id = 22, Name = "Subject-22"};
    subject22.Categories.Add(category11);

    var mark1 = new Mark
    {
        Id = 1,
        Name = "Mark-1",
        CategoryId = 11,
        SubjectId = 22,
        Category = category11,
        Subject = subject22
    };

    context.Categories.Add(category11);
    context.Subjects.Add(subject22);
    context.Marks.Add(mark1);
    context.SaveChanges();

    var markList = context.Marks.ToList();

    foreach (var mark in markList)
    {
        Console.WriteLine(mark.Name);
        Console.WriteLine(mark.Subject.Categories.FirstOrDefault().Name);
    }

    Console.ReadKey();
}