Building an Online Store with Platform UI and Hugo
With the release of our CSS Framework, Platform UI, it might be fun to actually build something with it! At RitterIM, we have an interna...
CSS Frameworks Static SitesRegardless of your opinion on Object Relational Mappers (ORM), I think they are a great tool for prototyping ideas. Over time, they can lead to lackluster performance as your application’s ideas begin to cement. Replacing an ORM with a Micro-ORM is a great way to regain some performance. In this post, I’ll show you how to fetch multiple SQL results using NPoco while keeping your code concise.
This example is Using PostgreSQL, .NET Core, and NPoco. You are welcome to use any database that NPoco supports, including SQL Server.
Entity Framework is the ORM we use, and to get multiple collections we have several options:
These options work, but sometimes you just want to run the query you want.
Below, we are using the FetchMultiple
method in NPoco to execute multiple SQL queries over a single connection. The method returns a Tuple<T1,T2>
. We can use Tuple deconstruction to map those result sets into variables.
using System;
using NPoco;
using ConsoleTables;
namespace NpocoExample
{
class Program
{
static void Main(string[] args)
{
var factory = Npgsql.NpgsqlFactory.Instance;
var database =
new Database(
"host=localhost;user id=admin;password=admin;database=postgres",
DatabaseType.PostgreSQL,
factory
);
using (var connection = database.OpenSharedConnection())
{
var (friends, family) =
connection.FetchMultiple<Friend,Family>(
@"select * from public.Friends fetch first 10 rows only;
select * from public.Family fetch first 10 rows only;"
);
ConsoleTable
.From<Friend>(friends)
.Write(Format.Alternative);
ConsoleTable
.From<Family>(family)
.Write(Format.Alternative);
}
}
}
public class Friend {
public int Id {get;set;}
public string Name {get;set;}
}
public class Family {
public int Id {get;set;}
public string Name {get;set;}
}
}
After executing the code above, we see our result sets in the console output. Both queries were efficiently executed without the need to do expensive expression compilation, object tracking, or database joins. I’d call that a win!
+----+---------+
| Id | Name |
+----+---------+
| 1 | Lavinia |
+----+---------+
| 2 | Lavinia |
+----+---------+
| 3 | Rhea |
+----+---------+
| 4 | Alfreda |
+----+---------+
| 5 | Justina |
+----+---------+
| 6 | Imani |
+----+---------+
| 7 | Yvette |
+----+---------+
| 8 | Mira |
+----+---------+
| 9 | Bevis |
+----+---------+
| 10 | Doris |
+----+---------+
+----+---------+
| Id | Name |
+----+---------+
| 1 | Rhona |
+----+---------+
| 2 | Phoebe |
+----+---------+
| 3 | Colton |
+----+---------+
| 4 | Malachi |
+----+---------+
| 5 | Olympia |
+----+---------+
| 6 | Noble |
+----+---------+
| 7 | Rebecca |
+----+---------+
| 8 | Amal |
+----+---------+
| 9 | Ulric |
+----+---------+
| 10 | Noble |
+----+---------+
NPoco is one of the best Micro-ORMs and with the addition of Tuple deconstruction we get a much nicer development experience.