Skip to content

Latest commit

 

History

History
817 lines (556 loc) · 26.4 KB

Slides.md

File metadata and controls

817 lines (556 loc) · 26.4 KB
theme _class paginate backgroundColor backgroundImage footer headingDivider marp style
gaia
lead
true
![width:300px](images/banner-transparent.png)
1
true
section { font-size: 25px; } container { height: 300px; width: 100%; display: block; justify-content: right; text-align: right; } header { float: right; } a { color: blue; text-decoration: underline; background-color: lightgrey; font-size: 80%; } table { font-size: 22px; }

BDSA: Session 6

Intro to EF Core with SQLite, LINQ, and Onion Architecture

bg right:50% width:100%

Adrian Hoff Postdoctoral Researcher ITU

What to expect from videos and slides

  • Slides: contain a lot of text

    • Goal: material that is self-contained (in the sense of: without videos)
      • e.g., you can skip through slides while working on the project without having to guess what I meant
  • Videos: focus on what is said

    • I will show slides mainly for illustrations

  Slides and videos explain background of the topics explored in the resp. week's project work

Suggestion: Watch the video before working on the project, use the slides while working on the project

Reflections on last week's project

Last week, we integrated SQLite as our database solution.

  • We defined our database schema via SQLite queries (schema.sql)
  • We defined our application data model via C# classes
  • We formulated queries to the database as strings with SQL statements
  • We accessed the database via an SQLite specific library

Think about drawbacks of this approach!

Reflections on last week's project (ctd.)

  • What happens if our domain model changes? (It will!)
    • Requires adjusting both the object-oriented C# version and the relational (tabular) SQL schema
      • High degree of manual effort
      • Invites mismatches between the two worlds
        • for instance, we used different terminology in domain model and in database schema, e.g., user vs. author and message vs. cheep
  • What happens if we want to switch from SQLite to another database solution?
    • Requires rewriting database access code (other database library API)
    • Requires rewriting queries (due to possible particularities in SQL syntax)
  • Querying via strings does not allow for compile time validation

Object-Relational Mapping (ORM)

An ORM framework bridges the gap between the world of objects in programming languages* and the relational (tabular) database world.

  • Database schemas and access to content are abstracted away into high-level APIs of the target programming language
    • Programmers don't write SQL directly. They formulate queries, e.g., via method calls
  • ORM frameworks intend to ease and speed up the development process when working with databases
  • Example ORM frameworks:
    • Hibernate for Java
    • Entity Framework Core (EF Core) for .NET

* in a broader sense than "object-oriented", there exist ORM frameworks for languages like PHP and Python, too

Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology.

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.
  • EF Core supports many database engines (see Database Providers for details):
    • SQLite
    • PostgreSQL
    • MongoDB
    • (many more) ...

EF Core: What is mapped to what?

bg 52%                       Image source: Andrew Lock ASP.NET Core in Action, Third Edition

Preview: SQL in strings vs. LINQ and EF Core

Querying using a string containing SQL:

databaseFacade.ExecuteQuery("SELECT * FROM message" +
    " INNER JOIN user ON message.author_id=user.user_id" +
    $" ORDER BY message.pub_date DESC LIMIT {32} OFFSET {32 * page}",
    (IDataRecord record) => cheeps.Add(new { record.GetString(...), ..., ... }));

Querying using EF Core and LINQ (expressions are automatically translated to SQL):

var query = (from cheep in dbContext.Cheeps
            orderby cheep.TimeStamp descending
            select cheep)
            .Include(c => c.Author)
            .Skip(page * 32).Take(32);
var result = await query.ToListAsync();

If your first impression is "that looks scary"; I agree! Just wait, we will get there 🙂

Strength and weaknesses of an ORM

  • Weaknesses

    • ORMs hide the underlying database from you
      • (Complex queries might translate to suboptimal SQL causing performance problems)
    • ORMs can have a steep learning curve
  • Strength

    • Faster development
      • Write less code: less boilerplate database code
    • Consistency between domain model and database schema
    • Security: ORMs take care of a variety of security checks (e.g., SQL injection)

Working with EF Core (Code First Approach)

  1. Install EF Core
  2. Define your domain model (C# classes)
  3. Give EF Core context on your database
  4. Let EF Core create a migration
  5. Execute the migration
  6. Write database-technology-independent queries using LINQ

... Life happens, your domain model requires changes!

  1. Change your domain model, update your migration, goto Step 4

  For a detailed description of each step, refer to Section 12 of Andrew Lock ASP.NET Core in Action, Third Edition

0. Install EF Core

  • Create a new Razor application

    • Use the template from last week: dotnet new chirp-razor -o MyChat.Razor
    • Refactor the CheepService class (+ interface): rename it to ChatService
  • Use NuGet to install EF Core to your new Razor application

    • The required packages depend on the specific database you want to use
    • We want to use SQLite. Install the following packages (for .NET 7):
    • Microsoft.EntityFrameworkCore.SQLite - This package is the main database provider package for using EF Core at runtime. It also contains a reference to the main EF Core NuGet package.
    • Microsoft.EntityFrameworkCore.Design - This package contains shared build-time components for EF Core, required for building the EF Core data model for your app. Taken from Section 12 of Andrew Lock ASP.NET Core in Action, Third Edition

1. Define your domain model

Wikipedia:

In software engineering, a domain model is a conceptual model of the domain that incorporates both behavior and data

EF Core will later translate our domain model (consisting of plain old class objects - POCO) to a database schema

bg right width:60%

Side Note: Attributes ending on 'Id' have a special meaning to EF Core. They will be translated to keys. See Section 12 of Andrew Lock ASP.NET Core in Action, Third Edition

Why did we not use C# records again?

So far, you saw many examples of records that we used to model data, e.g., the CheepViewModel in our chrip-razor project template.

Value equality

For records, value equality means that two variables of a record type are equal if the types match and all property and field values match. For other reference types such as classes, equality means reference equality. That is, two variables of a class type are equal if they refer to the same object. ...

Not all data models work well with value equality. For example, Entity Framework Core depends on reference equality to ensure that it uses only one instance of an entity type for what is conceptually one entity. For this reason, record types aren't appropriate for use as entity types in Entity Framework Core.

Source: Introduction to record types in C♯

Example: Value equality vs. reference equality

record User(int UserId, string Name);
User user1 = new User(1, "Adrian");
User user2 = new User(1, "Adrian");
Console.WriteLine($"Are the record objects equal? {(user1 == user2 ? "Yes" : "No")}");

Value equality - the snippet prints Are the record objects equal? Yes

class User {
  int UserId { get; set; }
  string Name { get; set; }
}
User user1 = new User() { UserId = 1, Name = "Adrian" };
User user2 = new User() { UserId = 1, Name = "Adrian" };
Console.WriteLine($"Are the class objects equal? {(user1 == user2 ? "Yes" : "No")}");

Reference equality - the snippet prints Are the class objects equal? No

1. Define your domain model

Implement the depicted domain model

  • use classes, not records
  • implement attributes as auto-properties ({ get; set; })
    • otherwise, EF Core will not consider your attributes during the mapping process

bg right width:60%

2. Give EF Core context on your database

Provide EF Core with a database context class exposing top-level db entities.

  • extends EF Core's DbContext class
  • has a constructor with parameter of type DbContextOptions<ChatDBContext>
    • needs to call parent constructor
  • has auto-properties ({ get; set; }) of type DbSet<T> for each top-level db entity T

(We use this context class later when querying from the database)

bg right:49% width:100%

2. Give EF Core context on your db schema (ctd.)

Register your database context in the application builder. Program.cs:

var builder = WebApplication.CreateBuilder(args);

// Load database connection via configuration
string? connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ChatDBContext>(options => options.UseSqlite(connectionString));

appsettings.json:

{
    "Logging": { ... },
    "ConnectionStrings": {
        "DefaultConnection": "Data Source=Chat.db"
    },
    ...
}

Interim Results: Where are we now?

  1. ✅ Install EF Core
  2. ✅ Define your domain model
  3. ✅ Give EF Core context on your db  
  4. ➡️ Let EF Core create a migration  
  5. ❓ Execute the migration
  6. ❓ Write queries using LINQ
  7. ❓ Change domain model & update db

bg right:50% width:100%

What is data migration??

Data migration is the process of transferring data from one storage system or computing environment to another.

Source: IBM

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.

Source: Microsoft

bg right:50% width:100%

3. Let EF Core create an initial migration

bg right:50% width:100%

  • You can install the .NET EF command line tool to create a migration: dotnet tool install --global dotnet-ef --version 7

  • Once installed, the tool can be used to create a first migration: dotnet ef migrations add InitialDBSchema

    • Run from within project folder
    • This generates three files, capturing the migration process
    • Inspect them to see what they do

4. Execute the migration

bg right:50% width:100%

Run dotnet ef database update (from within project folder).

The tool will apply the migration, i.e., it will create a database with tables according to your domain model and database context class.

4. Execute the migration (ctd.)

Alternatively, you can execute a migration from code. For instance, in Program.cs, you could run the following snippet:

// Create a disposable service scope
using (var scope = app.Services.CreateScope())
{
    // From the scope, get an instance of our database context.
    // Through the `using` keyword, we make sure to dispose it after we are done.
    using var context = scope.ServiceProvider.GetService<ChatDBContext>();

    // Execute the migration from code.
    context.Database.Migrate();
}

Hint You could access services from the DI container in other parts of your application, too. However, that is an anti pattern! You should inject services in constructors as shown later.

5. Write database queries using LINQ

Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language. Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support. Furthermore, you have to learn a different query language for each type of data source: SQL databases, XML documents, various Web services, and so on. With LINQ, a query is a first-class language construct, just like classes, methods, and events.

Source: Microsoft Documentation

  • LINQ queries are...
    • ... "first-class language constructs": They are checked for compile-time errors
    • ... (database-)technology agnostic: Queries we write this week with SQLite as database technology would still work if we replaced it with PostgreSQL next week

5. Write database queries using LINQ (ctd.)

LINQ query syntax:

// Define the query - with our setup, EF Core translates this to an SQLite query in the background
var query = from message in dbContext.Messages
            where message.User.Name == "Adrian"
            select new { message.Text, message.User };
// Execute the query and store the results
var result = await query.ToListAsync();

LINQ method syntax:

// Define the query - with our setup, EF Core translates this to an SQLite query in the background
var query = dbContext.Messages
            .Where(message => message.User.Name == "Adrian")
            .Select(message => new { message.Text, message.User });
// Execute the query and store the results
var result = await query.ToListAsync();

5. Write database queries using LINQ (ctd.)

            select new { message.Text, message.User };
            .Select(message => new { message.Text, message.User });

What is that syntax???

Anonymous types = on-the-fly read-only object creation without type declaration

var question = new
{
    Title = "The answer...?",
    Answer = 42
};

5. Write database queries using LINQ (ctd.)

            .Select(message => new { message.Text, message.User });

What is that syntax???

Lambda Expressions = compact, anonymous functions

C# lambda without return value (void):

Action<string> write = s => Console.WriteLine(s);

C# lambda with return value:

Func<float, int> round = f => (int)Math.Round(f);

Side Note: There exist other kinds of lambdas from older C# versions which are special cases of Func<Tin,Tout>, e.g., Predicate<T> which is equivalent to Func<T,bool>

5. Write database queries using LINQ (ctd.)

var query = dbContext.Messages.Where(...)...

What is that method???

We never defined a method Where(...) in our Cheep domain model class, did we?

Extension Methods = methods added to a type without modifying the type

public static class MyExtensions {
    public static int WordCount(this string str) {
        return str.Split(new[] { ' ', '.', '?' }, StringSplitOptions.RemoveEmptyEntries).Length;
    }
}
int count = "Hello".WordCount();

5. Write database queries using LINQ (ctd.)

Other useful C# language constructs

Tuples = Concise, lightweight data structure definition

var s = Tuple.Create("Clark Kent", "Superman");

var b = ("Bruce Wayne", "Batman");

var f = (name: "Barry Allen", alterEgo: "The Flash");

var random = new Random();
IEnumerable<(float x, float y)> GenerateCoordinates() {
    yield return (random.NextSingle() * 100, random.NextSingle() * 100);
}

Find information about differences between anonymous types and tuples here.

5. Write database queries using LINQ (ctd.)

Other useful C# language constructs

IEnumerable<User> users = new[] {
    new User(1, "Adrian"),
    new User(2, "Helge")
};
Message[] messages = new Message[] {
    new Message() { MessageId = 81, Text = "Hello", UserId = 1, User = u1 },
    new Message() { MessageId = 82, Text = "Hi", UserId = 2, User = u2 },
    new Message() { MessageId = 83, Text = "What's up?", UserId = 1, User = u1 },
    new Message() { MessageId = 84, Text = "Nothing", UserId = 2, User = u2 }
};

Repository Pattern

The Repository pattern [intends] to keep persistence concerns outside of the system's domain model. [..] Repository implementations are classes that encapsulate the logic required to access data sources.

Text and Image Source: Microsoft
  • Recommendation: One repository per aggregate
    • Order in slide example
    • Message in running example

bg right:56.5% 100%

Putting it all together: Repository pattern

bg right:35% 100%

  • Create a message repository, i.e., interface and class

    • For now, you may leave the methods empty
  • Register the repository in your Razor app via DI

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
builder.Services.AddScoped<IChatService, ChatService>();
builder.Services.AddScoped<IMessageRepository, MessageRepository>();

What is the difference between AddScoped() and AddSingleton() ? (cf. Section 9.4 in the book!)

(At this point, we will stop implementing the repository in the lecture, but you will need to continue with this in your project work!)

5. Writing database queries - final steps

Writing queries with EF Core requires an instance of your database context (see Step 2).

  • How do we get access to in our repository?
    • We do not want the repository to depend on any higher-level services.
  • The answer is (you guessed it): Dependency Injection!
public class ChatRepository : IChatRepository
{
    private readonly ChatDBContext _dbContext;
    public ChatRepository(ChatDBContext dbContext)
    {
        _dbContext = dbContext;
    }
    ...

5. Writing database queries - final steps (ctd.)

Finally, we can use our database context to start querying - example read query:

public async Task<List<MessageDTO>> ReadMessages(string userName)
{
  // Formulate the query - will be translated to SQL by EF Core
  var query = _dbContext.Messages.Select(message => new { message.User, message.Text });
  // Execute the query
  var result = await query.ToListAsync();

  // ...
}

Beware that the first statement specifies a query, while the second statement executes the query on our database. (More information)

  • The variable query represents the query, not the results from executing it.
  • The variable result contains results from executing the query (note the use of await)

5. Writing database queries - final steps (ctd.)

Example create method:

public async Task<int> CreateMessage(MessageDTO message)
{
    Message newMessage = new() { Text = message.Text, ... };
    var queryResult = await _dbContext.Messages.AddAsync(newMessage); // does not write to the database!

    await _dbContext.SaveChangesAsync(); // persist the changes in the database
    return queryResult.Entity.CheepId;
}

Update and delete operations work similarly.

Note: The above method persists the changes directly. Depending on your use-case, you might want to deviate from that approach. For instance, when creating multiple elements, you might want to add them first (e.g., multiple _dbContext.Message.Add(...)) and eventually persist the changes (_dbContext.SaveChanges(), e.g., in another method of your repository).

6. Change your domain model and update your migration

bg right:50% width:100%

Add a property for an email address to the User domain model class.

Run dotnet ef migrations add UsersEMail

  • Inspect the generated migration files
  • Execute the migration (cf. Step 4)
    • Run dotnet ef database update (from within project folder)
  • Inspect the altered database

Summary

Entity Framework Core = Object-Relational Mapper

  1. ✅ Define your domain model (C# classes)
  2. ✅ Give EF Core context on your database (ChatDBContext class)
  3. ✅ Let EF Core create a migration (e.g., via .NET CLI tool)
  4. ✅ Execute the migration (via .NET CLI tool or via C# code)
  5. ✅ Write database-technology-independent queries using LINQ
    • LINQ allows to formulate queries (i.a., for quering from relational databases) via C# language constructs (e.g., extension methods)

... Life happens, your domain model requires changes

  1. ✅ Update your migration
    • goto Step 4

What to do now?

w:400px


Feedback

bg right:70% 100%

All groups are working on their projects!


Feedback

<iframe src="http://209.38.208.62/report_razor_apps.html" width="100%" height=600 scrolling="auto"></iframe>

Feedback

  • Have one main branch called main.
  • Remember, we do trunk-based development in this course. That is, you do not have a long-lived dev branch (as in last semester's project). That would be another branching strategy.
  • You have short-lived feature branches. That is, at latest after a day your changes land on the main branch and thereby automatically in production (your deployment workflows deploy all changes from main, right?)

What do we do if we cannot finish our feature during a day?

  • Good observation, that means likely that your task descriptions in your issues are too coarse grained. Over time you should train to make them smaller so that you can complete your tasks in max. a day.

Feedback

  • Have one main branch called main.
  • It is not advisable to have a main2.0 branch.
  • Likely, it is also not a good idea to call a branch origin since it is confusing when pointing to remote repositories in git commands.

Process: Scientific problem solving

Starting this week, you are going to work more independently. Tasks will be less guided and you will need to research on your own.

How to do this systematically:

  1. Identify the problem
  2. Gather information
  3. Iterate potential solutions
  4. Test your solution
Source: E. Cain 4 steps to solving any software problem

Use the book! Use the internet, e.g., Microsoft documentation!