Programming Languages on Power

 View Only

C#.NET application to SQLite using Entity Framework Core

By Alhad Deshpande posted Tue January 17, 2023 12:46 PM

  

For this blog, we take a sample C# program that runs on an IBM Power (ppc64le) virtual machine with RHEL 8.7 installed on it. On this machine, we have .NET 7 that connects to a SQLite database on the same machine using the Microsoft.EntityFrameworkCore.Sqlite driver published by Microsoft under MIT license - a relational database management system (RDBMS).

In this exercise you will learn how to write a console application that connects from a .NET C# program to a backend SQLite database to perform CRUD operations.

Prerequisites

  • A partition running RHEL 8.7 or 9.1 on an IBM Power virtual machine.

    • If you need access to Power hardware, see:  Accelerate your open source development with access to IBM Power resources. It lists several free and low-cost options. Open source developers might consider the Open Source Lab at Oregon State University option.

    • .NET 7 is available as an RPM, dotnet-sdk-7.0, in the AppStream repositories for RHEL 8.7 and RHEL 9.1. The AppStream repositories are enabled by default on RHEL 8 and 9 systems.

  • A partition running Ubuntu 20.04.3 LTS on an Intel machine.

Before you begin

You need to build Entity Framework (EF) Core locally on the Intel machine and copy the generated NuGet packages to the Power machine. This is required because SQLitePCLRaw libarary version 2.1.3 is supported on the ppc64le architecture and the EF Core NuGet package does not yet refer to this latest version of the SQLitePCLRaw library. Therefore, you should complete the following steps on the Ubuntu 20.04.3 LTS Intel machine before you proceed with the rest of the steps on the Power machine.

Note: This step will not be required after EF Core on GitHub is updated to use the latest SQLitePCLRaw library versions. The pull request (PR) raised to update the versions can be found here: https://github.com/dotnet/efcore/pull/29783.

Build EF Core

Complete the following steps to build EF Core on the Intel machine.

  1. Clone the EF Core repository using the following commands.

    git clone --recursive https://github.com/dotnet/efcore.git
    
    cd efcore
  2. Checkout the release/7.0 branch.

    git checkout release/7.0
  3. Apply efcore_sqlitepclraw_2.1.3.patch.

    git apply efcore_sqlitepclraw_2.1.3.patch
  4. Build EF Core using the following commands:

    ./build.sh -clean
    
    ./build.sh -c Release -pack
  5. The EF Core NuGet packages with .nupkg extension will be generated in the artifacts/packages/Release/Shipping directory.

  6. Copy the generated EF Core NuGet packages from the Intel machine to the Power machine at some location, such as: “/root/dotnet-support/local-packages”.

    # cat efcore_sqlitepclraw_2.1.3.patch
    diff --git a/NuGet.config b/NuGet.config
    index 9e61a9aba1..4075f4f3ec 100644
    --- a/NuGet.config
    +++ b/NuGet.config
    @@ -5,7 +5,8 @@
         <!--Begin: Package sources managed by Dependency Flow automation. Do not edit the sources below.-->
         <!--  Begin: Package sources from dotnet-runtime -->
         <!--  End: Package sources from dotnet-runtime -->
    -    <!--End: Package sources managed by Dependency Flow automation. Do not edit the sources above.-->
    +           <!--End: Package sources managed by Dependency Flow automation. Do not edit the sources above.-->
    +    <add key="NuGet" value="https://api.nuget.org/v3/index.json" />
         <add key="dotnet-eng" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet-eng/nuget/v3/index.json" />
         <add key="dotnet-tools" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet-tools/nuget/v3/index.json" />
         <add key="dotnet7" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet7/nuget/v3/index.json" />
    diff --git a/benchmark/EFCore.Sqlite.Benchmarks/EFCore.Sqlite.Benchmarks.csproj b/benchmark/EFCore.Sqlite.Benchmarks/EFCore.Sqlite.Benchmarks.csproj
    index 48d03de0c4..fcef71f0d4 100644
    --- a/benchmark/EFCore.Sqlite.Benchmarks/EFCore.Sqlite.Benchmarks.csproj
    +++ b/benchmark/EFCore.Sqlite.Benchmarks/EFCore.Sqlite.Benchmarks.csproj
    @@ -16,7 +16,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.1" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
       <ItemGroup>
    diff --git a/eng/Versions.props b/eng/Versions.props
    index 357ebe4ba3..80e78a598b 100644
    --- a/eng/Versions.props
    +++ b/eng/Versions.props
    @@ -30,5 +30,6 @@
       <PropertyGroup Label="Other dependencies">
         <!-- NB: Roslyn version affects the minimum required Visual Studio version -->
         <MicrosoftCodeAnalysisVersion>4.2.0</MicrosoftCodeAnalysisVersion>
    +    <SqlitePCLRawVersion>2.1.3</SqlitePCLRawVersion>
       </PropertyGroup>
     </Project>
    diff --git a/src/EFCore.Sqlite/EFCore.Sqlite.csproj b/src/EFCore.Sqlite/EFCore.Sqlite.csproj
    index cf434b475e..a7c610daac 100644
    --- a/src/EFCore.Sqlite/EFCore.Sqlite.csproj
    +++ b/src/EFCore.Sqlite/EFCore.Sqlite.csproj
    @@ -47,7 +47,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
       <ItemGroup>
    diff --git a/src/Microsoft.Data.Sqlite.Core/Microsoft.Data.Sqlite.Core.csproj b/src/Microsoft.Data.Sqlite.Core/Microsoft.Data.Sqlite.Core.csproj
    index 9a4adce814..0721aaa84b 100644
    --- a/src/Microsoft.Data.Sqlite.Core/Microsoft.Data.Sqlite.Core.csproj
    +++ b/src/Microsoft.Data.Sqlite.Core/Microsoft.Data.Sqlite.Core.csproj
    @@ -39,7 +39,7 @@ Microsoft.Data.Sqlite.SqliteTransaction</Description>
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.core" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.core" Version="2.1.3" />
       </ItemGroup>
    
       <ItemGroup>
    diff --git a/src/Microsoft.Data.Sqlite/Microsoft.Data.Sqlite.csproj b/src/Microsoft.Data.Sqlite/Microsoft.Data.Sqlite.csproj
    index 68782e5b51..0bf27be89e 100644
    --- a/src/Microsoft.Data.Sqlite/Microsoft.Data.Sqlite.csproj
    +++ b/src/Microsoft.Data.Sqlite/Microsoft.Data.Sqlite.csproj
    @@ -24,7 +24,7 @@ Microsoft.Data.Sqlite.SqliteTransaction</Description>
       </PropertyGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
       <ItemGroup>
    diff --git a/test/EFCore.Design.Tests/EFCore.Design.Tests.csproj b/test/EFCore.Design.Tests/EFCore.Design.Tests.csproj
    index 4d4ff690a0..2f97e5b324 100644
    --- a/test/EFCore.Design.Tests/EFCore.Design.Tests.csproj
    +++ b/test/EFCore.Design.Tests/EFCore.Design.Tests.csproj
    @@ -57,7 +57,7 @@
       <ItemGroup>
         <PackageReference Include="Microsoft.CodeAnalysis.CSharp" Version="$(MicrosoftCodeAnalysisVersion)" />
         <PackageReference Include="Microsoft.Extensions.DependencyModel" Version="$(MicrosoftExtensionsDependencyModelVersion)" />
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
     </Project>
    diff --git a/test/EFCore.Sqlite.FunctionalTests/EFCore.Sqlite.FunctionalTests.csproj b/test/EFCore.Sqlite.FunctionalTests/EFCore.Sqlite.FunctionalTests.csproj
    index a59a6b8c57..5a3b407b7f 100644
    --- a/test/EFCore.Sqlite.FunctionalTests/EFCore.Sqlite.FunctionalTests.csproj
    +++ b/test/EFCore.Sqlite.FunctionalTests/EFCore.Sqlite.FunctionalTests.csproj
    @@ -53,7 +53,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
     </Project>
    diff --git a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.Tests.csproj b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.Tests.csproj
    index 9f959b8712..f2006d42a6 100644
    --- a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.Tests.csproj
    +++ b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.Tests.csproj
    @@ -11,7 +11,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
     </Project>
    diff --git a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.e_sqlcipher.Tests.csproj b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.e_sqlcipher.Tests.csproj
    index 5a2ef8bbed..11a054d65a 100644
    --- a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.e_sqlcipher.Tests.csproj
    +++ b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.e_sqlcipher.Tests.csproj
    @@ -11,7 +11,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlcipher" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlcipher" Version="2.1.3" />
       </ItemGroup>
    
     </Project>
    diff --git a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.sqlite3.Tests.csproj b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.sqlite3.Tests.csproj
    index c513f8befb..0b0aa70122 100644
    --- a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.sqlite3.Tests.csproj
    +++ b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.sqlite3.Tests.csproj
    @@ -11,7 +11,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.1.3" />
       </ItemGroup>
    
     </Project>
    diff --git a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.winsqlite3.Tests.csproj b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.winsqlite3.Tests.csproj
    index c51d7e22eb..7e04b6e9b1 100644
    --- a/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.winsqlite3.Tests.csproj
    +++ b/test/Microsoft.Data.Sqlite.Tests/Microsoft.Data.Sqlite.winsqlite3.Tests.csproj
    @@ -11,7 +11,7 @@
       </ItemGroup>
    
       <ItemGroup>
    -    <PackageReference Include="SQLitePCLRaw.bundle_winsqlite3" Version="2.1.2" />
    +    <PackageReference Include="SQLitePCLRaw.bundle_winsqlite3" Version="2.1.3" />
       </ItemGroup>
    
     </Project>

Steps

Complete the following steps on the RHEL 8.7 or 9.1 Power machine.

  1. Set up the SQLite3 database engine by installing the sqlite packages and optional development libraries.

    # Install optional development libraries
    yum install sqlite libsqlite3x sqlite-libs
    
    #Install optional development libraries
    yum install sqlite-devel libsqlite3x-devel
  2. Install dotnet. See the Red Hat Product Documentation for more details about the installation.

    yum install dotnet -y

    Verify that dotnet is installed correctly.

    dotnet --info

    The output of the command is displayed below:

    .NET SDK:
     Version:   7.0.100
     Commit:    e12b7af219
    
    Runtime Environment:
     OS Name:     rhel
     OS Version:  8
     OS Platform: Linux
     RID:         rhel.8-ppc64le
     Base Path:   /usr/lib64/dotnet/sdk/7.0.100/
    
    Host:
      Version:      7.0.0
      Architecture: ppc64le
      Commit:       d099f075e4
    
    .NET SDKs installed:
      7.0.100 [/usr/lib64/dotnet/sdk]
    
    .NET runtimes installed:
      Microsoft.AspNetCore.App 7.0.0 [/usr/lib64/dotnet/shared/Microsoft.AspNetCore.App]
      Microsoft.NETCore.App 7.0.0 [/usr/lib64/dotnet/shared/Microsoft.NETCore.App]
    
    Other architectures found:
      None
    
    Environment variables:
      DOTNET_ROOT       [/usr/lib64/dotnet]
    
    global.json file:
      Not found
    
    Learn more:
      https://aka.ms/dotnet/info
    
    Download .NET:
      https://aka.ms/dotnet/download
    
  3. Create a console program that connects to the SQLite database and performs CRUD operations.

    dotnet new console -o EFGetStarted
    cd EFGetStarted
  4. Create the Model.cs file with the following contents. This includes all the models that are required in the program.

    using Microsoft.EntityFrameworkCore;
    using System;
    using System.Collections.Generic;
    
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
    
        public string DbPath { get; }
    
        public BloggingContext()
        {
            var folder = Environment.SpecialFolder.LocalApplicationData;
            var path = Environment.GetFolderPath(folder);
            DbPath = System.IO.Path.Join(path, "blogging.db");
        }
    
        // The following configures EF to create a Sqlite database file in the
        // special "local" folder for your platform.
        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite($"Data Source={DbPath}");
    }
    
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    
        public List<Post> Posts { get; } = new();
    }
    
    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
    
        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
  5. Add the NuGet.config file with the contents below. In this file, the local key is added to the path where you copied the EF Core NuGet packages from Intel machine. For example: “/root/dotnet-support/local-packages”

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <packageSources>
              <clear/>
        <add key="local" value="/root/dotnet-support/local-packages" />
        <add key="NuGet" value="https://api.nuget.org/v3/index.json" />
      </packageSources>
    </configuration>
  6. Edit the Program.cs file with the following contents to connect to the SQLite database and perform CRUD operations.

    using System;
    using System.Linq;
    
    public class Program
    {
            public static void DumpDatabaseSnapshot(BloggingContext db)
            {
                    // Read
                    Console.WriteLine("\nDumping database snapshot...");
                    var blogs = db.Blogs
                            .OrderBy(b => b.BlogId)
                            .ToList();
    
                    Console.WriteLine("Number of records = {0}", blogs.Count);
    
                    foreach(Blog b in blogs)
                    {
                            Console.WriteLine("BlogId = {0}, Url = {1}", b.BlogId, b.Url);
                            foreach(Post p in b.Posts)
                            {
                                    Console.WriteLine("----->PostId = {0}, Title = {1}, Content = {2}", p.PostId, p.Title, p.Content);
                            }
                    }
    
                    Console.WriteLine("\n");
            }
    
            public static void Main(String[] args)
            {
                    using var db = new BloggingContext();
    
                    // Note: This sample requires the database to be created before running.
                    Console.WriteLine($"Database path: {db.DbPath}.");
    
                    // Create
                    Console.WriteLine("Inserting a new blog");
                    db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                    db.SaveChanges();
    
                    DumpDatabaseSnapshot(db);
    
                    var blog = db.Blogs
                            .OrderBy(b => b.BlogId)
                            .First();
    
                    // Update
                    Console.WriteLine("Updating the blog and adding a post");
                    blog.Url = "https://devblogs.microsoft.com/dotnet";
                    blog.Posts.Add(
                    new Post { Title = "Hello World", Content = "I wrote an app using EF Core!" });
                    db.SaveChanges();
    
                    DumpDatabaseSnapshot(db);
    
                    // Delete
                    Console.WriteLine("Delete the blog");
                    db.Remove(blog);
                    db.SaveChanges();
    
                    DumpDatabaseSnapshot(db);
            }
    }
  7. Add the EF Core Sqlite and Design packages to the C# project file. This database provider allows EF Core to be used with the SQLite database.

    dotnet add package Microsoft.EntityFrameworkCore.Sqlite -v 7.0.0
    dotnet add package Microsoft.EntityFrameworkCore.Design -v 7.0.0

    After the above commands are run, the contents of EFGetStarted.csproj file should look as follows:

    [EFGetStarted]# cat EFGetStarted.csproj
    <Project Sdk="Microsoft.NET.Sdk">
    
      <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net7.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
      </PropertyGroup>
    
      <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.0">
          <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
          <PrivateAssets>all</PrivateAssets>
        </PackageReference>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.0" />
      </ItemGroup>
    
    </Project>
  8. Create the database schema using following commands. This will update the database to the state of last migration performed. See, EF Core tools reference (.NET CLI) - EF Core | Microsoft Learn for more information.

    dotnet tool install --global dotnet-ef
    dotnet ef migrations add InitialCreate
    dotnet ef database update

    The output of the commands is displayed below.

    [EFGetStarted]# dotnet ef migrations add InitialCreate
    Build started...
    Build succeeded.
    Done. To undo this action, use 'ef migrations remove'
    [EFGetStarted]# dotnet ef database update
    Build started...
    Build succeeded.
    Applying migration '20221208110815_InitialCreate'.
    Done.
  9. Build and run the program. The output of the program after successful run should look as follows:

    [EFGetStarted]# dotnet run
    Database path: /root/.local/share/blogging.db.
    Inserting a new blog
    
    Dumping database snapshot...
    Number of records = 1
    BlogId = 1, Url = http://blogs.msdn.com/adonet
    
    
    Updating the blog and adding a post
    
    Dumping database snapshot...
    Number of records = 1
    BlogId = 1, Url = https://devblogs.microsoft.com/dotnet
    ----->PostId = 1, Title = Hello World, Content = I wrote an app using EF Core!
    
    
    Delete the blog
    
    Dumping database snapshot...
    Number of records = 0
    
    
    

Conclusion

The ability to connect from .NET applications on Linux on Power to a SQLite database using EF Core, will enable IBM Power customers to modernize their applications.

References

Permalink