Kimserey's blog

Entity Framework Core Performance Optimization Last year I talked about Entity Framework Core . It is a easy and feature rich ORM which makes working with database in a .NET environment typesafe. But even though it makes things easy, there are ambiguous cases which can take us off guard. Today we will see four of this cases and how to deal with them. Client evaluation Iteration Include and ThenInclude NoTracking For the following examples, I will be using SQLite with Entity Framework Core. tl;dr Make sure that the query constructed in c# uses function that can be translated to SQL, Make sure that there isn’t an abnormal amount of queries created and that it does not iter item per item, Make sure to use Include and ThenInclude for object relation to include them after query execution, before query execution it is not needed, Use NoTracking for readonly queries to disable tracking on entity to yield better performance. 1. Client evaluation The following example illust...

Saving data with Entity Framework Core with SQLite Entity Framework is a framework abstracting away all the complexity of dealing with storage. This abstraction is also known as ORM ~ object-relational mapping. There is a number of provider which are implementation of the storage like SQL server or MySql or also SQLite, the one we will be seeing in this post. SQLite is a embedded database. The whole database is contained within a single .db file which makes it highly portable, so portable that it is the default database installed in mobile OS like iOS and Android . It is extremely easy to use and to maintain. It also offer a powerful implementation of SQL . Today we will see how we can make use of Entity Framework with SQLite provider in a ASP.NET Core application. 1. Install EF and create a new DbContext 2. Create migrations 3. Use in ASP NET Core 1. Install EF and create a new DbContext Start by installing the packages: Install-Package Microsoft.EntityFrameworkCore.SQL...

Nice features and tricks with SQLite For the past few days, I have been writing a lot of SQL queries to query SQLite databases. I had to extract data for reporting purposes from SQLite databases where simple SELECT-FROM-WHERE queries weren’t enough. From this experience, I learnt few tricks that I am sure some of you will be interested in. So today, I will list it all in this blog post. This post is composed by six parts: Use the built in date functions Cast your string to integer with CAST Transpose a table using GROUP BY , CASE and Aggregate functions Concatenate value with || Attach databases to JOIN on tables from different databases Improve the performance of your query with EXPLAIN QUERY PLAN The parts aren’t related with one another. 1. Use the built in date functions strftime is the main function for datetime manipulation. It takes a format , a string date and some modifiers . Here are the format extracted from https://www.sqlite.org/lang_datefunc.html . %...