Using the Entity Framework Core code-first with SQLite DBMS when developing WinForms-applications in VisualStudio 2015

At first glance, the material below may seem another banal “Halloween world”, “a tutorial from Captain Obviousness”, of which there are already plenty on the Internet, but this impression is deceptive. In fact, in order to achieve the same result from scratch, a WinForms programmer who had not previously worked with the Entity Framework Core (not to be confused with the classic Entity Framework) and armed only with EF Core tutorials and documentation that already abound in the Internet’s open spaces can take much longer than he naively expected before he got down to business. It happened to me. Therefore I want to share the experience.

The main problem is that the vast majority of available materials on EF Core do not imply the use of Visual Studio (instead, the use of the lightweight cross-platform .NET Core tuling is implied) and certainly do not take into account that someone wants to use EF Core in desktop file distribution. an application based on the Windows Forms framework (which usually means using the ASP.NET core instead). But the tasks and situations in which the solution in the form of a desktop application is preferable (or even necessary) still exist, and the advantages (as is, of course, disadvantages) of the Entity Framework Core compared to Entity Framework 6 are. In addition, in the examples available on the Web, as a rule, only the most elementary actions such as adding a record to the database are considered, even the question of subsequent data retrieval is often not considered, and there are no obvious points in it. It can also be noted that EF Core is actively developing and many instructions lose their relevance, including for this reason.

In my attempts to solve such an seemingly elementary task as the development of a minimal WinForms demo application using EF Core in VisualStudo 2015, I came across a whole range of problems such as exceptions, unexpected behavior, misunderstanding how to do something that in tutorials is meant as self-evident, etc. . As a result, some of the steps that I will give below had to grope almost blindly, Google, asking questions and experimenting.

As a platform, I chose the .NET Framework 4.6.1 (version 4.6.2 for some reason does not appear in the list of available in my Visual Studio, but when selecting 4.6.1 everything works, it will probably work with earlier versions, but I did not check), as a development environment - Visual Studio Community Edition 2015 Update 3, as a DBMS - SQLite, as an OS - Windows 7 64-bit.
')
Below is a list of steps that resulted in a properly running demo application and during the execution of which no error message was received.

0. Verify that the latest versions of the .NET Framework 4.6.2 ( web installer , offline installer ) and Visual Studio 2015 Update 3 are installed ( update web installer , update offline installer ISO , web installer full version of Visual Studio 2015 Update Community Edition 3 , offline installer ISO full version of Visual Studio 2015 Update Community Edition 3 with the integrated update )

1. Install Windows Management Framework 5.0 to upgrade PowerShell to version 5.0. I (after installing Windows 7 and Visual Studio 2015 and all the updates for them) had version 2.0 in the system and then in the process I received an error message demanding a newer version

2. Install .NET Core 1.0.1 tools Preview 2 ( web installer , can be used to create an offline distribution using the / layout key ).

3. Update the Visual Studio extension to work with NuGet repositories. To do this, either download the currently available version 3.5.0.1996 by a direct link, or add the corresponding repository of extensions for automatic updates to the Visual Studio settings.



4. Create a new project like Windows Forms Application. I called it “Vs2015WinFormsEfcSqliteCodeFirst20170304Example” (an example of a WinForms application using the Entity Framework Core, the “code-first” approach and SQLite DBMS in Visual Studio 2015) is relevant on 04.03.2017. As the target framework, select the .NET Framework 4.6.1.

5. Right-click on our project in the Solution Explorer panel, select Manage NuGet Packages ..., go to the Browse tab, install Microsoft.EntityFrameworkCore.Sqlite.Design and Microsoft.EntityFrameworkCore.Tools. To install the current versions, you may need to tick the “Include prerelease” checkbox; in stable versions, something may be missing or there are uncorrected bugs. I installed the latest Microsoft.EntityFrameworkCore.Sqlite.Design 1.1.0 and Microsoft.EntityFrameworkCore.Tools 1.1.0-preview4-final. If you have problems installing Microsoft.EntityFrameworkCore.Tools, you can try to do it via the NuGet command line: choose Tools - NuGet Package Manager - Package Manager Console in the menu, in the console that appears (which, by the way, we still need) after the prompt “PM> "Enter" Install-Package Microsoft.EntityFrameworkCore.Tools -Pre "

PM> Install-Package Microsoft.EntityFrameworkCore.Tools -Pre 

6. Create the source code files of the data model classes. For the order, I put them in the “Model” subfolder (some call it “Entities”, some throw all the classes at the root of the project, and some in one file). The model describes an educational example of a database storing a list of cities and people to / from them. Each person can be associated with only one city, it may be unknown from which city he is at all.

Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext.cs

 using Microsoft.EntityFrameworkCore; namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model { public class Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext : DbContext { public DbSet<City> Cities { get; set; } public DbSet<Person> People { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlite("Filename=Vs2015WinFormsEfcSqliteCodeFirst20170304Example.sqlite"); } } } 

City.cs

 using System.Collections.Generic; using System.ComponentModel.DataAnnotations.Schema; namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model { public class City { public int Id { get; set; } public string Name { get; set; } [InverseProperty("City")] public virtual ICollection<Person> People { get; set; } } } 

Person.cs

 using System.ComponentModel.DataAnnotations.Schema; namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model { public class Person { public int Id { get; set; } public string Name { get; set; } public string Surname { get; set; } public int? CityId { get; set; } [InverseProperty("People")] public virtual City City { get; set; } } } 

7. Allowing PowerShell scripts, if this is not done, an error can occur "... \ packages \ Microsoft.EntityFrameworkCore.Tools.1.1.0-preview4-final \ tools \ init.ps1 . " To do this, go to the NuGet command line (select Tools - NuGet Package Manager - Package Manager Console in the menu) and execute the following command

 PM> Set-ExecutionPolicy RemoteSigned 

8. Create a "migration." To do this, save and compile our code (just to make sure there are no obvious typos) go to the NuGet command line and execute the following command.

 PM> Add-Migration -Name "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration" -Context "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext" 

in vsth in the project should be in the folder "Migrations" and two files in it: "vs2015WinFormsEftsp4pciteitedevice20170304ExampleContextModelSnapshot.cs" and the moment of generation in an obvious format, I then deleted it altogether leaving only “Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration.cs”).

9. Edit the generated files to add a unique condition (unique constraint, also known as the secondary key) to the name of the city (in reality, of course, there are cities with the same name, but for example, it will not be superfluous)

Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration.cs

 using Microsoft.EntityFrameworkCore.Migrations; namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Migrations { public partial class Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.CreateTable( name: "Cities", columns: table => new { Id = table.Column<int>(nullable: false) .Annotation("Sqlite:Autoincrement", true), Name = table.Column<string>(nullable: true) }, constraints: table => { table.PrimaryKey("PK_Cities", x => x.Id); //     table.UniqueConstraint("UQ_Cities_Name", x => x.Name); }); migrationBuilder.CreateTable( name: "People", columns: table => new { Id = table.Column<int>(nullable: false) .Annotation("Sqlite:Autoincrement", true), CityId = table.Column<int>(nullable: true), Name = table.Column<string>(nullable: true), Surname = table.Column<string>(nullable: true) }, constraints: table => { table.PrimaryKey("PK_People", x => x.Id); table.ForeignKey( name: "FK_People_Cities_CityId", column: x => x.CityId, principalTable: "Cities", principalColumn: "Id", onDelete: ReferentialAction.Restrict); }); migrationBuilder.CreateIndex( name: "IX_People_CityId", table: "People", column: "CityId"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropTable( name: "People"); migrationBuilder.DropTable( name: "Cities"); } } } 

Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContextModelSnapshot.cs

 using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Infrastructure; using Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model; namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Migrations { [DbContext(typeof(Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext))] partial class Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContextModelSnapshot : ModelSnapshot { protected override void BuildModel(ModelBuilder modelBuilder) { modelBuilder .HasAnnotation("ProductVersion", "1.1.0-rtm-22752"); modelBuilder.Entity("Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model.City", b => { b.Property<int>("Id") .ValueGeneratedOnAdd(); b.Property<string>("Name"); b.HasKey("Id"); //     b.HasIndex("Name").IsUnique(); b.ToTable("Cities"); }); modelBuilder.Entity("Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model.Person", b => { b.Property<int>("Id") .ValueGeneratedOnAdd(); b.Property<int?>("CityId"); b.Property<string>("Name"); b.Property<string>("Surname"); b.HasKey("Id"); b.HasIndex("CityId"); b.ToTable("People"); }); modelBuilder.Entity("Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model.Person", b => { b.HasOne("Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model.City", "City") .WithMany("People") .HasForeignKey("CityId"); }); } } } 

10. Generate the database file. To do this, in the command line NuGet run the following command

 PM> Update-Database -Context "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext" 

The database file will be created in the same directory as the compiled exe-file of our application, I have this "... \ Vs2015WinFormsEfcSqliteCodeFirst20170304Example \ bin \ Debug \ Vs2015WinFormsEfcSqliteCodeFirst20170304Example.sll".

At this stage, we can already look inside the created file, for example, using the official console client sqlite3 or the free DB Browser for SQLite DB Cross- Platform GUI, and make sure that the tables were created correctly. In addition to our “Cities” and “People” tables, we also find there the “__EFMigrationsHistory” table with the EF Core service information and the “sqlite_sequence” table with the SQLite service information.

11. Now let's move on to the designer of our form, place a button on it, click and on it twice to create an event handler for clicking a button and proceed to editing its code. Below I provide my code demonstrating the main actions with database records. I still have the habit of always renaming Form1 to MainForm and controls in a similar way (in this case, the only button1 control in mainButton), but this is a matter of taste and naming standards adopted by your team.

MainForm.cs

 private void mainButton_Click(object sender, EventArgs e) { //       using (var context = new Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext()) { foreach (var person in context.People) context.Remove(person); foreach (var city in context.Cities) context.Remove(city); context.SaveChanges(); } //         //     ,     using (var context = new Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext()) { var praha = new City { Name = "Praha" }; var london = new City { Name = "London" }; var madrid = new City { Name = "Madrid" }; var jan = new Person { Name = "Jan", City = praha }; var john = new Person { Name = "John", City = london }; var juan = new Person { Name = "Juan", City = madrid }; context.Cities.AddRange(praha, london, madrid); context.People.AddRange(jan, john, juan); context.SaveChanges(); } //        , //      , //       ( ) using (var context = new Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext()) { //     Include(city => city.People) //    context.Cities.Single(city => city.Name == "London"); //   ,    .People   null. //  production    .Single     , //           var london = context.Cities.Include(city => city.People)(city => city.Name == "London"); var peter = new Person { Name = "Peter", City = london }; var john = london.People.Single(person => person.Name == "John"); john.Surname = "Smith"; context.Add(peter); context.Update(john); context.SaveChanges(); } } 

Of course, in real life, you implement a richer interface, more meaningful logic in the application, add exception handling and data validation to prevent them, the same example is enough to understand how to do it.

Download all the above and the accompanying code here .