Norm.NET Micro ORM
Norm.NET Micro ORM is a one-way lightweight object-relational mapping library.
Micro ORM for .NET/C#.
dotnet add package Norm.net --version 5.4.0dotnet add package Norm.net --version 5.4.0NuGet\Install-Package Norm.net -Version 5.4.0NuGet\Install-Package Norm.net -Version 5.4.0<PackageReference Include="Norm.net" Version="5.4.0" /><PackageReference Include="Norm.net" Version="5.4.0" />#r "nuget: Norm.net, 5.4.0"#r "nuget: Norm.net, 5.4.0"netstandard2.1
net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows net8.0 net8.0-android net8.0-ios net8.0-maccatalyst net8.0-macos net8.0-tvos net8.0-windows
netcoreapp3.0 netcoreapp3.1
monoandroid monomac monotouch
tizen60
xamarinios xamarinmac xamarintvos xamarinwatchos
You can read an array of name and value tuples
Use LINQ expressions to build appropriate data structures such as dictionaries directly from your database and with minimum iterations.
(string name, value object) - for each record, where the name is the field name, and the value is, well, the value of that record.Use LINQ expressions to build appropriate data structures such as dictionaries directly from your database and with minimum iterations.
using System.Linq;
using Norm;
//
// Creates enumerable of name and value tuples array.
//
// Database is NOT read until resulting enumeration is iterated.
// name is the column name, and value is the column value.
//
IEnumerable<(string name, value object)[]> enumeration = connection
.Read("select id, name from table");
//
// Creates list of name and value tuples array.
//
// Database is read on `ToList` iteration.
// name is the column name, and value is the column value.
//
List<(string name, object value)[]> tuples = connection
.Read("select id, name from table")
.ToList();
//
// Creates list of name and value dictionaries.
//
// Database is read on `ToList` iteration.
// Dictionary keys are column names, values are column values.
//
List<Dictionary<string, object>> list1 = connection
.Read("select id, name from table")
.Select(tuples =>
tuples.ToDictionary(
tuple => tuple.name,
tuple => tuple.value))
.ToList();
//
// Creates dictionary where:
// - The key is the first column value.
// - The value is the last column value.
//
// Database is read on `ToDictionary` iteration.
//
Dictionary<int, string> dict1 = connection
.Read("select id, name from table")
.ToDictionary(
tuples => (int)tuples.First().value,
tuples => tuples.Last().value?.ToString());using System.Linq;
using Norm;
//
// Creates enumerable of name and value tuples array.
//
// Database is NOT read until resulting enumeration is iterated.
// name is the column name, and value is the column value.
//
IEnumerable<(string name, value object)[]> enumeration = connection
.Read("select id, name from table");
//
// Creates list of name and value tuples array.
//
// Database is read on `ToList` iteration.
// name is the column name, and value is the column value.
//
List<(string name, object value)[]> tuples = connection
.Read("select id, name from table")
.ToList();
//
// Creates list of name and value dictionaries.
//
// Database is read on `ToList` iteration.
// Dictionary keys are column names, values are column values.
//
List<Dictionary<string, object>> list1 = connection
.Read("select id, name from table")
.Select(tuples =>
tuples.ToDictionary(
tuple => tuple.name,
tuple => tuple.value))
.ToList();
//
// Creates dictionary where:
// - The key is the first column value.
// - The value is the last column value.
//
// Database is read on `ToDictionary` iteration.
//
Dictionary<int, string> dict1 = connection
.Read("select id, name from table")
.ToDictionary(
tuples => (int)tuples.First().value,
tuples => tuples.Last().value?.ToString());You can map a maximum of 12 classes, records, or dynamic instances from the same query result.
Mapping is done by the field name (snake case is the default name mapping method) - and from left to right for multiple instance mappings.
Mapping is done by the field name (snake case is the default name mapping method) - and from left to right for multiple instance mappings.
using System.Linq;
using Norm;
//
// Single instance mapping by name.
//
var result1 = connection
.Read<Class1>(query)
.Single();
//
// Two instances mapping by name.
// Read method yields a tuple that can be deconstructed.
//
var (result1, result2) = connection
.Read<Class1, Class2>(query)
.Single();
//
// Two class instances and one dynamic instance mapping by name.
// Read method yields a tuple that can be deconstructed.
//
var (result1, result2, result3) = connection
.Read<Class1, Class2, dynamic>(query)
.Single();
//
// Six instances mapping by name.
//
var (result1, result2, result3, result4, result5, result6) = connection
.Read<Class1, Class2, Class3, Class4, Class5, Class6>(query)
.Single();
//
// ... up to twelve (12) instances max.
//using System.Linq;
using Norm;
//
// Single instance mapping by name.
//
var result1 = connection
.Read<Class1>(query)
.Single();
//
// Two instances mapping by name.
// Read method yields a tuple that can be deconstructed.
//
var (result1, result2) = connection
.Read<Class1, Class2>(query)
.Single();
//
// Two class instances and one dynamic instance mapping by name.
// Read method yields a tuple that can be deconstructed.
//
var (result1, result2, result3) = connection
.Read<Class1, Class2, dynamic>(query)
.Single();
//
// Six instances mapping by name.
//
var (result1, result2, result3, result4, result5, result6) = connection
.Read<Class1, Class2, Class3, Class4, Class5, Class6>(query)
.Single();
//
// ... up to twelve (12) instances max.
//Instead of classes or records, you can map to multiple, simple single-value value types (such as integers, strings, dates, GUIDs, booleans, etc). In this case, mapping is done by position.
The resulting structure will be an unnamed tuple that can be deconstructed to the same simple value variable values. See examples bellow:
The resulting structure will be an unnamed tuple that can be deconstructed to the same simple value variable values. See examples bellow:
using System.Linq;
using Norm;
//
// Single value.
//
var id = connection
.Read<int>("select id from table")
.Single();
//
// Two values mapping by position.
//
var (id, value) = connection
.Read<int, string>("select id, value from table")
.Single();
//
// Iterating over three values mapped by position.
//
foreach(var (id, foo, bar) in connection.Read<int, string, string>(
"select id, foo, bar from table"))
{
//...
}
//
// Six value mapping by position.
//
var (value1, value2, value3, value4, value5, value6) = connection
.Read<string, string, string, string, string, string>(@"
select value1, value2, value3, value4, value5, value6
from table
")
.Single();
//
// ... up to twelve (12) values max.
//using System.Linq;
using Norm;
//
// Single value.
//
var id = connection
.Read<int>("select id from table")
.Single();
//
// Two values mapping by position.
//
var (id, value) = connection
.Read<int, string>("select id, value from table")
.Single();
//
// Iterating over three values mapped by position.
//
foreach(var (id, foo, bar) in connection.Read<int, string, string>(
"select id, foo, bar from table"))
{
//...
}
//
// Six value mapping by position.
//
var (value1, value2, value3, value4, value5, value6) = connection
.Read<string, string, string, string, string, string>(@"
select value1, value2, value3, value4, value5, value6
from table
")
.Single();
//
// ... up to twelve (12) values max.
//You can also give meaningful names to your unnamed tuples by using named tuples. Just provide a named tuple type as a generic parameter and map your database values by position.
You can also map to multiple named tuple types in a single command, up to 12 named tuples maximum.
You can also map to multiple named tuple types in a single command, up to 12 named tuples maximum.
using System.Linq;
using Norm;
//
// You can also map to named tuples and give names to the columns.
// Two value mapping by position.
//
var result = connection
.Read<(int id, string name)>("select id, value from table")
.Single();
var id = result.id;
var name = result.name;
//
// Iterating over named tuples mapped by position.
//
var query = "select id, foo, bar from table";
foreach(var item in connection
.Read<(int id, string foo, string bar)>(query))
{
WriteLine($"id: {item.id}, foo: {item.foo}, bar: {item.bar}");
}
//
// Mapping two named tuples by position.
//
var query = "select t1.*, t2.* from t1 join t2 using (id)";
foreach (var (t1, t2) in connection.Read<
(int id, string name),
(int id, string name)>(query))
{
WriteLine($"t1 - id: {t1.id}, name: {t1.name});
WriteLine($"t2 - id: {t1.id}, name: {t1.name});
}
//
// ... up to twelve (12) named tuples max.
//using System.Linq;
using Norm;
//
// You can also map to named tuples and give names to the columns.
// Two value mapping by position.
//
var result = connection
.Read<(int id, string name)>("select id, value from table")
.Single();
var id = result.id;
var name = result.name;
//
// Iterating over named tuples mapped by position.
//
var query = "select id, foo, bar from table";
foreach(var item in connection
.Read<(int id, string foo, string bar)>(query))
{
WriteLine($"id: {item.id}, foo: {item.foo}, bar: {item.bar}");
}
//
// Mapping two named tuples by position.
//
var query = "select t1.*, t2.* from t1 join t2 using (id)";
foreach (var (t1, t2) in connection.Read<
(int id, string name),
(int id, string name)>(query))
{
WriteLine($"t1 - id: {t1.id}, name: {t1.name});
WriteLine($"t2 - id: {t1.id}, name: {t1.name});
}
//
// ... up to twelve (12) named tuples max.
//Declare type aliases for your named tuples and us them in your entire project..
//
// C#12
// Global usings for entire project
//
global using TitleDescriptionYear = (string title, string description, int year);
global using IdName = (int id, string name);
//
// Later in your code, you can use the types, intelisense will work
//
using System.Linq;
using Norm;
//
// Iterate over tuples, intelisense enabled
//
foreach (var tuple in connection.Read<TitleDescriptionYear>(
"select title, description, release_year from film limit 3"))
{
WriteLine("Title: {0}, Description: {1}, Year: {2}",
tuple.title, tuple.description, tuple.year);
}
foreach (var tuple in connection.Read<IdName>(
"select film_id, title from film limit 3"))
{
WriteLine("Film Id: {0}, Name: {1}",
tuple.id, tuple.name);
}
//
// Build a dictionary from tuples, intelisense enabled
//
var dict = connection
.Read<IdName>("select film_id, title from film limit 3")
.ToDictionary(
tuple => tuple.id,
tuple => tuple.name);//
// C#12
// Global usings for entire project
//
global using TitleDescriptionYear = (string title, string description, int year);
global using IdName = (int id, string name);
//
// Later in your code, you can use the types, intelisense will work
//
using System.Linq;
using Norm;
//
// Iterate over tuples, intelisense enabled
//
foreach (var tuple in connection.Read<TitleDescriptionYear>(
"select title, description, release_year from film limit 3"))
{
WriteLine("Title: {0}, Description: {1}, Year: {2}",
tuple.title, tuple.description, tuple.year);
}
foreach (var tuple in connection.Read<IdName>(
"select film_id, title from film limit 3"))
{
WriteLine("Film Id: {0}, Name: {1}",
tuple.id, tuple.name);
}
//
// Build a dictionary from tuples, intelisense enabled
//
var dict = connection
.Read<IdName>("select film_id, title from film limit 3")
.ToDictionary(
tuple => tuple.id,
tuple => tuple.name);If you use a non-generic version of the
In that case, Norm will use that prototype to construct resulting instances.
That prototype could also be an anonymous type - initilaized with default values as in examples bellow:
Read method, you can provide a prototype instance as a first parameter.In that case, Norm will use that prototype to construct resulting instances.
That prototype could also be an anonymous type - initilaized with default values as in examples bellow:
using System.Linq;
using Norm;
//
// You can use existing instances as a bluprint for mapping.
//
class Class1
{
public int Id { get; set; }
public string Name { get; set; }
}
//
// Maps existing instances of Class1 to
// new instances of the same type by name.
//
var instance1 = new Class1();
var result1 = connection
.Read(instance1, "select 1 as id, 'foo' as name")
.Single();
Assert.Equal(1, result1.Id);
Assert.Equal("foo", result1.Name);
//
// You can use anonymous instances prototype
// to map to anonymous instances.
// Also by name.
//
var result1 = connection
.Read(
new {id = default(int), name = default(string)},
"select 1 as id, 'foo' as name")
.Single();
Assert.Equal(1, result1.Id);
Assert.Equal("foo", result1.Name);
//
// Iterate anonymous instances mapped by name.
//
foreach(var item in connection.Read(new
{
first = default(int),
bar = default(string),
day = default(DateTime?),
@bool = default(bool?),
myString = default(string),
}, @"
select * from (
values
(1, 'foo1', '1977-05-19'::date, true, null),
(2, 'foo2', '1978-05-19'::date, false, 'bar2'),
(3, 'foo3', null::date, null, 'bar3')
) t(first, bar, day, bool, my_string)
"))
{
WriteLine("{0}, {1}, {2}, {3}, {4}",
item.first,
item.bar,
item.day,
item.@bool,
item.myString);
}using System.Linq;
using Norm;
//
// You can use existing instances as a bluprint for mapping.
//
class Class1
{
public int Id { get; set; }
public string Name { get; set; }
}
//
// Maps existing instances of Class1 to
// new instances of the same type by name.
//
var instance1 = new Class1();
var result1 = connection
.Read(instance1, "select 1 as id, 'foo' as name")
.Single();
Assert.Equal(1, result1.Id);
Assert.Equal("foo", result1.Name);
//
// You can use anonymous instances prototype
// to map to anonymous instances.
// Also by name.
//
var result1 = connection
.Read(
new {id = default(int), name = default(string)},
"select 1 as id, 'foo' as name")
.Single();
Assert.Equal(1, result1.Id);
Assert.Equal("foo", result1.Name);
//
// Iterate anonymous instances mapped by name.
//
foreach(var item in connection.Read(new
{
first = default(int),
bar = default(string),
day = default(DateTime?),
@bool = default(bool?),
myString = default(string),
}, @"
select * from (
values
(1, 'foo1', '1977-05-19'::date, true, null),
(2, 'foo2', '1978-05-19'::date, false, 'bar2'),
(3, 'foo3', null::date, null, 'bar3')
) t(first, bar, day, bool, my_string)
"))
{
WriteLine("{0}, {1}, {2}, {3}, {4}",
item.first,
item.bar,
item.day,
item.@bool,
item.myString);
}Take advantage of the new
All asynchronous methods in Norm will return the
IAsyncEnumerable type and asynchronous streaming directly from your database.All asynchronous methods in Norm will return the
IAsyncEnumerable type, so you can take async code to the next level by leveraging System.Linq.Async.using Norm;
const string query =
"select id, name, foo, bar from my_streaming_table";
//
// Asynchronously stream values directly from database
//
await foreach(var (id, name, foo, bar) in
connection.ReadAsync<int, string, string, string>(query))
{
//...
}
//
// Asynchronously stream named tuples directly from database
//
await foreach(var item in
connection.ReadAsync<(
int id,
string name,
string foo,
string bar)>(query))
{
//...
}
//
// Asynchronously stream to one or more
// class instances directly from database
//
class Class1
{
public int Id { get; set; }
public string Name { get; set; }
}
class Class2
{
public int Foo { get; set; }
public string Bar { get; set; }
}
// Single class (id and name)
await foreach(var item in
connection.ReadAsync<Class1>(query))
{
//...
}
// Two classes
// class1: id and name
// class2: foo and bar (up to 12 max)
await foreach(var (r1, r2) in
connection.ReadAsync<Class1, Class2>(query))
{
//...
}
// One normal class (id and name) and one dynamic (up to 12 max)
await foreach(var (x, y) in
connection.ReadAsync<Class1, dynamic>(query))
{
//...
}using Norm;
const string query =
"select id, name, foo, bar from my_streaming_table";
//
// Asynchronously stream values directly from database
//
await foreach(var (id, name, foo, bar) in
connection.ReadAsync<int, string, string, string>(query))
{
//...
}
//
// Asynchronously stream named tuples directly from database
//
await foreach(var item in
connection.ReadAsync<(
int id,
string name,
string foo,
string bar)>(query))
{
//...
}
//
// Asynchronously stream to one or more
// class instances directly from database
//
class Class1
{
public int Id { get; set; }
public string Name { get; set; }
}
class Class2
{
public int Foo { get; set; }
public string Bar { get; set; }
}
// Single class (id and name)
await foreach(var item in
connection.ReadAsync<Class1>(query))
{
//...
}
// Two classes
// class1: id and name
// class2: foo and bar (up to 12 max)
await foreach(var (r1, r2) in
connection.ReadAsync<Class1, Class2>(query))
{
//...
}
// One normal class (id and name) and one dynamic (up to 12 max)
await foreach(var (x, y) in
connection.ReadAsync<Class1, dynamic>(query))
{
//...
}You can map from multiple queries by using the
When mapping from multiple queries, all the same rules apply as when mapping from a single query.
Multiple method.When mapping from multiple queries, all the same rules apply as when mapping from a single query.
using System.Linq;
using Norm;
const string Queires = @"
select 1 as id1, 'foo1' as foo1, 'bar1' as bar1; // query 1
select 2 as id2, 'foo2' as foo2, 'bar2' as bar2; // query 2";
//
// declare two record types - Record1 and Record2
//
public record Record1(int Id1, string Foo1, string Bar1);
public record Record2(int Id2, string Foo2, string Bar2);
//
// Use Multiple method to create a disposable mutiple read object.
// Move to next result set with Next method.
//
using var multiple = connection.Multiple(Queires);
var result1 = multiple
.Read<Record1>()
.Single();
multiple.Next();
var result2 = multiple
.Read<Record2>()
.Single();
//
// Same mapping rules apply as in regular Read methods.
//
using var multiple = connection.Multiple(Queires);
var result1 = multiple
.Read<(int Id1, string Foo1, string Bar1)>()
.Single();
multiple.Next();
var result2 = multiple
.Read<(int Id2, string Foo2, string Bar2)>()
.Single();using System.Linq;
using Norm;
const string Queires = @"
select 1 as id1, 'foo1' as foo1, 'bar1' as bar1; // query 1
select 2 as id2, 'foo2' as foo2, 'bar2' as bar2; // query 2";
//
// declare two record types - Record1 and Record2
//
public record Record1(int Id1, string Foo1, string Bar1);
public record Record2(int Id2, string Foo2, string Bar2);
//
// Use Multiple method to create a disposable mutiple read object.
// Move to next result set with Next method.
//
using var multiple = connection.Multiple(Queires);
var result1 = multiple
.Read<Record1>()
.Single();
multiple.Next();
var result2 = multiple
.Read<Record2>()
.Single();
//
// Same mapping rules apply as in regular Read methods.
//
using var multiple = connection.Multiple(Queires);
var result1 = multiple
.Read<(int Id1, string Foo1, string Bar1)>()
.Single();
multiple.Next();
var result2 = multiple
.Read<(int Id2, string Foo2, string Bar2)>()
.Single();With Norm, building nested object maps is easy.
You can use the combination of tuple destructions and LINQ expressions to build complex object maps with nested objects from your database. See example bellow:
You can use the combination of tuple destructions and LINQ expressions to build complex object maps with nested objects from your database. See example bellow:
using System.Linq;
using Norm;
const string query = @"
select shop.*, account.*
from shop
join account on shop.id = account.shop_id";
/*
"id" "name" "id" "name" "address" "shop_id"
---------------------------------------------------------
1 "shop1" 3 "account3" "addr3" 1
1 "shop1" 2 "account2" "addr2" 1
1 "shop1" 1 "account1" "addr1" 1
2 "shop2" 5 "account5" "addr5" 2
2 "shop2" 4 "account4" "addr4" 2
*/
//
// Each Shop instance will have a list of related Accounts.
//
public class Shop
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Account> Accounts { get; set; }
}
//
// Each Account instance will have a reference to its parent Shop.
//
public class Account
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public int ShopId { get; set; }
public Shop Shop { get; set; }
}
//
// Build a list of Shops with list of related Accounts where
// each Account has a reference to its parent Shop
// using GroupBy and Select LINQ methods for nested mappings.
//
var shops = connection.Read<Shop, Account>(query)
.GroupBy(item => item.Item1.Id)
.Select(group =>
{
var shop = group.First().Item1;
shop.Accounts = group.Select(item =>
{
var account = item.Item2;
account.Shop = shop;
return account;
}).ToList();
return shop;
})
.ToList();using System.Linq;
using Norm;
const string query = @"
select shop.*, account.*
from shop
join account on shop.id = account.shop_id";
/*
"id" "name" "id" "name" "address" "shop_id"
---------------------------------------------------------
1 "shop1" 3 "account3" "addr3" 1
1 "shop1" 2 "account2" "addr2" 1
1 "shop1" 1 "account1" "addr1" 1
2 "shop2" 5 "account5" "addr5" 2
2 "shop2" 4 "account4" "addr4" 2
*/
//
// Each Shop instance will have a list of related Accounts.
//
public class Shop
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Account> Accounts { get; set; }
}
//
// Each Account instance will have a reference to its parent Shop.
//
public class Account
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public int ShopId { get; set; }
public Shop Shop { get; set; }
}
//
// Build a list of Shops with list of related Accounts where
// each Account has a reference to its parent Shop
// using GroupBy and Select LINQ methods for nested mappings.
//
var shops = connection.Read<Shop, Account>(query)
.GroupBy(item => item.Item1.Id)
.Select(group =>
{
var shop = group.First().Item1;
shop.Accounts = group.Select(item =>
{
var account = item.Item2;
account.Shop = shop;
return account;
}).ToList();
return shop;
})
.ToList();You can configure Norm to return
This is useful when mapping the result of left outer joins, where you want to get
NULL values for instances that have all mapped properties NULL.This is useful when mapping the result of left outer joins, where you want to get
NULL values when joins doesn't match.using System.Linq;
using Norm;
//
// Set mapping behavior to nullable instances in your startup.
//
// If instance has all properties or fields null,
// that instance will be null by default.
//
NormOptions.Configure(options =>
{
options.NullableInstances = true;
});
//
// Get the shop and account instance by shop id = 1
//
var (shop, account) = connection
.Read<Shop, Account>(@"
select
shop.*, account.*
from shop
left outer join account
on shop.id = account.shop_id
where
shop.id = @id", 1) // id = 1
.Single();
// For @id = 1, this is the result:
/*
"id" "name" "id" "name" "address" "shop_id"
-----------------------------------------------------
1 "shop1" NULL NULL NULL NULL
*/
Assert.NotNull(shop); // shop is not null
Assert.Null(account); // account is nullusing System.Linq;
using Norm;
//
// Set mapping behavior to nullable instances in your startup.
//
// If instance has all properties or fields null,
// that instance will be null by default.
//
NormOptions.Configure(options =>
{
options.NullableInstances = true;
});
//
// Get the shop and account instance by shop id = 1
//
var (shop, account) = connection
.Read<Shop, Account>(@"
select
shop.*, account.*
from shop
left outer join account
on shop.id = account.shop_id
where
shop.id = @id", 1) // id = 1
.Single();
// For @id = 1, this is the result:
/*
"id" "name" "id" "name" "address" "shop_id"
-----------------------------------------------------
1 "shop1" NULL NULL NULL NULL
*/
Assert.NotNull(shop); // shop is not null
Assert.Null(account); // account is nullYou can map to complicated types like enums or arrays. Nullable or non-nullable enums are supported.
Enums can be mapped from either string or integer values.
Enums can be mapped from either string or integer values.
using System.Linq;
using Norm;
public enum TestEnum { Value1, Value2 }
public class TestEnumClass
{
public TestEnum FromText { get; set; }
public TestEnum FromInt{ get; set; }
// PostgerSQL only
public TestEnum[] ArrayFromText { get; set; }
// PostgerSQL only
public TestEnum[] ArrayFromInt { get; set; }
}
//
// Map Enum types from text or from integer columns in the database.
// Null values are also supported.
// You can also map arrays of enums (PostgreSQL only).
//
var instances = connection.Read<TestEnumClass>(@"
select
from_text,
from_int,
ARRAY['Value1', 'Value2'] as array_from_text,
ARRAY[0, 1] as array_from_int
from (
values
('Value1', 0),
('Value2', 1)
) t(from_text, from_int,)")
.ToArray();
// first row
Assert.Equal(TestEnum.Value1, instances[0].FromText);
Assert.Equal(TestEnum.Value1, instances[0].FromInt);
// second row
Assert.Equal(TestEnum.Value2, instances[1].FromText);
Assert.Equal(TestEnum.Value2, instances[1].FromInt);
// arrays (PostgreSQL only)
Assert.Equal(
new[] { TestEnum.Value1, TestEnum.Value2 },
instances[0].ArrayFromText);
Assert.Equal(
new[] { TestEnum.Value1, TestEnum.Value2 },
instances[0].ArrayFromInt);using System.Linq;
using Norm;
public enum TestEnum { Value1, Value2 }
public class TestEnumClass
{
public TestEnum FromText { get; set; }
public TestEnum FromInt{ get; set; }
// PostgerSQL only
public TestEnum[] ArrayFromText { get; set; }
// PostgerSQL only
public TestEnum[] ArrayFromInt { get; set; }
}
//
// Map Enum types from text or from integer columns in the database.
// Null values are also supported.
// You can also map arrays of enums (PostgreSQL only).
//
var instances = connection.Read<TestEnumClass>(@"
select
from_text,
from_int,
ARRAY['Value1', 'Value2'] as array_from_text,
ARRAY[0, 1] as array_from_int
from (
values
('Value1', 0),
('Value2', 1)
) t(from_text, from_int,)")
.ToArray();
// first row
Assert.Equal(TestEnum.Value1, instances[0].FromText);
Assert.Equal(TestEnum.Value1, instances[0].FromInt);
// second row
Assert.Equal(TestEnum.Value2, instances[1].FromText);
Assert.Equal(TestEnum.Value2, instances[1].FromInt);
// arrays (PostgreSQL only)
Assert.Equal(
new[] { TestEnum.Value1, TestEnum.Value2 },
instances[0].ArrayFromText);
Assert.Equal(
new[] { TestEnum.Value1, TestEnum.Value2 },
instances[0].ArrayFromInt);If you need to run some or all commands in a prepared mode, you can do that by using the
Prepared mode is useful for PostgreSQL databases because explicit command preparation is recommended for better performance.
Prepared method.Prepared mode is useful for PostgreSQL databases because explicit command preparation is recommended for better performance.
using System.Linq;
using Norm;
//
// Following query will be executed in prepared mode.
// Any Read, ReadAsync, Execute or ExecuteAsync method
// after Prepared() will be executed in prepared mode.
//
var (p1, p2, p3) = connection
.Prepared()
.WithParameters(1, 2, 3) // parameters are positional
.Read<int, int, int>("select @p1, @p2, @p3")
.Single();
//
// Set global options in your startup
// to always execute in prepared mode (default is false).
//
NormOptions.Configure(options =>
{
options.Prepared = true;
});
// No need to call Prepared() anymore, every call is prepared.
var (p1, p2, p3) = connection
.WithParameters(1, 2, 3) // parameters are positional
.Read<int, int, int>("select @p1, @p2, @p3")
.Single();using System.Linq;
using Norm;
//
// Following query will be executed in prepared mode.
// Any Read, ReadAsync, Execute or ExecuteAsync method
// after Prepared() will be executed in prepared mode.
//
var (p1, p2, p3) = connection
.Prepared()
.WithParameters(1, 2, 3) // parameters are positional
.Read<int, int, int>("select @p1, @p2, @p3")
.Single();
//
// Set global options in your startup
// to always execute in prepared mode (default is false).
//
NormOptions.Configure(options =>
{
options.Prepared = true;
});
// No need to call Prepared() anymore, every call is prepared.
var (p1, p2, p3) = connection
.WithParameters(1, 2, 3) // parameters are positional
.Read<int, int, int>("select @p1, @p2, @p3")
.Single();Norm has several built-in support for PostgreSQL-specific optimizations. You can even say it was made for PostgreSQL (although it works with other databases as well).
For example, you can use specific PostgreSQL paremeters format and disable Npgsql query rewriting. See this article.
You can also skip Npgsql parsing type parsing and use direct PostgreSQL text format instead.
For example, you can use specific PostgreSQL paremeters format and disable Npgsql query rewriting. See this article.
You can also skip Npgsql parsing type parsing and use direct PostgreSQL text format instead.
using System.Linq;
using Norm;
//
// When using PostgreSQL, you can use PostgreSQL paremeter format.
// This will allow Npgsql to skip query rewriting.
//
var (s, i, b, d) = connection
// parameters are positional
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, int, bool, DateTime, string>("select $1, $2, $3, $4")
.Single();
//
// You can globally disable query rewriting by setting
// NpgsqlEnableSqlRewriting to false in your startup.
// This will ensure that all queries are intact by underlying Npgsql driver
// (skips query rewriting).
// In that case, PostgreSQL paremeter format is required always.
//
NormOptions.Configure(options =>
{
options.NpgsqlEnableSqlRewriting = false;
});
//
// Marks all of the query result columns as unknown.
// Unknown result columns are requested from PostgreSQL in text format,
// and Npgsql makes no attempt to parse them (accessible as strings).
//
// This is useful when you want to avoid the overhead of parsing
// or you want to fetch some exotic type from PostgreSQL.
//
// Note: you can write your own parser with `WithReaderCallback` method.
//
var (s, i, b, d) = connection
.WithUnknownResultType()
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, string, string, string, string>("select $1, $2, $3, $4")
.Single();
//
// Marks all of the query result columns as unknown, except first one.
//
var (s, i, b, d) = connection
.WithUnknownResultType(false, true, true, true)
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, string, string, string, string>("select $1, $2, $3, $4")
.Single();using System.Linq;
using Norm;
//
// When using PostgreSQL, you can use PostgreSQL paremeter format.
// This will allow Npgsql to skip query rewriting.
//
var (s, i, b, d) = connection
// parameters are positional
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, int, bool, DateTime, string>("select $1, $2, $3, $4")
.Single();
//
// You can globally disable query rewriting by setting
// NpgsqlEnableSqlRewriting to false in your startup.
// This will ensure that all queries are intact by underlying Npgsql driver
// (skips query rewriting).
// In that case, PostgreSQL paremeter format is required always.
//
NormOptions.Configure(options =>
{
options.NpgsqlEnableSqlRewriting = false;
});
//
// Marks all of the query result columns as unknown.
// Unknown result columns are requested from PostgreSQL in text format,
// and Npgsql makes no attempt to parse them (accessible as strings).
//
// This is useful when you want to avoid the overhead of parsing
// or you want to fetch some exotic type from PostgreSQL.
//
// Note: you can write your own parser with `WithReaderCallback` method.
//
var (s, i, b, d) = connection
.WithUnknownResultType()
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, string, string, string, string>("select $1, $2, $3, $4")
.Single();
//
// Marks all of the query result columns as unknown, except first one.
//
var (s, i, b, d) = connection
.WithUnknownResultType(false, true, true, true)
.WithParameters("str", 999, true, new DateTime(1977, 5, 19))
.Read<string, string, string, string, string>("select $1, $2, $3, $4")
.Single();You can gain access to the low-level data reader object (type
This provides the ability to do some interesting stuff, like:
- Do some custom mapping.
- Return alternative values.
- Create complex objects with different reader values, etc.
The following example shows how to return a value incremented by 1 a) for all columns at the first position and b) for all columns with the name "i".
DbDataReader) - created for every query command. Reader callback accepts returning values and passes them down to the default mapper.This provides the ability to do some interesting stuff, like:
- Do some custom mapping.
- Return alternative values.
- Create complex objects with different reader values, etc.
The following example shows how to return a value incremented by 1 a) for all columns at the first position and b) for all columns with the name "i".
using System.Linq;
using Norm;
const string query = @"
select * from (values
(1, 1),
(2, 2),
(3, 3)
) t(i, j)";
//
// You can gain access to the underlying DbDataReader by
// using the WithReaderCallback method.
//
// This method allows you to perform any custom processing on the DbDataReader.
// Return null to indicate that the default value should be used.
// If you need to return a literal null value, return `DBNull.Value`.
//
// This example will i and j tuple, where i value is incremented by 1.
//
var result = connection
.WithReaderCallback(r => r.Ordinal switch
{
0 => r.Reader.GetInt32(r.Ordinal) + 1,
_ => null // default value
})
.Read<(int i, int j)>(query)
.ToArray();
//
// You can also use the column name instead of the ordinal position.
//
var result = connection
.WithReaderCallback(arg => arg.Name switch
{
"i" => arg.Reader.GetInt32(arg.Ordinal) + 1,
_ => null // default value
})
.Read<(int i, int j)>(query)
.ToArray();
//
// You can also declare a method that implements the reader callback
//
private object? ReaderCallback((string Name, int Ordinal, DbDataReader Reader) arg)
{
return arg => arg.Name switch
{
"i" => arg.Reader.GetInt32(arg.Ordinal) + 1,
_ => null // default value
}
}
// map reader callback to method
var result = connection
.WithReaderCallback(ReaderCallback)
// map to anonymous type by name
.Read(new { i = default(int), j = default(int) }, query)
.ToArray();using System.Linq;
using Norm;
const string query = @"
select * from (values
(1, 1),
(2, 2),
(3, 3)
) t(i, j)";
//
// You can gain access to the underlying DbDataReader by
// using the WithReaderCallback method.
//
// This method allows you to perform any custom processing on the DbDataReader.
// Return null to indicate that the default value should be used.
// If you need to return a literal null value, return `DBNull.Value`.
//
// This example will i and j tuple, where i value is incremented by 1.
//
var result = connection
.WithReaderCallback(r => r.Ordinal switch
{
0 => r.Reader.GetInt32(r.Ordinal) + 1,
_ => null // default value
})
.Read<(int i, int j)>(query)
.ToArray();
//
// You can also use the column name instead of the ordinal position.
//
var result = connection
.WithReaderCallback(arg => arg.Name switch
{
"i" => arg.Reader.GetInt32(arg.Ordinal) + 1,
_ => null // default value
})
.Read<(int i, int j)>(query)
.ToArray();
//
// You can also declare a method that implements the reader callback
//
private object? ReaderCallback((string Name, int Ordinal, DbDataReader Reader) arg)
{
return arg => arg.Name switch
{
"i" => arg.Reader.GetInt32(arg.Ordinal) + 1,
_ => null // default value
}
}
// map reader callback to method
var result = connection
.WithReaderCallback(ReaderCallback)
// map to anonymous type by name
.Read(new { i = default(int), j = default(int) }, query)
.ToArray();You can configure a global reader callback that will be used for all queries in your application.
This is useful when you want to implement your custom mapping for the entire application.
For example, I want all database type
Here is how to do that:
This is useful when you want to implement your custom mapping for the entire application.
For example, I want all database type
json to be mapped to JsonObject type always.Here is how to do that:
using System.Linq;
using System.Text.Json.Nodes;
using Norm;
//
// You can also set a global reader callback
// that will be executed for every query in the application.
//
// This is a great way to implement custom type mapping.
// Here is an example of how to map all database `json` types to `JsonObject`
//
// Reader callback can be in a expression method instead of a lambda function
private object? ReaderCallback((string Name, int Ordinal, DbDataReader Reader) arg) =>
// switch over the column type
arg.Reader.GetDataTypeName(arg.Ordinal) switch
{
// if the column type is json, then convert it to JsonObject
"json" => JsonNode.Parse(arg.Reader.GetString(arg.Ordinal))?.AsObject(),
_ => null // default value
};
// Set the option in the your startup code...
NormOptions.Configure(options => options.DbReaderCallback = ReaderCallback);
// example class (I is string and J is JsonObject)
private class JsonTest
{
public string I { get; set; }
public JsonObject J { get; set; }
}
// example query
var result = connection
.Read<JsonTest>(
// column i is a text and column j is a json
"select '{\"a\": 1}'::text as i, '{\"a\": 1}'::json as j")
.Single();
// assertion
Assert.IsType<JsonObject>(result.J); // property J is JsonObject
Assert.Equal(1, (int)result.J["a"]); // property J has a property "a" with value 1using System.Linq;
using System.Text.Json.Nodes;
using Norm;
//
// You can also set a global reader callback
// that will be executed for every query in the application.
//
// This is a great way to implement custom type mapping.
// Here is an example of how to map all database `json` types to `JsonObject`
//
// Reader callback can be in a expression method instead of a lambda function
private object? ReaderCallback((string Name, int Ordinal, DbDataReader Reader) arg) =>
// switch over the column type
arg.Reader.GetDataTypeName(arg.Ordinal) switch
{
// if the column type is json, then convert it to JsonObject
"json" => JsonNode.Parse(arg.Reader.GetString(arg.Ordinal))?.AsObject(),
_ => null // default value
};
// Set the option in the your startup code...
NormOptions.Configure(options => options.DbReaderCallback = ReaderCallback);
// example class (I is string and J is JsonObject)
private class JsonTest
{
public string I { get; set; }
public JsonObject J { get; set; }
}
// example query
var result = connection
.Read<JsonTest>(
// column i is a text and column j is a json
"select '{\"a\": 1}'::text as i, '{\"a\": 1}'::json as j")
.Single();
// assertion
Assert.IsType<JsonObject>(result.J); // property J is JsonObject
Assert.Equal(1, (int)result.J["a"]); // property J has a property "a" with value 1Besides access to the data reader, you can gain access to the
By using global command callback, you can implement logging and analytics for all queries in your application, see the example below.
To improve application analytics, you can configure Norm to automatically add a comment header to every command text that can contain the following information:
- Command type and timeout.
- Exact source file name and line number (from compiler metadata).
- Parameter names and values.
This information will not only be visible when you log commands, but it will also be visible in your database administration tools such as Activity Monitor for SQL Server or pg_stat_activity on PostgreSQL which can be very useful for database administrators.
DbCommand object also, for all commands created by Norm.By using global command callback, you can implement logging and analytics for all queries in your application, see the example below.
To improve application analytics, you can configure Norm to automatically add a comment header to every command text that can contain the following information:
- Command type and timeout.
- Exact source file name and line number (from compiler metadata).
- Parameter names and values.
This information will not only be visible when you log commands, but it will also be visible in your database administration tools such as Activity Monitor for SQL Server or pg_stat_activity on PostgreSQL which can be very useful for database administrators.
using System.Linq;
using Norm;
//
// Set global handler in your startup
// for each DbCommand.created by Norm.
//
// You can use this to log every query executed with Norm in your application.
//
NormOptions.Configure(options =>
options.DbCommandCallback = command =>
logger.LogInformation(command.CommandText));
// ... later in your application
var result = connection
.Read<FooBar>(@"select foo, bar from foo_bar where id = @id", id)
.ToArray();
//
// Console output:
//
// info: Application.Logger[0]
// select foo, bar from foo_bar where id = @id
//
//
// Configure Norm to include comment header in each query
// that will include one or more entries like:
// - Comand type (Text, StoredProcedure, TableDirect) and timeout.
// - Method name and source file path from compiler metadata.
// - Parameter values.
// - Custom comments.
//
// Example:
//
NormOptions.Configure(options =>
{
options.CommandCommentHeader.Enabled = true;
options.DbCommandCallback = command =>
logger.LogInformation(command.CommandText);
});
// ... later in your application
var result = connection
.Read<FooBar>(@"select foo, bar from foo_bar where id = @id", id)
.ToArray();
//
// Console output:
//
// info: Application.Logger[0]
// /*
// Sql Text Command. Timeout: 30 seconds.
// at GetData in /home/user/project/MyModule.cs#10
// @id = 1
// */
// select foo, bar from foo_bar where id = @id
//using System.Linq;
using Norm;
//
// Set global handler in your startup
// for each DbCommand.created by Norm.
//
// You can use this to log every query executed with Norm in your application.
//
NormOptions.Configure(options =>
options.DbCommandCallback = command =>
logger.LogInformation(command.CommandText));
// ... later in your application
var result = connection
.Read<FooBar>(@"select foo, bar from foo_bar where id = @id", id)
.ToArray();
//
// Console output:
//
// info: Application.Logger[0]
// select foo, bar from foo_bar where id = @id
//
//
// Configure Norm to include comment header in each query
// that will include one or more entries like:
// - Comand type (Text, StoredProcedure, TableDirect) and timeout.
// - Method name and source file path from compiler metadata.
// - Parameter values.
// - Custom comments.
//
// Example:
//
NormOptions.Configure(options =>
{
options.CommandCommentHeader.Enabled = true;
options.DbCommandCallback = command =>
logger.LogInformation(command.CommandText);
});
// ... later in your application
var result = connection
.Read<FooBar>(@"select foo, bar from foo_bar where id = @id", id)
.ToArray();
//
// Console output:
//
// info: Application.Logger[0]
// /*
// Sql Text Command. Timeout: 30 seconds.
// at GetData in /home/user/project/MyModule.cs#10
// @id = 1
// */
// select foo, bar from foo_bar where id = @id
//Comments