SQL

#CASBAN6: Implementing the data model using EntityFramework Core (separate libraries)

#CASBAN6: Implementing the data model using EntityFramework Core (separate libraries)

EntityModel library

When I started the project, I started with creating the classes for all the tables that I need for my blog engine. I have put them into their own library to keep things clean.

The classes also use ICollection references for relationships whenever required. Let’s have a look at the Blog class:

public class Blog 
{
    public Guid BlogId { get; set; }

    public string Name { get; set; }

    public string Slogan { get; set; }

    public Uri LogoUrl { get; set; }

    public ICollection<Post> Posts { get; set; }

    public ICollection<Author> Authors { get; set; }

    public ICollection<Tag> Tags { get; set; }

    public ICollection<Medium> Media { get; set; }

}

The other classes are implemented similarly to reflect the data model I showed you in my last post. You can have a look at the other class implementations in the GitHub repo (folder: EntityModel).

EFCore library

The EFCore library has three main components:

  1. BlogContext
  2. Configurations
  3. Seed extension

BlogContext

The BlogContext is straight forward and follows the pattern described here in the documentation for using a factory (spoiler: we will do that later):

public sealed class BlogContext : DbContext
{
    public BlogContext(DbContextOptions<BlogContext> options) : base(options)
    {

    }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<Author> Authors { get; set; }
    public DbSet<MSiccDev.ServerlessBlog.EntityModel.Medium> Media { get; set; }
    public DbSet<MediumType> MediaTypes { get; set; }
    public DbSet<Tag> Tags { get; set; }
}

The class is declaring a constructor that uses the DBContextOptions<DBContext> parameter that allows our factory to configure the context later on for the migrations. Of course, we need references to all the possible DbSets as well to be able to access them via the BlogContext instance.

Configurations

In Entity Framework, we can configure our tables with configurations. By implementing the IEntityTypeConfiguration interface for all of our models in a separate file for each, we are continuing to keep our code clean and easily maintainable. Here is how the implementation for the Blog table:

public class BlogConfiguration : IEntityTypeConfiguration<Blog>
{
    public void Configure(EntityTypeBuilder<Blog> builder)
    {
        builder.Property(nameof(Blog.BlogId)).
            IsRequired();

        builder.Property(nameof(Blog.BlogId)).
            ValueGeneratedOnAdd();

        builder.HasKey(blog => blog.BlogId).
            HasName($"PK_{nameof(Blog.BlogId)}");

        builder.Property(nameof(Blog.Name)).
            HasMaxLength(255).
            IsRequired();

        builder.Property(nameof(Blog.Slogan)).
            HasMaxLength(255).
            IsRequired();

        builder.Property(nameof(Blog.LogoUrl)).
            IsRequired();
    }
}

Most of the fluent implementations above are self-explaining. The other classes of the project are implemented in a similar way, you can find them here in the Github repository.

I implemented my configurations by following the docs, which I absolutely recommend reading:

To apply the configurations, we need to override the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new BlogConfiguration());
    modelBuilder.ApplyConfiguration(new MediumypeConfiguration());
    modelBuilder.ApplyConfiguration(new MediumConfiguration());
    modelBuilder.ApplyConfiguration(new AuthorConfiguration());
    modelBuilder.ApplyConfiguration(new TagConfiguration());
    modelBuilder.ApplyConfiguration(new PostConfiguration());
}

Seed extension

To verify our configurations are working, we need some test data. This is where the seeding feature of EF Core comes in handy, and it helped me to improve my data model a lot. I am using the extension method approach here to implement a blog with three test posts, including all relations, constraints, and property configurations. You can find the full implementation here in the Github repository.

Besides the docs on EF Core data seeding, these links helped me to understand and write my seed implementation:

With this extension method in place, applying the seed is just one line of code at the end of the OnModelCreating override away:

modelBuilder.Seed();

Now we have everything together, we finally can turn to actually migrate our code to database.

EFCore.DesignDummy library

Because I am running this whole thing on a Mac, I need to use the CLI tools for all migrations. To keep also this step in its own library, I created a DesignDummy library which I use for pushing the migrations to my local database.

The library requires a reference to the EFCore library as well as to the EntityModel library. On top of that, we need the Microsoft.EntityFrameworkCore.Design NuGet package.

Now that our dependencies are in place, we just need to create an implementation of the IDesignTimeDbContextFactory interface as described here in the docs:

public class BlogContextFactory : IDesignTimeDbContextFactory<BlogContext>
{
    public BlogContext CreateDbContext(string[] args)
    {
        BlogContext? instance = null;

        var optionsBuilder = new DbContextOptionsBuilder<BlogContext>();

        optionsBuilder.UseSqlServer(dbContextBuilder =>
            dbContextBuilder.MigrationsAssembly("EFCore.DesignDummy")).
            EnableSensitiveDataLogging();

        instance = new BlogContext(optionsBuilder.Options);

        return instance;
    }
}

Now let’s create our first migration and push it to the database (find the docs here). In your terminal window, change to the folder of your dummy project. Once you’re in the correct folder, create a new migration with the add command:

dotnet ef migrations add {MigrationName}

To push the migration you just created to the database, use the update command with the connection parameter:

dotnet ef database update --connection 'Data Source=localhost;Initial Catalog=localDB;User ID=sa;Password=thisShouldB3Stronger!'

If all goes well, you should now be able to view your database with the seeded data:

database seeded

Conclusion

In this post, I showed you how to create the model for the database and their matching IEntityTypeConfiguration implementations. We learned how to create a IDesignTimeDbContextFactory and how to add migrations and push them to the database. The full code is on GitHub for your further exploration.

As always, I hope this post is helpful for some of you.

Until the next post, happy coding, everyone!

Posted by msicc in Azure, Database, Dev Stories, 3 comments
#CASBAN6: the data model explained

#CASBAN6: the data model explained

Preface

Initially, this post should have been about the direct implementation and design of the data model for my serverless blog engine. As the data model became a bit more complex, I decided to split the data model post into two posts. The first aims to explain the data model, while the second post is for the implementation with Entity Framework Core.

The data model

A picture is worth a thousand words, they say. So here is a complete picture of the data model:

CASBAN6 data model

I will go through the model table by table and tell you a sentence or two on each of it for the rest of this post.

The tables

__EFMigrationsHistory

This table just stores all the MigrationIds and is handled by the Entity Framework. I recommend you to not touch this table.

Blogs

In theory, the database could hold more than one blog. By adding a new row to this table, you are creating a new blog in the database. The BlogId is essential for a range of other tables.

Authors

To be able to issue blog posts, our blog needs at least one author. As you may have more than one person to fill your blog, a collection of authors can be saved within this table. One author can only be assigned to one blog (at least for now, this is intentional).

Posts

The content fuelling our blog is in the posts table. One post can only be on one blog. The published date will be set on insert, all subsequent changes will modify the LastModified column. Also, one post can only have one author. The author can be replaced on updating the row in the table.

The slug can be used to create a human-readable URL for the post (instead of the PostId, which is a GUID). The slug must be unique across all blogs.

Tags

In order to group and categorize posts, I decided to go with a tags-only approach (unlike other platforms, which allow both categories and tags). Tags are unique to a blog, but can be used in multiple posts.

Media

Of course, our blog should support also media content like images, videos and other types. I opted in for a URL-based approach, which is making it easier to add content from other platforms (like videos hosted on dedicated platforms). A medium can be used in several posts.

MediaTypes

To make it a bit easier to determine a medium’s type, I added the MediaTypes table. It holds information about the MIME-Type and possibly also the encoding of a file. The uniqueness is based off the MIME-Type.

Mapping tables

As we learned already above, both tags and media can be used in multiple posts. At the same time, posts can have multiple media and also multiple tags. To cover this many-to-many relationships, I use two mapping-tables with a composite primary key to ensure their uniqueness across the blog.

Conclusion

In this post, I outlined the data model of my serverless blog engine. In the next post, I will show you the implementation of this model with Entity Framework Core.

Until the next post, happy coding, everyone!

Posted by msicc in Azure, Database, Dev Stories, 2 comments
#CASBAN6: How to set up a local Microsoft SQL database on macOS

#CASBAN6: How to set up a local Microsoft SQL database on macOS

Microsoft’s SQL Server cannot be installed directly on macOS, like on Windows machines. Luckily, there is a not so complicated solution using a Docker container – provided by Microsoft themselves.

Install Docker

Obviously, the first step is to download Docker and install it on your Mac. Just head over to the Docker website and download the appropriate version. Install the app by opening the disk image and follow the instructions.

Install SQL Server

After installing the Docker desktop client, head over to the docker hub of Microsoft’s SQL Server. You can choose between SQL Server 2017, 2019 and 2022, with the latter one being preview (as of publishing time of this post). To download the image, we need to open a terminal and download it with the pull command:

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

I am selecting 2019 here as it is closest to what Azure SQL databases uses as of publishing time of this post.

Create a server instance

Microsoft makes it quite easy to create a server instance, we just need to copy the appropriate run command from the docker hub website. I am using SQL Server Express for my testing purposes:

 docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=thisShouldB3Stronger!' -e 'MSSQL_PID=Express' -p 1433:1433 --name mssql  -d mcr.microsoft.com/mssql/server:2019-latest

Once you run this command without any error, type in docker ps to verify the image is up and running. If all goes well, you should see something like this:

Create a database

Now that we have a running server instance, we can finally create a database for our purposes. We are using this terminal command to achieve our goal:

docker exec -i mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'thisShouldB3Stronger!' -Q 'CREATE DATABASE localDB'

We are logging into our server with this and send the command to create our database. Alternatively, we could already connect using DBeaver(link below) to create the Database. In both cases, we have our local database up and running by now.

Connect

There are several ways to connect to this database. The one we are going to use with Entity Framework Core is the good old connection string:

//template: Data Source=localhost;Initial Catalog=<database>;User ID=sa;Password=<password>

Data Source=localhost;Initial Catalog=localDB;User ID=sa;Password=thisShouldB3Stronger!

If you want to access your database with a GUI, I recommend using either Visual Studio Code with the Azure and SQL workload installed or the Community Edition of DBeaver.

DBeaver Community screenshot

Visual Studio allows connecting on a database level, while DBeaver can be used to connect at server level as well. Both of them also support access to Azure SQL databases, which will be helpful later on.

Conclusion

Microsoft’s SQL Server is not available for macOS. Nonetheless, we are able to quickly set up a Docker container that runs MS SQL and set up a local database for testing. I wrote this post for completeness of the series.

Useful links

Until the next post – happy coding, everyone!

Posted by msicc in Azure, Database, Dev Stories, 3 comments
#CASBAN6: Creating A Serverless Blog on Azure with .NET 6 (new series)

#CASBAN6: Creating A Serverless Blog on Azure with .NET 6 (new series)

Motivation

I was planning to run my blog without WordPress for quite some time. For one, because WordPress is really blown up as a platform. The second reason is more of a practical nature – this project gives me lots of stuff to improve my programming skills. I already started to move my developer website away from WordPress with ASP.NET CORE and Razor Pages. Eventually I arrived at the point where I needed to implement a blog engine for the news section. So, I have two websites (including this one here) that will take advantage of the outcome of this journey.

High Level Architecture

Now that the ‘why’ is clear, let’s have a look at the ‘how’:

There are several layers in my concept. The data layer consists of a serverless MS SQL instance on Azure, on which I will work with the help of Entity Framework Core and Azure Functions for all the CRUD operations of the blog. I will use the powers of Azure API Management, which will allow me to provide a secure layer for the clients – of course, an ASP.NET CORE Website with RazorPages, flanked by a .NET MAUI admin client (no web administration). Once the former two are done, I will also add a mobile client for this blog. It will be the next major update for my existing blog reader that is already in the app stores.

For comments, I will use Disqus. This way, I have a proven comment system where anyone can use his/her favorite account to participate in discussions. They also have an API, so there is a good chance that I will be able to implement Disqus in the Desktop and Mobile clients.

Last but not least, there are (for now) two open points – performance measuring/logging and notifications. I haven’t decided yet how to implement these – but I guess there will be an Azure based implementation as well (until there are good reasons to use another service).

Open Source

Most of the software I will write and blog about in this series will be available publicly on GitHub. You can find the repository already there, including stuff for the next two upcoming blog posts already in there.

Index

I will update this blog post regularly with a link new entries of the series.

Additional note

Please note that I am working on this in my spare time. This may result in delays between the blog posts and the updates committed into the repository on GitHub.

Until the next post – happy coding, everyone!


Title Image by Roman from Pixabay

Posted by msicc in Android, Azure, Dev Stories, iOS, MAUI, Web, 2 comments
Book review (and recommendation): Learn T-SQL Querying

Book review (and recommendation): Learn T-SQL Querying

The Fundamentals

In the first section of the book, the authors give us an overview about the anatomy of a query (going very deep) and how SQL Server (also on Azure) processes queries. They also explain how SQL Server optimizes queries and how different versions of SQL Server are processing them differently, which can result in different performances for complex queries.

Dos and Don’ts

The second section of the book is guiding us through how Query Execution plans work and how they can help to write more efficient SQL queries. The section has a bunch of tips that developers can use in their everyday life with databases. The perhaps most important part in this section are the two chapters about T-SQL antipatterns. This is the section where I learned the most throughout the book.

Troubleshooting and tools

The last section of the book shows a lot of troubleshooting techniques and how to use them properly. Microsoft’s SQL Server Management Tools itself comes with a bunch of such tools, and the book helps not only to find them, but also to use them correctly. On top, there are also references to some Open Source tools that can be helpful at times. The book closes with the Query Tuning Assistant, which is the recommended tool to perform SQL server updates.

Conclusion

I got the book because my current job requires me to write efficient T-SQL code for the interfaces I am developing. The book already helped me during the reading time to better understand what I am doing and how I can optimize my own queries. It will be one of my reference books in future when it comes to troubleshooting and query performance with SQL. Long story short, if you are working regularly with Microsoft SQL Server (also on Azure), you should have this book in your (digital) bookshelf.

Book metadata

Posted by msicc in Book Review, Dev Stories, Editorials, 0 comments

Goodbye Telefónica (Germany), Hello ShareCommService (Switzerland)

Yes, you read that right. Today was my last work day at Telefónica, where I worked since June 2007. During that time, I learned a lot about how good customer service should be, and even more about mobile networks and mobile devices (phones, tablets and more). During that time, I also started my dev story in my spare time – all because there was not a single fishing knots application available for Windows Phone (read more here).

This lead to get recognized also within Telefónica as a developer, and so I was asked to concept and develop the application Friends & You (nope, you can’t download it as it is an internal app).  I learned a lot about how corporate apps aren’t that easy to concept and build during that time, as well as as how restrictive corporate rules can be. After all, I had a few challenges to complete. Thanks to Friends & You, I was also able to dive into the world of Xamarin, where I learned a lot about the application structures of Android and iOS (which will be helpful for eventually porting some of my Windows Phone apps to those two).

I want to say thanks to my colleagues, you´re awesome! Keep up the great work you´re doing in Customer Service.

Back in September then, I opened up my Twitter app and the first tweet I read was this one:

image

I opened the attached document and read the job description. As an self-thought developer, I first was about to close it and put it aside as “read and done”.

Due to the head count freeze at Telefónica after acquiring e-Plus from KPN, there are no chances to become a real full time developer within Telefónica for the next time. But that is what I want to do. After reopening the document and reading it again, I decided to get in contact with Roman Mueller (who I knew from Twitter already).

We talked about the job itself and the circumstances behind the announcement. After a few talks and a visit in Switzerland, the decision was made. I am starting the next chapter of my developer story in January, moving to Switzerland, where I will add deeper SQL knowledge, WPF and a lot more to my development knowledge.

At this point, I want to thank ShareCommService and especially Roman for all the help I received already to make this all happen and get started.

It is going to be an exciting journey for me (and my family), and we are ready to go.

Happy coding everyone!

Posted by msicc in Dev Stories, Editorials, 1 comment

Getting productive with WAMS: how to update data for a specific row in a table

WAMS.png

Like I promised, I will share some of the Azure goodness I learned during creating my last app.

This post is all about how to update a specific table entry (like a user’s data) in a Azure SQL table from an Windows Phone app.

First, we need to make sure that there is some data from the user we want to update. I used the LookupAsync () method to achieve that.

IMobileServiceTable<userItems> TableToUpdate = App.MobileService.GetTable<userItems>();
IMobileServiceTableQuery<userItems> query = TableToUpdate.Where(useritem => useritem.TwitterId == App.TwitterId);

var useritemFromAzure = await query.ToListAsync();
var useritemLookUp = await TableToUpdate.LookupAsync(useritemFromAzure.FirstOrDefault<userItems>().Id);

If we want a specific entry, we need a search criteria to find our user and fetch the id of the user’s table entry. In my case, I used the Twitter Id for the query as every user has this on my project.

Now that we have the table row id, we can easily update the data of this specific row with the UpdateAsync() method.

We need to declare which columns should be updated and asign the values to it first. After that, we simply call the UpdateAsync() method.

useritemLookUp.TwitterId = App.TwitterId;
useritemLookUp.LastCheckedAt = DateTime.Now;
useritemLookUp.OSVersion = "WP8";
useritemLookUp.AppVersion = App.VersionNumber;

await TableToUpdate.UpdateAsync(useritemLookUp);

Please note that you need a items class/model to create the update data (which you should have already before thinking about updating the data).

You should wrap this code in a try{}/catch{} block to be able to react to the Exceptions that possibly can be thrown and display a matching message to the user.

That’s already all about updating a specific row in a WAMS SQL table.

As always I hope this post is helpful for some of you.

Happy coding!

Posted by msicc in Azure, Dev Stories, 1 comment