4 - Entity Framework

At the end of the previous practice, an in-memory database has been created to store products. This week this solution is replaced by an SQLite-based solution. To do so, two new NuGet packages should be added to the Common project:

• Microsoft.EntityFrameworkCore.Sqlite
• Microsoft.EntityFrameworkCore.Tools

After the two packages are installed, the Common.csproj file should look like as follows:

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="10.0.3">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="System.Drawing.Common" Version="10.0.3" /> <!-- installed in previous weeks -->
</ItemGroup>

Entity Framework

Entity Framework is a modern object-relation mapper that lets you build a clean, portable, and high-level data access layer with .NET (C#) across a variety of databases, including SQL Database (on-premises and Azure), SQLite, MySQL, PostgreSQL, and Azure Cosmos DB. It supports LINQ queries, change tracking, updates, and schema migrations.²

Entity Framework was first released in 2008 as part of the .NET Framework. Since then it has gone through several evolutions. New versions of Entity Framework Core are shipped at the same time as new .NET versions.³

EF Core can serve as an object-relational mapper (ORM), which:

• Enables .NET developers to work with a database using .NET objects.
• Eliminates the need for most of the data-access code that typically needs to be written.⁴

Data access is performed using a model. A model is made up of entity classes and a context object that represents a session with the database. The context object allows querying and saving data.

EF supports the following model development approaches:

• Generate a model from an existing database.
• Hand-code a model to match the database.
• Once a model is created, use EF Migrations to create a database from the model. Migrations allow evolving the database as the model changes.

Entity Framework ORM considerations

While EF Core is good at abstracting many programming details, there are some best practices applicable to any ORM that help to avoid common pitfalls in production apps:

• Intermediate-level knowledge or higher of the underlying database server is essential to architect, debug, profile, and migrate data in high performance production apps. For example, knowledge of primary and foreign keys, constraints, indexes, normalization, DML and DDL statements, data types, profiling, etc.
• Functional and integration testing: It's important to replicate the production environment as closely as possible to:
  • Find issues in the app that only show up when using a specific version or edition of the database server.
  • Catch breaking changes when upgrading EF Core and other dependencies.
• Performance and stress testing with representative loads. The naive usage of some features doesn't scale well. For example, multiple collections includes, heavy use of lazy loading, conditional queries on non-indexed columns, massive updates and inserts with store-generated values, lack of concurrency handling, large models, inadequate cache policy.
• Security review: For example, handling of connection strings and other secrets, database permissions for non-deployment operation, input validation for raw SQL, encryption for sensitive data. See Secure authentication flows for secure configuration and authentication flow.
• Make sure logging and diagnostics are sufficient and usable. For example, appropriate logging configuration, query tags, and Application Insights.
• Error recovery. Prepare contingencies for common failure scenarios such as version rollback, fallback servers, scale-out and load balancing, DoS mitigation, and data backups.
• Application deployment and migration. Plan out how migrations are going to be applied during deployment; doing it at application start can suffer from concurrency issues and requires higher permissions than necessary for normal operation. Use staging to facilitate recovery from fatal errors during migration. For more information, see Applying Migrations.
• Detailed examination and testing of generated migrations. Migrations should be thoroughly tested before being applied to production data. The shape of the schema and the column types cannot be easily changed once the tables contain production data. For example, on SQL Server, nvarchar(max) and decimal(18, 2) are rarely the best types for columns mapped to string and decimal properties, but those are the defaults that EF uses because it doesn't have knowledge of your specific scenario.

In the current example, we implement a model using "Code First". Luckily our model has been created previously, it's just a refresher:

namespace Common.Model
{
    public class Product
    {
        public int ID { get; set; }
        public string? Name { get; set; }
        public string? Description { get; set; }
        public float Price { get; set; }
        public System.Drawing.Image? Image { get; set; }
        public DateTime PackagingDate { get; set; }
        public DateTime ExpirationDate { get; set; }
    }
}

Code First vs Database First

• Code-first: You define the data model using C# classes. EF uses these to generate the database schema. This approach works well for domain-driven projects where code defines structure.

• Database-first: You start with an existing schema. EF reverse-engineers it into data model classes. This is a better fit for legacy systems or when the database design comes first.

In EF Core, a class derived from DbContext is used to configure entity types in a model and act as a session for interacting with the database. In the simplest case, a DbContext class¹:

• Contains DbSet properties for each entity type in the model.
• Overrides the OnConfiguring method to configure the database provider and connection string to use.

Create a class called ApiaryContext in the Database folder under the Common project:

namespace Common.Database
{
    public class ApiaryContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=absolute/path/to/the/apiary.sqlite");
        }
    }
}

The DbSet<Product> Products property represents the entities stored in the database. It can be queried to collect data and new items can be inserted to save new data.

To specify the location of the database, the OnConfiguring method should be overridden. The optionsBuilder.UseSqlite call defines that an SQLite database will be used, and the Data Source=... sets the path of the database. As three different projects will use this database (Common, Administration, and the future webshop), and absolute path is a convenient and working solution to set the database.

Entity Framework Context

The primary class the application interacts with is System.Data.Entity.DbContext (often referred to as the context class). You can use a DbContext associated to a model to⁵:

• Write and execute queries,
• Materialize query results as entity objects,
• Track changes that are made to those objects,
• Persist object changes back on the database,
• Bind objects in memory to UI controls.

The recommended way to work with context is to define a class that derives from DbContext and exposes DbSet properties that represent collections of the specified entities in the context. If you are working with the EF Designer, the context will be generated for you. If you are working with Code First, you will typically write the context yourself.

public class ProductContext : DbContext
{
    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }
}

Once you have a context, you would query for, add (using Add or Attach methods ) or remove (using Remove) entities in the context through these properties. Accessing a DbSet property on a context object represent a starting query that returns all entities of the specified type. Note that just accessing a property will not execute the query. A query is executed when:

• It is enumerated by a foreach (C#) statement.
• It is enumerated by a collection operation such as ToArray, ToDictionary, or ToList.
• LINQ operators such as First or Any are specified in the outermost part of the query.
• One of the following methods are called: the Load extension method, DbEntityEntry.Reload, Database.ExecuteSqlCommand, and DbSet<T>.Find, if an entity with the specified key is not found already loaded in the context.

The lifetime of the context begins when the instance is created and ends when the instance is either disposed or garbage-collected. Use using if you want all the resources that the context controls to be disposed at the end of the block. When you use using, the compiler automatically creates a try/finally block and calls dispose in the finally block.

public void UseProducts()
{
    using (var context = new ProductContext())
    {
        // Perform data access using the context
    }
}

Here are some general guidelines when deciding on the lifetime of the context:

• When working with Web applications, use a context instance per request.
• When working with Windows Presentation Foundation (WPF) or Windows Forms, use a context instance per form. This lets you use change-tracking functionality that context provides. (In the tiny Apiary project we use a context instance for the whole application lifecycle.)
• If the context instance is created by a dependency injection container, it is usually the responsibility of the container to dispose the context.
• If the context is created in application code, remember to dispose of the context when it is no longer required.
• When working with long-running context consider the following:
  • As you load more objects and their references into memory, the memory consumption of the context may increase rapidly. This may cause performance issues.
  • The context is not thread-safe, therefore it should not be shared across multiple threads doing work on it concurrently.
  • If an exception causes the context to be in an unrecoverable state, the whole application may terminate.
  • The chances of running into concurrency-related issues increase as the gap between the time when the data is queried and updated grows.

When the ApiaryContext is ready, we can tell the EntityFramework that the database is ready to be created. In Visual Studio, Tools -> NuGet Package Manager -> Package Manager Console is the right place to do that. However, first, the startup project should be changed from the Administration to the Common. This step is needed, so that the Entity Framework can work properly.

Add-Migration InitialCreate
# dotnet cli: dotnet ef migrations add InitialCreate

Entity Framework Migrations

In real world projects, data models change as features get implemented: new entities or properties are added and removed, and database schemas need to be changed accordingly to be kept in sync with the application.⁶ The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application's data model while preserving existing data in the database.

At a high level, migrations function in the following way:

• When a data model change is introduced, the developer uses EF Core tools to add a corresponding migration describing the updates necessary to keep the database schema in sync. EF Core compares the current model against a snapshot of the old model to determine the differences, and generates migration source files; the files can be tracked in your project's source control like any other source file.
• Once a new migration has been generated, it can be applied to a database in various ways. EF Core records all applied migrations in a special history table, allowing it to know which migrations have been applied and which haven't.

The command compiles the project, searches for the implemented context class and tries to build a database schema based on the given information. The following error message should be given after the Add-Migration:

Unable to create a 'DbContext' of type 'ApiaryContext'. The exception 'The property
'Image.Tag' could not be mapped because it is of type 'object', which is not a
supported primitive type or a valid entity type. Either explicitly map this property,
or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore'
in 'OnModelCreating'.' was thrown while attempting to create an instance. For the
different patterns supported at design time, see
https://go.microsoft.com/fwlink/?linkid=851728

The reason behind this error message is the Image property in the Product class. A solution might be to change that property to something else (e.g., saving the image to the local file system instead of the database and only store its url for reference), however, it can be also converted to an SQLite-compatible type before saving, and converting back to a C# type when needed.

Create a new folder called Util in the Common project and a new class called ImageToBytesConverter.cs. It should be derived from the ValueConverter class and specify the from-to types Image, byte[].

using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace Common.Util
{
    public class ImageToBytesConverter : ValueConverter<Image, byte[]>
    {
        public ImageToBytesConverter() : base (
            img => ImageToBytes(img),
            bytes => BytesToImage(bytes))
            { }

        private static byte[] ImageToBytes(Image img)
        {
            using var ms = new MemoryStream();
            img.Save(ms, ImageFormat.Png);
            return ms.ToArray();
        }

        private static Image BytesToImage(byte[] bytes)
        {
            if (bytes == null || bytes.Length == 0)
            {
                return null;
            }

            using var ms = new MemoryStream(bytes);
            return Image.FromStream(ms);
        }
    }
}

Also this class should be registered in the ApiaryContext to be used when storing and retrieving objects.

using Common.Util;
// ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .Property(p => p.Image)
        .HasConversion<ImageToBytesConverter>();
}

When these changes are made, the Add-Migration should work properly. A new folder is created, called Migrations and two new C# sources are generated there.

• {timestamp}_{migration_name}.cs: contains the current model changes compared to the previous one (currently compared to an empty model),
• {context_name}ModelSnapshot.cs: contains the current schema of the database

Ideally these files are generated by the Entity Framework and do not change them by hand.

namespace Common.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Products",
                columns: table => new
                {
                    ID = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Name = table.Column<string>(type: "TEXT", nullable: true),
                    Description = table.Column<string>(type: "TEXT", nullable: true),
                    Price = table.Column<float>(type: "REAL", nullable: false),
                    Image = table.Column<byte[]>(type: "BLOB", nullable: true),
                    PackagingDate = table.Column<DateTime>(type: "TEXT", nullable: false),
                    ExpirationDate = table.Column<DateTime>(type: "TEXT", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Products", x => x.ID);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(name: "Products");
        }
    }
}

namespace Common.Migrations
{
    [DbContext(typeof(ApiaryContext))]
    partial class ApiaryContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("ProductVersion", "10.0.3");

            modelBuilder.Entity("Common.Model.Product", b =>
                {
                    b.Property<int>("ID")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("INTEGER");

                    b.Property<string>("Description")
                        .HasColumnType("TEXT");

                    b.Property<DateTime>("ExpirationDate")
                        .HasColumnType("TEXT");

                    b.Property<byte[]>("Image")
                        .HasColumnType("BLOB");

                    b.Property<string>("Name")
                        .HasColumnType("TEXT");

                    b.Property<DateTime>("PackagingDate")
                        .HasColumnType("TEXT");

                    b.Property<float>("Price")
                        .HasColumnType("REAL");

                    b.HasKey("ID");

                    b.ToTable("Products");
                });
        }
    }
}

As the last step, in the Package Manager Console, a database update should be started:

Update-Database
# dotnet cli: dotnet ef database update

The process would check the current state of the database and compare it to the latest snapshot. Any changes are made in the model will be pushed down to the database, i.e., it's actualized. (If any breaking changes are made, data loss can happen. Pay attention to these Migrations.)

If the database is up and ready, is't the time of the new implementation of the IDatabase.cs.

using Common.Model;

namespace Common.Database
{
    public class SQLiteDatabase : IDatabase, IDisposable
    {
        private ApiaryContext context;

        public SQLiteDatabase()
        {
            context = new ApiaryContext();
        }

        public void Dispose()
        {
            context?.Dispose();
        }

        public void Delete(int productID)
        {
            Product? product = context.Products.Where(x => x.ID == productID).FirstOrDefault();

            if (product == null)
                throw new KeyNotFoundException($"Product with ID = {productID} is not found");

            context.Products.Remove(product);
        }

        public IEnumerable<Product> List()
        {
            return context.Products.ToList();
        }

        public void Store(Product product)
        {
            context.Products.Add(product);
            context.SaveChanges();
        }

        public void Update(Product product)
        {
            context.Products.Update(product);
            context.SaveChanges();
        }
    }
}

In the program startup, the dependency injection is configured to use the InMemoryDatabase every time when an IDatabase implementation is required. If that is changed, the whole application uses this new SQLite-based database implementation:

var host = Host.CreateDefaultBuilder()
    .ConfigureServices((context, services) =>
    {
-       services.AddSingleton <Common.Database.IDatabase, Common.Database.InMemoryDatabase>();
+       services.AddSingleton <Common.Database.IDatabase, Common.Database.SQLiteDatabase>();
        services.AddTransient<Start>();
        services.AddTransient<ProductAdd>();
    }).Build();

Don't forget to change the startup project back to the Administration and start the application.

Lambda expressions and anonymous functions

Use a lambda expression to create an anonymous function. Use the lambda declaration operator => to separate the lambda's parameter list from its body.⁷

A lambda expression can be any of the following two forms:

• Expression lambda that has an expression as its body:

  (input-parameters) => expression
  

• Statement lambda that has a statement block as its body:

  (input-parameters) => { <sequence-of-statements> }
  

To create a lambda expression, specify input parameters (if any) on the left side of the lambda operator and an expression or a statement block on the other side. You can convert any lambda expression to a delegate type. The types of its parameters and return value define the delegate type to which a lambda expression can be converted. If a lambda expression doesn't return a value, convert it to one of the Action delegate types. If it returns a value, convert it to one of the Func delegate types.

For example, convert a lambda expression that has two parameters and returns no value to an Action<T1,T2> delegate. Convert a lambda expression that has one parameter and returns a value to a Func<T,TResult> delegate.

In the following example, the lambda expression x => x * x, which specifies a parameter named x and returns the value of x squared, is assigned to a variable of a delegate type:

Func<int, int> square = x => x * x;
Console.WriteLine(square(5));
// Output:
// 25

You can also use lambda expressions when you write LINQ in C#, as the following example shows:

int[] numbers = { 2, 3, 4, 5 };
var squaredNumbers = numbers.Select(x => x * x);
Console.WriteLine(string.Join(" ", squaredNumbers));
// Output:
// 4 9 16 25

Input parameters of a lambda expression

Enclose input parameters of a lambda expression in parentheses. Specify zero input parameters by using empty parentheses:

Action line = () => Console.WriteLine();
line();

If a lambda expression has only one input parameter, you can omit the parentheses:

Func<double, double> cube = x => x * x * x;
double result = cube(3.14);

Separate two or more input parameters with commas:

Func<int, int, bool> testForEquality = (x, y) => x == y;
bool equal = testForEquality(3, 4);

The compiler typically infers the types for parameters to lambda expressions, which is called an implicitly typed parameter list. You can specify the types explicitly, which is called an explicitly typed parameter list. The following example shows an explicitly typed parameter list:

Func<int, string, bool> isTooLong = (int x, string s) => s.Length > x;
bool long = isTooLong(10, "too long");

Explicit return type

Typically, the return type of a lambda expression is obvious and inferred. For some expressions, that inference doesn't work:

var choose = (bool b) => b ? 1 : "two"; // ERROR: Can't infer return type

You can specify the return type of a lambda expression before the input parameters. When you specify an explicit return type, you must parenthesize the input parameters:

var choose = object (bool b) => b ? 1 : "two"; // Func<bool, object>

The progress of the Apiary project can be accessed: apiary-practice-4.zip.

References

---

1. Getting Started with Windows Forms ↩

2. Entity Framework documentation hub ↩

3. Entity Framework Support Policies ↩

4. Entity Framework Core ↩

5. Working with DbContext ↩

6. Migrations Overview ↩

7. Lambda expressions and anonymous functions ↩

---

Last update: 2026-03-09 11:47:47