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

At first glance, the material below may seem like another banal “halloworld”, “a tutorial from Captain Obviousness”, which are already abundant on the Web, but this impression is misleading. In fact, in order to achieve the same result from scratch, a WinForms programmer who did not work previously with the Entity Framework Core (not to be confused with the classic Entity Framework) and armed only with EF Core tutorials and documentation that are already abundant in the vast expanses of the Web and documentation can take much longer than he naively expected before he got down to business. So it happened with me. Therefore, I want to share my experience.

The main problem is that the overwhelming majority of EF Core materials do not imply the use of Visual Studio (instead, they use the lightweight cross-platform .NET Core tuning) and certainly do not take into account that someone wants to use EF Core in the desktop an application based on the Windows Forms framework (instead, ASP.NET core is usually assumed instead). But there are still problems and situations in which a solution in the form of a desktop application is preferable (or even necessary), and the Entity Framework Core has some advantages (as, of course, disadvantages) compared to Entity Framework 6. 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 extraction is often not considered, and there are already not obvious points in it. You can also notice that EF Core is developing quite actively and many instructions lose relevance, including for this reason.

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

As the platform, I chose .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 choosing 4.6.1 everything works, it will probably work with earlier versions, but I did not check), as the development environment - Visual Studio Community Edition 2015 Update 3, as the DBMS - SQLite, as the OS - Windows 7 64-bit.

Below is a list of steps that resulted in a properly working demo application and during which no error messages were received.

0. Check that the latest versions of the .NET Framework 4.6.2 ( web installer , offline installer ) and Visual Studio 2015 Update 3 ( 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 an integrated update )

1. Install Windows Management Framework 5.0 to update PowerShell to version 5.0. I (after installing Windows 7 and Visual Studio 2015 and all updates to them) had version 2.0 in the system and then in the process I received an error message requiring 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 switch ).

3. We are updating the Visual Studio extension for working with NuGet repositories. To do this, either download the current version 3.5.0.1996 by direct link or add the appropriate extension repository for automatic updates to the Visual Studio settings .

Visual Studio 2015 'Tools' - 'Options' - 'Extensions and Updates' dialogue

4. Create a new project such as Windows Forms Application. I called it "Vs2015WinFormsEfcSqliteCodeFirst20170304Example" (an actual WinForms application using Entity Framework Core, the "code-first" approach and SQLite DBMS in Visual Studio 2015 as of 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 check the “Include prerelease” checkbox; in stable versions, something may be missing or there may be uncorrected bugs. I installed the latest at the moment 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 this through the NuGet command line: select Tools - NuGet Package Manager - Package Manager Console from the menu in the console that appears (which, by the way,

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

6. Create source code files for data model classes. For the sake of order, I placed them in a subfolder of "Model" (some call it "Entities", some throw all the classes to the root of the project, and some even go to the same file). The model describes a case study of a database storing a list of cities and people to / from them. Each person can be associated with only one city, it may not be known from which city in general.

Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext.cs

using Microsoft.EntityFrameworkCore;
namespace Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model
{
    public class Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext : DbContext
    {
        public DbSet Cities { get; set; }
        public DbSet 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 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. We resolve PowerShell scripts, if this is not done, the error "... \ packages \ Microsoft.EntityFrameworkCore.Tools.1.1.0-preview4-final \ tools \ init.ps1 cannot be loaded because running scripts is disabled on this system . " To do this, go to the NuGet command line (select Tools - NuGet Package Manager - Package Manager Console in the menu) and run the following command

PM> Set-ExecutionPolicy RemoteSigned

8. We create “migrations”. To do this, having saved and compiled 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"

as a result, the “Migrations” folder should appear in our project and two files in it: “Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContextModelSnapshot.cs” and “20170304204355_Vs2015WinFormsEfcSqliteCodeFirst201704 other than the last time and other time. the moment of generation in the obvious format, I later deleted it altogether, leaving only “Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration.cs”).

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

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(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Name = table.Column(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(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    CityId = table.Column(nullable: true),
                    Name = table.Column(nullable: true),
                    Surname = table.Column(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("Id")
                        .ValueGeneratedOnAdd();
                    b.Property("Name");
                    b.HasKey("Id");
                    // Эта строка добавлена вручную
                    b.HasIndex("Name").IsUnique();
                    b.ToTable("Cities");
                });
            modelBuilder.Entity("Vs2015WinFormsEfcSqliteCodeFirst20170304Example.Model.Person", b =>
                {
                    b.Property("Id")
                        .ValueGeneratedOnAdd();
                    b.Property("CityId");
                    b.Property("Name");
                    b.Property("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. We generate a database file. To do this, at the NuGet command prompt, 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.sqlite".

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

eleven.Now let's move on to the designer of our form, place a button on it, double-click on it to create an event handler for clicking a button and proceed to edit its code. Below is my code demonstrating the basic actions with database records. I still have the habit of always renaming Form1 to MainForm and the controls are similar in meaning (in this case the only control is button1 in mainButton), but this is a matter of taste and the naming standards accepted in 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 will implement a richer interface, more meaningful logic in the application, add exception handling and data validation to prevent them, but the above example is enough to understand how to do this.

You can download all the above and related code here .

Also popular now: