实体框架和SQLite，最终方法 [英] Entity Framework and SQLite, the ultimate how-to
问题描述
我正试图使Entity Framework(6.4.4.2020年夏季的最新版本)与SQLite(1.0.113.1，也是2020年夏季的最新版本)一起工作.
I'm trying to get Entity Framework (6.4.4. the newest version in summer 2020) working together with SQLite (1.0.113.1, also latest in summer 2020).
我发现了很多有关如何执行此操作的信息，但是这些信息并不总是有用的，它们经常相互矛盾.
I found a lot of information about how to do this, but this information was not always helpful, quite often they contradicted each other.
现在我知道了如何做，我决定记下我是怎么做的.
Now that I found out how to do it, I decided to jot down how I did it.
问题描述了类和表，答案将描述如何做.
The question describes the classes and the tables, the answer will describe how to do it.
我为学校描述了一个数据库，其中每个学校有零个或更多的学生和老师(一对多)，每个学生和每个老师都有一个地址(一对一)，老师教的零个或更多学生，而学生则由零个或多个老师(多对多)来教
I describe a database for Schools, where every School has zero or more Students and Teachers (one-to-many), every Student and every Teacher has exactly one Address (one-to-one), Teachers teach zero or more Students, while Students are taught by zero or more teachers (many-to-many)
所以我有几个表:
- 一个简单的地址:地址
- 一个简单的例子:学校
- 对进入的学校具有外键的学生
- 对他们所在的学校具有外键的老师.
- TeachersStudents:实现学生与教师之间多对多关系的连接表
课程:
地址和学校:
public class Address
{
public long Id { get; set; }
public string Street { get; set; }
public int Number { get; set; }
public string Ext { get; set; }
public string ExtraLine { get; set; }
public string PostalCode { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
public class School
{
public long Id { get; set; }
public string Name { get; set; }
// Every School has zero or more Students (one-to-many)
public virtual ICollection<Student> Students { get; set; }
// Every School has zero or more Teachers (one-to-many)
public virtual ICollection<Teacher> Teachers { get; set; }
}
师生:
public class Teacher
{
public long Id { get; set; }
public string Name { get; set; }
// Every Teacher lives at exactly one Address
public long AddressId { get; set; }
public virtual Address Address { get; set; }
// Every Teacher teaches at exactly one School, using foreign key
public long SchoolId { get; set; }
public virtual School School { get; set; }
// Every Teacher Teaches zero or more Students (many-to-many)
public virtual ICollection<Student> Students { get; set; }
}
public class Student
{
public long Id { get; set; }
public string Name { get; set; }
// Every Student lives at exactly one Address
public long AddressId { get; set; }
public virtual Address Address { get; set; }
// Every Student attends exactly one School, using foreign key
public long SchoolId { get; set; }
public virtual School School { get; set; }
// Every Student is taught by zero or more Teachers (many-to-many)
public virtual ICollection<Teacher> Teachers { get; set; }
}
最后是DbContext:
And finally the DbContext:
public class SchoolDbContext : DbContext
{
public DbSet<Address> Addresses { get; set; }
public DbSet<School> Schools { get; set; }
public DbSet<Student> Students { get; set; }
public DbSet<Teacher> Teachers { get; set; }
}
使用实体框架时，无需在DbContext中定义Junction表TeachersStudents.当然，这并不意味着您将不需要它.
When using entity framework you don't need to define the Junction table TeachersStudents in your DbContext. Of course this doesn't mean that you won't need it.
如果您使用Microsoft SQL Server，这足以让实体框架识别表以及表之间的关系.
If you use Microsoft SQL server this would have been enough to let entity framework identify the tables and the relations between the tables.
A，使用SQLite还不够.
Alas, with SQLite this is not enough.
所以:如何使它工作.继续回答！
So: how to get this working. On to the answer!
推荐答案
因此，我使用Visual Studio创建了一个空解决方案，并添加了一个DLL项目:SchoolSQLite.为了查看是否可行，我还添加了一个控制台应用程序，该应用程序将使用实体框架访问数据库.
So I used Visual Studio to create an empty solution and added a DLL project: SchoolSQLite. To see if this works I also added a console application that would access the database using entity framework.
为完整起见，我添加了一些单元测试.这超出了这个答案的范围.
To be complete I added some unit tests. This is out-of-scope of this answer.
在DLL项目中，我使用了 References-Manage NUGET Packages 来搜索 System.Data.SQLite .这是同时添加实体框架和SQLite所需代码的版本.如果需要:请更新到最新版本.
In the DLL project I used References-Manage NUGET Packages to search for System.Data.SQLite. This is the version that adds both the code needed for Entity Framework and SQLite. If needed: update to the newest version.
添加问题中描述的班级:地址，学校，老师，学生，SchoolDbContext.
Add the classes described in the question: Address, School, Teacher, Student, SchoolDbContext.
现在出现了我发现最困难的部分:控制台应用程序的文件 App.Config 中的连接字符串.
Now comes the part that I found most difficult: the connection string in file App.Config of your console App.
要使其正常运行，我需要在App.Config中包含以下部分:
To get it working I needed the following parts in App.Config:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit ... -->
<section name="entityFramework"
type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework,
Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
requirePermission="false"/>
</configSections>
稍后在App.Config中的EntityFramework部分:
Later in App.Config the section EntityFramework:
<entityFramework>
<providers>
<provider invariantName="System.Data.SqlClient"
type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
<provider invariantName="System.Data.SQLite.EF6"
type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SQLite.EF6" />
<add name="SQLite Data Provider"
invariant="System.Data.SQLite.EF6"
description=".NET Framework Data Provider for SQLite"
type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>
</system.data>
最后是连接字符串.我的数据库所在的文件是 C:\ Users \ Harald \ Documents \ DbSchools.sqlite .当然，您可以选择自己的位置.
And finally the connection string. The file where my database is located is C:\Users\Harald\Documents\DbSchools.sqlite. Of course you can choose your own location.
<connectionStrings>
<add name="SchoolDbContext"
connectionString="data source=C:\Users\Haral\Documents\DbSchools.sqlite"
providerName="System.Data.SQLite.EF6" />
(到其他数据库的连接字符串可能更多)
(there may be more connection strings to other databases)
这应该可以编译，但是您还不能访问数据库.2020年夏季实体框架不会创建表，因此您必须自己执行此操作.
This should compile, but you can't access the database yet. Summer 2020 Entity Framework does not create the tables, so you'll have to do this yourself.
由于我认为这是SchoolDbContext的一部分，所以我添加了一个方法.为此，您需要对SQL有一点了解，但是我认为您的要点是:
As I thought this was part of the SchoolDbContext I added a method. For this you need a little knowledge of SQL, but I think you get the gist:
protected void CreateTables()
{
const string sqlTextCreateTables = @"
CREATE TABLE IF NOT EXISTS Addresses
(
Id INTEGER PRIMARY KEY NOT NULL,
Street TEXT NOT NULL,
Number INTEGER NOT NULL,
Ext TEXT,
ExtraLine TEXT,
PostalCode TEXT NOT NULL,
City TEXT NOT NULL,
Country TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS indexAddresses ON Addresses (PostalCode, Number, Ext);
CREATE TABLE IF NOT EXISTS Schools
(
Id INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS Students
(
Id INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
AddressId INTEGER NOT NULL,
SchoolId INTEGER NOT NULL,
FOREIGN KEY(AddressId) REFERENCES Addresses(Id) ON DELETE NO ACTION,
FOREIGN KEY(SchoolId) REFERENCES Schools(Id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS Teachers
(
Id INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
AddressId INTEGER NOT NULL,
SchoolId INTEGER NOT NULL,
FOREIGN KEY(AddressId) REFERENCES Addresses(Id) ON DELETE NO ACTION,
FOREIGN KEY(SchoolId) REFERENCES Schools(Id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS TeachersStudents
(
TeacherId INTEGER NOT NULL,
StudentId INTEGER NOT NULL,
PRIMARY KEY (TeacherId, StudentId)
FOREIGN KEY(TeacherId) REFERENCES Teachers(Id) ON DELETE NO ACTION,
FOREIGN KEY(StudentId) REFERENCES Students(Id) ON DELETE NO ACTION
)";
var connectionString = this.Database.Connection.ConnectionString;
using (var dbConnection = new System.Data.SQLite.SQLiteConnection(connectionString))
{
dbConnection.Open();
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sqlTextCreateTables;
dbCommand.ExecuteNonQuery();
}
}
}
有些事情值得一提:
- 表地址有一个额外的索引，因此使用PostalCode +门牌号(+扩展名)搜索地址会更快.您的邮政编码是什么?"嗯，这是5473TB，门牌号为6".索引将立即显示完整的地址.
- 尽管SchoolDbcontext并未提及联结表TeachersStudents，但我仍然需要创建它.组合[TeacherId，StudentId]将是唯一的，因此可以用作主键
- 如果学校被删除，则其所有的老师和学生都需要被删除:在DECATE CASCADE上
- 如果老师离开了学校，应该不会伤害学生.如果学生离开学校，则老师会继续教学:不删除任何动作
在您的应用程序启动后首次执行实体框架查询时，将调用方法 OnModelCreating .因此，这是检查表是否存在以及是否创建表的好时机.
When your application executes an entity framework query for the first time after it has been started, method OnModelCreating is called. So that is a good moment to check if the tables exist, and if not, create them.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
this.CreateTables();
当然，您应该使用OnModelCreating通知实体框架有关您的表以及表之间的关系.可以在创建表后完成.
Of course you should use OnModelCreating to inform entity framework about your tables and the relations between the tables. This can be done after the tables are created.
继续OnModelCreating:
Continuing OnModelCreating:
this.OnModelCreatingTable(modelBuilder.Entity<Address>());
this.OnModelCreatingTable(modelBuilder.Entity<School>());
this.OnModelCreatingTable(modelBuilder.Entity<Teacher>());
this.OnModelCreatingTable(modelBuilder.Entity<Student>());
this.OnModelCreatingTableRelations(modelBuilder);
base.OnModelCreating(modelBuilder);
}
对于那些了解实体框架的人来说，对这些表进行建模非常简单.
For those who know entity framework, modelling these tables is fairly straightforward.
地址；简单表的示例
private void OnModelCreatingTable(EntityTypeConfiguration<Address> addresses)
{
addresses.ToTable(nameof(SchoolDbContext.Addresses)).HasKey(address => address.Id);
addresses.Property(address => address.Street).IsRequired();
addresses.Property(address => address.Number).IsRequired();
addresses.Property(address => address.Ext).IsOptional();
addresses.Property(address => address.ExtraLine).IsOptional();
addresses.Property(address => address.PostAlCode).IsRequired();
addresses.Property(address => address.City).IsRequired();
addresses.Property(address => address.Country).IsRequired();
// The extra index, for fast search on [PostalCode, Number, Ext]
addresses.HasIndex(address => new {address.PostAlCode, address.Number, address.Ext})
.HasName("indexAddresses")
.IsUnique();
}
学校也很简单:
private void OnModelCreatingTable(EntityTypeConfiguration<School> schools)
{
schools.ToTable(nameof(this.Schools))
.HasKey(school => school.Id);
schools.Property(school => school.Name)
.IsRequired();
}
师生:他们需要学校的外键，每所学校的学生/老师为零或更多:
Teachers and Students: they have required foreign key to the Schools, every School has zero or more Students / Teachers:
private void OnModelCreatingTable(EntityTypeConfiguration<Teacher> teachers)
{
teachers.ToTable(nameof(SchoolDbContext.Teachers))
.HasKey(teacher => teacher.Id);
teachers.Property(teacher => teacher.Name)
.IsRequired();
// Specify one-to-many to Schools using foreign key SchoolId
teachers.HasRequired(teacher => teacher.School)
.WithMany(school => school.Teachers)
.HasForeignKey(teacher => teacher.SchoolId);
}
private void OnModelCreatingTable(EntityTypeConfiguration<Student> students)
{
students.ToTable(nameof(SchoolDbContext.Students))
.HasKey(student => student.Id);
students.Property(student => student.Name)
.IsRequired();
// Specify one-to-many to Schools using foreign key SchoolId
students.HasRequired(student => student.School)
.WithMany(school => school.Students)
.HasForeignKey(student => student.SchoolId);
}
注意:默认情况下:如果学校被删除，它将级联下降:所有教师和学生都将被删除.
Note: by default: if a School is removed, this will cascade down: all its Teachers and Students will be removed.
只剩下一个表关系:联结表.如果我愿意的话，我可以在这里定义学校与教师之间以及学校与学生之间的一对多关系.在定义教师和学生时，我已经做到了.因此，这里不需要它们.我留下了代码，作为示例，如果您想将它们放在这里.
Only one table relation is left: the junction table. If I wanted I could have defined the one-to-many relations between Schools and Teachers and Schools and Students also here. I already did this when defining the Teachers and the Students. So they are not needed here. I left the code, as example if you want to put them here.
private void OnModelCreatingTableRelations(DbModelBuilder modelBuilder)
{
//// School <--> Teacher: One-to-Many
//modelBuilder.Entity<School>()
// .HasMany(school => school.Teachers)
// .WithRequired(teacher => teacher.School)
// .HasForeignKey(teacher => teacher.SchoolId)
// .WillCascadeOnDelete(true);
//// School <--> Student: One-To-Many
//modelBuilder.Entity<School>()
// .HasMany(school => school.Students)
// .WithRequired(student => student.School)
// .HasForeignKey(student => student.SchoolId)
// .WillCascadeOnDelete(true);
// Teacher <--> Student: Many-to-many
modelBuilder.Entity<Teacher>()
.HasMany(teacher => teacher.Students)
.WithMany(student => student.Teachers)
.Map(manyToMany =>
{
manyToMany.ToTable("TeachersStudents");
manyToMany.MapLeftKey("TeacherId");
manyToMany.MapRightKey("StudentId");
});
}
现在我们快完成了.我们要做的就是确保不会删除和重新创建数据库.通常通过以下方式完成:
Now we are almost done. All we have to do is make sure that the database will not be dropped and recreated. This is usually done in:
Database.SetInitializer<SchoolDbContext>(null);
因为我想隐藏我们使用SQLite的功能，所以我将其添加为SchoolDbContext的一种方法:
Because I wanted to hide that we use SQLite, so I added this as a method to SchoolDbContext:
public class SchoolDbContext : DbContext
{
public static void SetInitializeNoCreate()
{
Database.SetInitializer<SchoolDbContext>(null);
}
public SchoolDbContext() : base() { }
public SchoolDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { }
// etc: add the DbSets, OnModelCreating and CreateTables as described earlier
}
我有时看到人们在构造函数中设置初始化程序:
I sometimes see that people set the initializer in the constructor:
public SchoolDbContext() : base()
{
Database.SetInitializer<SchoolDbContext>(null);
}
但是，此构造函数将经常被调用.我认为每次这样做都有些浪费.
However, this constructor will be called very often. I thought it a bit of a waste to do this every time.
当然，有些模式可以在首次构造SchoolDbContext时自动设置一次初始化程序.为了简单起见，我在这里没有使用它们.
Of course there are patterns to automatically set the initializer once when the SchoolDbContext is constructed for the first time. For simplicity I didn't use them here.
static void Main(string[] args)
{
Console.SetBufferSize(120, 1000);
Console.SetWindowSize(120, 40);
Program p = new Program();
p.Run();
// just for some need ending:
if (System.Diagnostics.Debugger.IsAttached)
{
Console.WriteLine();
Console.WriteLine("Fin");
Console.ReadKey();
}
}
Program()
{
// Set the database initializer:
SchoolDbContext.SetInitializeNoCreate();
}
现在有趣的部分:添加一所学校，添加一些老师，添加一些学生，然后给他一些老师.
And now the fun part: Add a School, add some Teachers, add Some Student and give him some Teachers.
void Run()
{
// Add a School:
School schoolToAdd = this.CreateRandomSchool();
long addedSchoolId;
using (var dbContext = new SchoolDbContext())
{
var addedSchool = dbContext.Schools.Add(schoolToAdd);
dbContext.SaveChanges();
addedSchoolId = addedSchool.Id;
}
添加老师:
Teacher teacherToAdd = this.CreateRandomTeacher();
teacherToAdd.SchoolId = addedSchoolId;
long addedTeacherId;
using (var dbContext = new SchoolDbContext())
{
var addedTeacher = dbContext.Teachers.Add(teacherToAdd);
dbContext.SaveChanges();
addedTeacherId = addedTeacher.Id;
}
添加学生.
Student studentToAdd = this.CreateRandomStudent();
studentToAdd.SchoolId = addedSchoolId;
long addedStudentId;
using (var dbContext = new SchoolDbContext())
{
var addedStudent = dbContext.Students.Add(studentToAdd);
dbContext.SaveChanges();
addedStudentId = addedStudent.Id;
}
几乎完成了:只有师生之间的多对多关系:
Almost done: only the many-to-many relation between teachers and students:
学生决定由老师授课:
using (var dbContext = new SchoolDbContext())
{
var fetchedStudent = dbContext.Find(addedStudentId);
var fetchedTeacher = dbContext.Find(addedTeacherId);
// either Add the Student to the Teacher:
fetchedTeacher.Students.Add(fetchedStudent);
// or Add the Teacher to the Student:
fetchedStudents.Teachers.Add(fetchedTeacher);
dbContext.SaveChanges();
}
我还试图从学校撤职，发现这并没有伤害学生.另外，如果学生离开学校，则老师会继续教学.最后:如果我删除一所学校，所有的学生和老师都会被删除.
I also tried to Remove Teachers from the Schools, and saw that this didn't harm the Students. Also if a Student leaves School, the Teachers keep on teaching. Finally: if I delete a School, all Students and Teachers are deleted.
因此，现在我向您展示了一些简单的表格，例如地址"和学校"；具有一对多关系的表:教师和学生；以及多对多的关系:StudentsTeachers.
So now I've shown you simple tables, like Addresses and Schools; tables with one-to-many relationships: Teachers and Students; and a many-to-many relationship: StudentsTeachers.
我没有显示一种关系:自引用:指向同一表中另一个对象的外键.我无法在School数据库中为此提供一个很好的例子.如果有人有个好主意，请编辑此答案并添加自引用表.
There is one relation that I didn't show: self-referencing: a foreign key to another object in the same table. I couldn't come up with a good example for this in the School database. If anyone has a good idea, please edit this answer and add the self-referencing table.
希望这对您有用.
这篇关于实体框架和SQLite，最终方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
