正在从DbContext创建接口

Question

ASP。NET Core 5 MVC应用程序使用EF Core和具有不同子数据库的Npgsql数据提供程序。

有一个基本数据库。子数据库在表中包含其他列。子数据库包含所有基表表和列。

如何使用动态列？EF Core和ER Npgsql提供程序不支持动态列。

我尝试从基本数据库生成接口，并使用了所有这些数据库。每个数据库CCD_ 1被编译成单独的汇编和ASP。NET MVC依赖注入用于在运行时将其提供给应用程序。对于数据库特定的列，可以使用PocoGetProperties()方法来访问它们。

EF核心脚手架工具在创建接口而不是类上并没有任何选项。另外，Visual Studio似乎不允许从DbContext类创建接口。

如何从DbContext类创建接口？或者还有其他解决方案吗？

Answer 1

如果我理解正确，您可能想创建一个接口，它将定义数据库上下文的常见任务，并将由SqlDbLayer实现，它将实际与数据库上下文一起工作。因此，通过这种方法，您可以轻松地在数据库之间切换或使用多个数据库。

例如，您的DAL可能如下所示：

数据库层接口

public interface IDbLayer
{
public Task<IEnumerable<Student>> GetStudents();
public Task<IEnumerable<Subject>> GetSubjects();
public Task<IEnumerable<Enrollment>> GetEnrollments();
// Student
public Task<Student> GetStudent(int? id);
public Task<Student> AddStudent(Student newStudent);
public Task<Student> EditStudent(Student studentToEdit);
public Task<Student> DeleteStudent(int? id);
// Course
public Task<Subject> GetSubject(int? id);
public Task<Subject> AddSubject(Subject newSubject);
public Task<Subject> EditSubject(Subject subjectToEdit);
public Task<Subject> DeleteSubject(int? id);
// Enrollment
public Task<Enrollment> GetEnrollment(int? id);
public Task<Enrollment> AddEnrollment(Enrollment newEnrollment);
public Task<Enrollment> EditEnrollment(Enrollment enrollmentToEdit);
public Task<Enrollment> DeleteEnrollment(int? grade);
}

SQL数据库层

public class SqlDbLayer : IDbLayer
{
public readonly SchoolDbContext _context;
public SqlDbLayer(SchoolDbContext context)
{
_context = context;
}
public async Task<IEnumerable<Student>> GetStudents()
{
return await _context.Students
.Include(s => s.Study)
.OrderBy(s => s.FirstName)
.ThenBy(s => s.LastName)
.ThenBy(s => s.IndexNumber)
.ThenBy(s => s.Study.Name)
.ToListAsync();
}
public async Task<IEnumerable<Subject>> GetSubjects()
{
return await _context.Subjects
.OrderBy(s => s.Name)
.ToListAsync();
}
public async Task<IEnumerable<Enrollment>> GetEnrollments(int? id)
{
if(id != null)
{
return await _context.Enrollments
.Include(e => e.Student)
.ThenInclude(e => e.Study)
.Include(e => e.Subject)
.Include(e => e.Instructor)
.OrderBy(e => e.IdEnrollment)
.Where(e => e.IdStudent == id)
.ToListAsync();
} 
else
{
return await _context.Enrollments
.Include(e => e.Student)
.ThenInclude(e => e.Study)
.Include(e => e.Subject)
.Include(e => e.Instructor)
.OrderBy(e => e.IdEnrollment)
.ToListAsync();
}
}
public async Task<Student> GetStudent(int? id)
{
var students = await _context.Students.Include(s => s.Study).ToListAsync();
return students.Find(s => s.IdStudent == id);
}
public async Task<Subject> GetSubject(int? id)
{
var subjects = await _context.Subjects.ToListAsync();
return subjects.Find(s => s.IdSubject == id);
}
public async Task<Enrollment> GetEnrollment(int? id)
{
var enrollemnts = await _context.Enrollments
.Include(e => e.Student)
.Include(e => e.Subject)
.ToListAsync();
return enrollemnts.Find(s => s.IdEnrollment == id);
}
public async Task<Student> AddStudent(Student newStudent)
{
_context.Students.Add(newStudent);
await _context.SaveChangesAsync();
return newStudent;
}
public async Task<Subject> AddSubject(Subject newSubject)
{
_context.Subjects.Add(newSubject);
await _context.SaveChangesAsync();
return newSubject;
}
public async Task<Enrollment> AddEnrollment(Enrollment newEnrollment)
{
_context.Enrollments.Add(newEnrollment);
await _context.SaveChangesAsync();
return newEnrollment;
}
public async Task<Student> DeleteStudent(int? id)
{
var studentToDelete = await _context.Students.FindAsync(id);
_context.Students.Remove(studentToDelete);
await _context.SaveChangesAsync();
// int g = 0;
_context.Remove(studentToDelete);
return null;
}
public async Task<Subject> DeleteSubject(int? id)
{
var subjectToDelete = _context.Subjects.Find(id);
_context.Subjects.Remove(subjectToDelete);
await _context.SaveChangesAsync();
_context.Remove(subjectToDelete);
return null;
}
public async Task<Enrollment> DeleteEnrollment(int? id)
{
var enrollmentToDelete = _context.Enrollments.Find(id);
_context.Enrollments.Remove(enrollmentToDelete);
await _context.SaveChangesAsync();
_context.Remove(enrollmentToDelete);
return null;
}
public async Task<Student> EditStudent(Student studentToEdit)
{
_context.Update(studentToEdit);
await _context.SaveChangesAsync();
return null;
}
public async Task<Subject> EditSubject(Subject subjectToEdit)
{
_context.Update(subjectToEdit);
await _context.SaveChangesAsync();
return null;
}
public async Task<Enrollment> EditEnrollment(Enrollment enrollmentToEdit)
{
_context.Update(enrollmentToEdit);
await _context.SaveChangesAsync();
return null;
}
}

数据库上下文

public class SchoolDbContext : DbContext
{
public SchoolDbContext(DbContextOptions options) : base(options)
{
}
public DbSet<Student> Students { get; set; }
public DbSet<Subject> Subjects { get; set; }
public DbSet<Enrollment> Enrollments { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Student>().ToTable("Student", schema: "School");
modelBuilder.Entity<Subject>().ToTable("Course", schema: "School");
modelBuilder.Entity<Enrollment>().ToTable("Enrollment", schema: "School");
base.OnModelCreating(modelBuilder);
}
}

因此，在这里，定义数据库的DbContext由SqlDbLayer注入，并为IDbLayer中定义的所有数据库实现通用任务，您可以定义不同的数据上下文，并使用SqlDbLayer来实现它们。您可能还想在启动中注入IDbLayer和SqlDbLayer例如：

public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<SchoolDbContext>
(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
services.AddScoped<IDbLayer, SqlDbLayer>();
services.AddControllersWithViews();
}

您将通过您定义的IDbLayer接口使用数据库上下文。例如：

public class StudentsController : Controller
{
public readonly IDbLayer _context;
public StudentsController(IDbLayer context)
{
_context = context;
}
public async Task<IActionResult> Index(string studyName, string searchString)
{
var students = await _context.GetStudents();
var studies = await _context.GetStudies();
IQueryable<string> studyQuery = (from s in students
orderby s.Study.Name
select s.Study.Name).AsQueryable();
if (!string.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.IndexNumber == searchString).ToList();
}
if (!string.IsNullOrEmpty(studyName))
{
students = students.Where(s => s.Study.Name == studyName).ToList();
}

ViewBag.Students = students;
ViewBag.Studies = studies;
var studentStudyView = new StudentStudyViewModel
{
StudentsList = students.ToList(),
Studies = new SelectList(studyQuery.Distinct().ToList()),
StudentsCount = students.Count()
};

// int g = 0;

return View(studentStudyView);
}
public async Task<IActionResult> Details(int? id)
{
if(id == null)
{
return NotFound();
}
var student = await _context.GetStudent(id);
if(student == null)
{
return NotFound();
}
return View(student);
}
public async Task<IActionResult> Create()
{
ViewBag.Studies = await _context.GetStudies();
return View();
}
[HttpPost]
public async Task<IActionResult> Create(Student studentToAdd)
{
if (!ModelState.IsValid)
{
return View("Create", studentToAdd);
}
await _context.AddStudent(studentToAdd);
//int g = 0;
return RedirectToAction("Create");  // 302
}

我希望我的观点实际上与你的问题有关。