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

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

by admin

At first sight, this material below might look like a trivial "Halloween" or "tutorial by Captain Hindsight", which you can find a lot on the Internet, but this impression is misleading. In fact, to achieve the same result from scratch a WinForms programmer who never worked with Entity Framework Core (do not confuse with the classical Entity Framework)and armed only with abundant tutorials on EF Core and documentation lying around the World Wide Web can take much more time than he naively expected before taking the job. So it happened to me. So I wanted to share my experience.
The main problem is that available materials on EF Core overwhelmingly do not imply using Visual Studio (lightweight cross-platform .NET Core toggling is implied instead) and definitely do not take into account that someone would want to use EF Core in a Windows Forms-based desktop application (as a rule ASP.NET core is implied instead).But there are still tasks and situations where it is preferable (or even necessary) to use a desktop application, and there are advantages (and disadvantages, of course) of Entity Framework Core over Entity Framework 6.In addition, the examples available in the Web usually only cover the most basic steps such as adding a record to the database, and even the question of the subsequent data extraction is often not considered, and it already has its moments that are not obvious. Also, you can see that EF Core is actively developing and many instructions are losing relevance for this reason.
In my attempts to solve such a seemingly elementary task as developing a minimal WinForms demonstration application using EF Core in VisualStudo 2015, I ran into a whole bunch of problems like exceptions, unexpected behavior, not understanding how to do something that the tutorials imply is self-evident, etc. As a result, some of the steps I will quote below had to be fumbled almost blindly, googling, asking questions and experimenting.
As a platform I chose .NET Framework 4.6.1 (version 4.6.2 for some reason does not appear in the list of available to me in Visual Studio, but when I choose 4.6.1 everything works, probably will work with earlier versions, but I have not checked), as a development environment – Visual Studio Community Edition 2015 Update 3, as a DBMS – SQLite, as the OS – Windows 7 64-bit.
Below is a list of steps that resulted in a properly working demonstration application and in which no error messages were received in the process.
0. Check that the latest versions of the .NET Framework 4.6.2 are installed ( web installer , offline installer ) and Visual Studio 2015 Update 3 ( update web installer , update offline installer ISO , web installerfull version of Visual Studio 2015 Update Community Edition 3 , offline installer ISO of the full version of Visual Studio 2015 Update Community Edition 3 with intergrated update )
1. Install Windows Management Framework 5.0 To update PowerShell to version 5.0. I had (after installing Windows 7 and Visual Studio 2015 and all its updates) version 2.0 in my system and further in the process I got 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. Update the Visual Studio extension to work with NuGet repositories. To do this, either download the currently current version from direct link Or add in Visual Studio settings the appropriate extension repository for automatic updates.
Using Entity Framework Core code-first with DBMS SQLite when developing WinForms applications in VisualStudio 2015
4. Create a new project of Windows Forms Application type. I named it "Vs2015WinFormsEfcSqliteCodeFirst20170304Example" (the current 04.03.2017 example of a WinForms application using Entity Framework Core, "code-first" approach and SQLite DBMS in Visual Studio 2015). We choose .NET Framework 4.6.1 as the target framework.
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 current versions you may need to check "Include prerelease", stable versions may be missing something or have 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 with installation of Microsoft.EntityFrameworkCore.Tools you can try to do it through NuGet command line: select Tools – NuGet Package Manager – Package Manager Console, in the appeared console (which by the way we need further) after "PM> " prompt enter "Install-Package Microsoft.EntityFrameworkCore.Tools -Pre"

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

6. Create source code files of data model classes. For order, I put them in a subfolder called "Model" (some call it "Entities", some put all the classes in the root of the project, and some put them in a single file). The model describes a textbook example of a database storing a list of cities and people in/from them. Each person can be associated with only one city, it may not be known which city they are from at all.

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");}}}


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; }}}


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. Allow PowerShell scripts, if not done, you might get an error " packagesMicrosoft.EntityFrameworkCore.Tools.1.1.0-preview4-finaltoolsinit.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 from the menu) and run the following command

PM> Set-ExecutionPolicy RemoteSigned

8. Let’s create a "migration". To do this, after saving and compiling our code (just to make sure there are no obvious typos), go to the NuGet command line and run the following command.

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

as a result, we should have in the project folder "Migrations" and two files in it: "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContextModelSnapshot.cs" and "20170304204355_Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration.cs" (of course the number at the beginning of the latter you will have a different – this is nothing but the date and time at the time of generation in the obvious format, I then removed it at all leaving only "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleMigration.cs").
9. Edit the generated files to add a unique constraint (also known as a secondary key) on the city name (in reality, of course, there are cities with the same name, but for example it is useful)

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);// This line was added manuallytable.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");}}}


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");// This line was added manuallyb.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. Let’s generate the database file. To do this, run the following command in the NuGet command line

PM> Update-Database -Context "Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext"

The database file will be created in the same directory where the compiled exe file of our application is located, in my case it’s "Vs2015WinFormsEfcSqliteCodeFirst20170304ExamplebinDebugVs2015WinFormsEfcSqliteCodeFirst20170304Example.sqlite".
At this point we can already look inside the created file, for example with 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. Besides our "Cities" and "People" tables, we will also find the "__EFMigrationsHistory" table with the EF Core information and the "sqlite_sequence" table with the SQLite information.
11. Now let’s go to our form designer, place a button on it, click on it twice to create a button event handler and go to editing its code. Below is my code showing the basic operations with database records. I also tend to always rename Form1 to MainForm and controls similarly (in this case the only control button1 to mainButton), but it’s a matter of taste and your team’s naming standards.

private void mainButton_Click(object sender, EventArgs e){// Deleting all records from both tablesusing (var context = new Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext()){foreach (var person in context.People)context.Remove(person);foreach (var city in context.Cities)context.Remove(city);context.SaveChanges();}// Add new entries to the city table and then// Add new entries to the people table, referring to the added citiesusing (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();}// Load a record about the previously added city from the database, // add a new person to this city, // change the entry about the person added earlier (add a surname)using (var context = new Vs2015WinFormsEfcSqliteCodeFirst20170304ExampleContext()){// Note the Include(city => city.People) construct.// if you write just context.Cities.Single(city => city.Name == "London")// then the city will be found, but its .People list will be null.// In the code, when using .Single, we should also add handling of cases, // when there are no matching entries or when there is more than onevar 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 in your application a richer interface, more meaningful logic, add exception handling and data validation to prevent exceptions, but this example is enough to understand how to do it.
Download the entire code above and the accompanying code can be here

You may also like