A simple tentaculated object-relational mapper for .Net
$ dotnet add package Eshava.StormEshava.Storm - a simple tentaculated object-relational mapper for .Net
Eshava.Storm is a NuGet library that you can add in to your project that will extend your IDbConnection interface.
It provides 4 helpers:
public static Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection connection, string sql, object parameter = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, CancellationToken cancellationToken = default)
Example usage:
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
}
var alphas = await connection.QueryAsync<Alpha>("SELECT * FROM alphas WHERE Beta = @Beta", new { Beta = "nonsense" });
public static Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection connection, string sql, Func<IObjectMapper, T> map, object parameter = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, CancellationToken cancellationToken = default)
Example usage:
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
public Guid OmegaId {get; set;}
public Omega Omega {get; set;}
}
public class Omega
{
public string Name { get; set; }
}
var query = $@"
SELECT
a.*
,o.*
FROM alphas a
LEFT OUTER JOIN omegas o ON o.Id = a.OmegaId
WHERE a.Beta = @Beta";
var alphas = await connection.QueryAsync<Alpha>(
query,
mapper =>
{
var alpha = mapper.Map<Alpha>("a");
var omega = mapper.Map<Omega>("o");
alpha.Omega = omega;
return alpha;
},
new { Beta = "nonsense" });
public static Task<int> ExecuteAsync(this IDbConnection connection, string sql, object parameter = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, CancellationToken cancellationToken = default)
Example usage:
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
}
await connection.ExecuteAsync("UPDATE alpha SET Beta = @Beta WHERE Id = @Id", new { Beta = "nonsense", Id = Guid.NewGuid() });public static Task<T> ExecuteScalarAsync<T>(this IDbConnection connection, string sql, object parameter = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, CancellationToken cancellationToken = default)Example usage:
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
}
await connection.ExecuteAsync("SELECT COUNT(Id) FROM alpha WHERE Beta = @Beta", new { Beta = "nonsense" });Parameters are passed in as anonymous classes. This allow you to name your parameters easily and gives you the ability to simply cut-and-paste SQL snippets and run them in your db platform's Query analyzer.
new { Gamma = 1, Beta = "nonsense" } // Gamma will be mapped to the param @Gamma, Beta to the param @BetaAlso supported
// Gamma will be mapped to the param @Gamma, Beta to the param @Beta
new List<KeyValuePair<string, object>()
{
new KeyValuePair<string, object>("Gamma", 1),
new KeyValuePair<string, object>("Beta", "nonsense")
};Eshava.Storm allows you to pass in IEnumerable<int> and will automatically parameterize your query.
For example:
connection.QueryAsync<Guid>("SELECT Id FROM alphas where Gamma in @Gammas", new { Gammas = new int[] { 3, 5, 7 } });Will be translated to:
SELECT Id FROM alphas where Gamme in (@Gammas_p1, @Gammas_p2, @Gammas_p3)" -- @Gammas_p0 = 3 , @Gammas_p1 = 5 , @Gammas_p2 = 7Eshava.Storm allows you to fill sub objects which are marked as owns one properties.
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
[OwnsOne]
public Omega Omega {get; set;}
}
public class Omega
{
public string Name { get; set; }
}Sql query example:
SELECT * FROM alphasOR
SELECT
Id
,Beta
,Gamma
,Omega_Name
FROM alphasEshava.Storm allows you to define treatments for custom data types or to change the treatment of known data types
public interface ITypeHandler
{
bool ReadAsByteArray { get; }
void SetValue(IDbDataParameter parameter, object value);
object Parse(Type destinationType, object value);
}OR
public abstract class TypeHandler<T> : ITypeHandler
{
public abstract void SetValue(IDbDataParameter parameter, T value);
public abstract T Parse(object value);
}For example:
public class DateTimeHandler : TypeHandler<DateTime>
{
public override void SetValue(IDbDataParameter parameter, DateTime value)
{
parameter.Value = value.ToUniversalTime();
}
public override DateTime Parse(object value)
{
return DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
}
}Eshava.Storm provides also helpers to simple create, update and delete data objects
The query method allows you to read a data object by id. If the data object has an combined key, you can pass an anonymous type object as id parameter.
public static Task<T> QueryEntityAsync<T>(this IDbConnection connection, object id, IDbTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classThe insert method allows you to define the type of the primary key column
public static Task<K> InsertAsync<T, K>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classpublic static async Task<bool> UpdateAsync<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classThe partial update method analyzes the given data type and passed entity, so that only an anonymous object can be parsed
public static async Task<bool> UpdatePartialAsync<T>(this IDbConnection connection, object entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classFor Example:
public class Alpha
{
public Guid? Id { get; set; }
public string Beta { get; set; }
public int Gamma { get; set; }
}
var partialAlpha = new
{
Id = Guid.NewGuid(),
Beta = "nonsense"
};
await connection.UpdatePartialAsync<Alpha>(partialAlpha);public static async Task<bool> DeleteAsync<T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classpublic static Task BulkInsertAsync<T>(this SqlConnection connection, IEnumerable<T> entitiesToInsert, SqlTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classpublic static Task BulkInsertAsync<T>(this SqlConnection connection, IEnumerable<T> entitiesToInsert, string CustomTableName, SqlTransaction transaction = null, int? commandTimeout = null, CancellationToken cancellationToken = default) where T : classKeyAttributeNotMappedAttributeReadOnlyAttributeDatabaseGeneratedAttributeColumnAttributeTableAttributeThere is also a custom attribute to support the entity framework behaviour with owns one objects
OwnsOneAttributeNone of these attributes have to be set.
Id will be used as fallbackThe attribute DatabaseGeneratedAttribute allows you also to mark properties as identity column (need not to be a primary column).
Register (optional):
Eshava.Storm.MetaData.TypeAnalyzer.AddType<Alpha>();Instead of defining the configuration of the classes via attributes, it is possible to control them via an EntityTypeConfiguration The configuration is based on the behaviour of the Entity Framework Code.
Define:
public class AlphaTypeConfiguration : IEntityTypeConfiguration<Alpha>
{
public void Configure(EntityTypeBuilder<Alpha> builder)
{
...
}
}Register:
Eshava.Storm.MetaData.TypeAnalyzer.AddType(new AlphaTypeConfiguration());/// <summary>
/// Specifies the default Command Timeout for all Queries
/// </summary>
Eshava.Storm.Settings.CommandTimeout = null;
/// <summary>
/// If a column name is duplicated, the duplicates are skipped (by default, all are processed, so the last column wins)
/// Hint: Will be ignored if an table alias is passed for object mapping
/// </summary>
/// <remarks>This setting should be set once at the start of the application; it is not intended to be toggled per-query</remarks>
Eshava.Storm.Settings.IgnoreDuplicatedColumns = false;
/// <summary>
/// If the DatabaseGenerated attribute is not set, the option specifies whether the value of a key column is assumed to be autogenerated.
/// </summary>
Eshava.Storm.Settings.DefaultKeyColumnValueGeneration = DatabaseGeneratedOption.None;
/// <summary>
/// Date and time data. Date value with an accuracy of 100 nanoseconds.
/// </summary>
Eshava.Storm.Settings.EnableDateTimeHighAccuracy = false;
/// <summary>
/// All models must be explicitly registered by a DbConfiguration or by TypeAnalyzer.Register<>() to be used in the mapper.
/// </summary>
Eshava.Storm.Settings.RestrictToRegisteredModels = false;Eshava.Storm.Linq - a extension to Eshava.Storm
Eshava.Storm.Linq is a NuGet library that you can add in to your project that will extend your IEnumerable interface.
It provides 5 helpers:
The linq filter expressions can be created manually or with the WhereQueryEngine from Eshava.Core.Linq is a NuGet library
public static WhereQueryResult AddWhereConditionsToQuery<T>(this IEnumerable<Expression<Func<T, bool>>> queryConditions, string sqlQuery, WhereQuerySettings settings = null) where T : classpublic static WhereQueryResult CalculateWhereConditions<T>(this IEnumerable<Expression<Func<T, bool>>> queryConditions, WhereQuerySettings settings) where T : classThe extension methods produces an WhereQueryResult that contains the raw sql query and a dictionary of parameter
public class WhereQueryResult
{
public string Sql { get; set; }
public Dictionary<string, object> QueryParameter { get; set; }
}The order by conditions can be created with the SortingQueryEngine from Eshava.Core.Linq is a NuGet library
public static string AddSortConditionsToQuery(this IEnumerable<OrderByCondition> orderByConditions, string sqlQuery, QuerySettings settings = null)public static string CalculateSortConditions(this IEnumerable<OrderByCondition> orderByConditions, QuerySettings settings = null)public static string AppendSkipAndTake(this string sqlQuery, int skip, int take)For the four extension method it is recommended to pass property name mappings. This can be achieved in two different ways, explicit or implicit.
public class QuerySettings
{
public Dictionary<string, string> PropertyMappings { get; set; }
public Dictionary<Type, string> PropertyTypeMappings { get; set; }
}
public class WhereQuerySettings : QuerySettings
{
public Dictionary<string, object> QueryParameter { get; set; }
}The Alpha and Omega classes each represent a table and are joined in the example and have the aliases "a" and "o
A possible member expression could be p => p.Omega.Psi und should be translated to o.Psi. Therefore a mapping must be created.
public class Alpha
{
public string Beta { get; set; }
public Omega Omega { get; set;}
}
public class Omega
{
public string Psi { get; set; }
}During processing the expression p => p.Omega.Psi becomes the string .Omega.Psi.
So that the mapping looks like:
settings.PropertyMappings.Add(".Omega.Psi","o.Psi");During processing the expression p => p.Omega.Psi becomes the string .Omega.Psi.
So that the mapping looks like:
settings.PropertyTypeMappings.Add(typeof(Omega),"o");In a later stage there will be a intelligent processing of the member expressions. For processing, the DbConfigurations shall be extended by navigation relations, so that Joins and OwnsOnes can be recognized and translated automatically.