public async Task <ICursorPageSlice <Droid> > GetPagedDroidCharactersAsync(
IEnumerable <Field> selectFields,
IEnumerable <OrderField> sortFields,
IRepoDbCursorPagingParams pagingParams
)
{
await using var sqlConn = CreateConnection();
//BBernard - 08/09/2021
//NOTE: This Examples shows the use of RepoDb mapping and Raw SQL with Batch Slice Query capabilities;
// which enables powerful field processing and features not supported by QueryField/QueryGroup objects
// (e.g. LOWER(), TRIM(), or Full Text Search via CONTAINS() and FREETEXT()).
var idFieldName = PropertyMappedNameCache.Get <CharacterDbModel>(c => c.Id);
var pageSlice = await sqlConn.GraphQLBatchSliceQueryAsync <CharacterDbModel>(
orderBy : sortFields ?? DefaultCharacterSortFields,
fields : selectFields,
whereRawSql : new RawSqlWhere(@$ "{idFieldName} >= @StartId AND {idFieldName} < @EndId", new { StartId = 2000, EndId = 3000 }),
pagingParams : pagingParams,
commandTimeout : 15
);
var convertedSlice = pageSlice.AsMappedType(r => (Droid)MapDbModelToCharacterModel(r));
return(convertedSlice);
}
/// <summary>
/// Creates a parameter from object by mapping the property from the target entity type.
/// </summary>
/// <param name="command">The command object to be used.</param>
/// <param name="param">The object to be used when creating the parameters.</param>
public static void CreateParameters(this IDbCommand command, object param)
{
if (param == null)
{
return;
}
if (param is IEnumerable <PropertyValue> )
{
var propertyValues = (IEnumerable <PropertyValue>)param;
foreach (var propertyValue in propertyValues)
{
var dbType = propertyValue.Property.GetDbType();
if (dbType == null)
{
if (propertyValue.Value == null && propertyValue.Property.PropertyInfo.PropertyType == typeof(byte[]))
{
dbType = DbType.Binary;
}
}
command.Parameters.Add(command.CreateParameter(propertyValue.Name, propertyValue.Value, dbType));
}
}
else if (param is ExpandoObject)
{
var dictionary = (IDictionary <string, object>)param;
var dbType = (DbType?)null;
foreach (var item in dictionary)
{
var value = item.Value;
if (item.Value is CommandParameter)
{
var commandParameter = (CommandParameter)item.Value;
var property = commandParameter.MappedToType.GetTypeInfo().GetProperty(item.Key);
dbType = property?.GetCustomAttribute <TypeMapAttribute>()?.DbType ??
TypeMapper.Get(GetUnderlyingType(property?.PropertyType))?.DbType;
value = commandParameter.Value;
}
else
{
dbType = TypeMapper.Get(GetUnderlyingType(item.Value?.GetType()))?.DbType;
}
command.Parameters.Add(command.CreateParameter(item.Key, value, dbType));
}
}
else
{
foreach (var property in param.GetType().GetTypeInfo().GetProperties())
{
if (property.PropertyType.IsArray)
{
continue;
}
var dbType = property.GetCustomAttribute <TypeMapAttribute>()?.DbType ??
TypeMapper.Get(GetUnderlyingType(property.PropertyType))?.DbType;
command.Parameters.Add(command.CreateParameter(
PropertyMappedNameCache.Get(property), property.GetValue(param), dbType));
}
}
}
/// <summary>
/// Converts a <see cref="PropertyInfo"/> into a query field object.
/// </summary>
/// <param name="property">The instance of <see cref="PropertyInfo"/> to be converted.</param>
/// <param name="entity">The entity object where the value of the property will be retrieved.</param>
/// <returns>An instance of query field object that holds the converted name and values of the property.</returns>
/// <param name="appendUnderscore">The value to identify whether the underscope prefix will be appended to the parameter name.</param>
internal static QueryField AsQueryField(this PropertyInfo property,
object entity,
bool appendUnderscore)
{
var field = new Field(PropertyMappedNameCache.Get(property), property.PropertyType.GetUnderlyingType());
return(new QueryField(field, Operation.Equal, property.GetValue(entity), appendUnderscore));
}
public void TestColumnAttributeViaPropertyName()
{
// Act
var actual = PropertyMappedNameCache.Get <TestColumnAttributeUnquotedNameClass>("Id");
var expected = "PrimaryId";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithoutMapAttribute()
{
// Act
var actual = PropertyMappedNameCache.Get <PropertyMappedNameCacheTestClass>(e => e.ColumnString);
var expected = "ColumnString";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestMapAttributeForPropertyViaField()
{
// Act
var actual = PropertyMappedNameCache.Get <TestMapAttributeUnquotedNameClass>(new Field("Id"));
var expected = "PrimaryId";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestMapAttributeForPropertyViaExpression()
{
// Act
var actual = PropertyMappedNameCache.Get <TestMapAttributeUnquotedNameClass>(e => e.Id);
var expected = "PrimaryId";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestMapAttributeForPropertyWithQuotedNameViaPropertyName()
{
// Act
var actual = PropertyMappedNameCache.Get <TestMapAttributeQuotedNameClass>("Id");
var expected = "[PrimaryId]";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestFluentMapColumnMappingWithMapAttribute()
{
// Act
var actual = PropertyMappedNameCache.Get <FluentMapperTestWithAttributesClass>(e => e.PropertyString);
var expected = "ColumnString";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaColumnAndMapAttributeViaExpression()
{
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperColumnAndMapAttributeCollisionClass>(e => e.PropertyString);
var expected = "ColumnText";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestColumnAttributeQuotedNameViaExpression()
{
// Act
var actual = PropertyMappedNameCache.Get <TestColumnAttributeQuotedNameClass>(e => e.Id);
var expected = "[PrimaryId]";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestColumnAttributeQuotedNameViaField()
{
// Act
var actual = PropertyMappedNameCache.Get <TestColumnAttributeQuotedNameClass>(new Field("Id"));
var expected = "[PrimaryId]";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaColumnAndMapAttributeViaField()
{
// Setup
var field = new Field("PropertyString");
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperColumnAndMapAttributeCollisionClass>(field);
var expected = "ColumnText";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaColumnAndMapAttributeViaPropertyName()
{
// Setup
var propertyName = "PropertyString";
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperColumnAndMapAttributeCollisionClass>(propertyName);
var expected = "ColumnText";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaColumnAttributeViaExpressionWithMapAttribute()
{
// Setup
PropertyMapper.Add <PropertyMapperColumnAttributeClass>(e => e.PropertyString, "ColumnText");
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperColumnAttributeClass>(e => e.PropertyString);
var expected = "PropertyText";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaExpression()
{
// Setup
PropertyMapper.Add <PropertyMapperTestClass>(e => e.ColumnString, "PropertyString");
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperTestClass>(e => e.ColumnString);
var expected = "PropertyString";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaPropertyNameWithMapAttribute()
{
// Setup
var propertyName = "PropertyString";
PropertyMapper.Add <PropertyMapperTestClass>(propertyName, "ColumnText");
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperTestClass>(propertyName);
var expected = "PropertyText";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaField()
{
// Setup
var field = new Field("ColumnString");
PropertyMapper.Add <PropertyMapperTestClass>(new Field("ColumnString"), "PropertyString");
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperTestClass>(field);
var expected = "PropertyString";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPropertyMapperMappingViaColumnAttributeViaFieldOverride()
{
// Setup
var field = new Field("ColumnString");
PropertyMapper.Add <PropertyMapperColumnAttributeClass>(field, "PropertyString", true);
PropertyMapper.Add <PropertyMapperColumnAttributeClass>(field, "PropertyText", true);
// Act
var actual = PropertyMappedNameCache.Get <PropertyMapperColumnAttributeClass>(field);
var expected = "PropertyText";
// Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// Merge the <see cref="QueryGroup"/> object into the current object.
/// </summary>
/// <param name="obj">The object where the <see cref="QueryGroup"/> object will be merged.</param>
/// <param name="properties">The list of <see cref="PropertyInfo"/> objects.</param>
/// <param name="queryGroup">The <see cref="QueryGroup"/> object to merged.</param>
/// <returns>The object instance itself with the merged values.</returns>
internal static object Merge(this object obj, IEnumerable <PropertyInfo> properties, QueryGroup queryGroup)
{
var expandObject = new ExpandoObject() as IDictionary <string, object>;
foreach (var property in properties)
{
expandObject[PropertyMappedNameCache.Get(property, false)] = property.GetValue(obj);
}
if (queryGroup != null)
{
foreach (var queryField in queryGroup?.Fix().GetFields())
{
expandObject[queryField.Parameter.Name] = queryField.Parameter.Value;
}
}
return((ExpandoObject)expandObject);
}
/// <summary>
/// Creates a parameter from object by mapping the property from the target entity type.
/// </summary>
/// <param name="command">The command object to be used.</param>
/// <param name="param">The object to be used when creating the parameters.</param>
/// <param name="mappedToEntityType">The target type where to map the parameters.</param>
internal static void CreateParameters(this IDbCommand command, object param, Type mappedToEntityType)
{
if (param is IEnumerable <PropertyValue> )
{
var propertyValues = (IEnumerable <PropertyValue>)param;
propertyValues.ToList().ForEach(propertyValue =>
{
var dbType = propertyValue.Property.GetDbType();
command.Parameters.Add(command.CreateParameter(propertyValue.Name, propertyValue.Value, dbType));
});
}
else if (param is ExpandoObject)
{
var dictionary = (IDictionary <string, object>)param;
var dbType = (DbType?)null;
foreach (var item in dictionary)
{
if (mappedToEntityType != null)
{
var property = mappedToEntityType.GetTypeInfo().GetProperty(item.Key);
dbType = property?.GetCustomAttribute <TypeMapAttribute>()?.DbType ??
TypeMapper.Get(GetUnderlyingType(property?.PropertyType))?.DbType;
}
else
{
dbType = TypeMapper.Get(GetUnderlyingType(item.Value?.GetType()))?.DbType;
}
command.Parameters.Add(command.CreateParameter(item.Key, item.Value, dbType));
}
}
else
{
param?.GetType()
.GetTypeInfo()
.GetProperties()
.ToList()
.ForEach(property =>
{
var dbType = property.GetCustomAttribute <TypeMapAttribute>()?.DbType ??
TypeMapper.Get(GetUnderlyingType(property.PropertyType))?.DbType;
command.Parameters.Add(command.CreateParameter(PropertyMappedNameCache.Get(property), property.GetValue(param), dbType));
});
}
}
/// <summary>
/// Creates the command object list of parameters based on type.
/// </summary>
/// <param name="command">The command object instance to be used.</param>
/// <param name="properties">The target list of properties.</param>
/// <param name="propertiesToSkip">The list of the properties to be skipped.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
internal static void CreateParametersFromClassProperties(this IDbCommand command,
IEnumerable <ClassProperty> properties,
IEnumerable <string> propertiesToSkip,
IDbSetting dbSetting)
{
// Filter the target properties
if (propertiesToSkip?.Any() == true)
{
properties = properties?.Where(p =>
propertiesToSkip.Contains(PropertyMappedNameCache.Get(p.PropertyInfo, false, dbSetting), StringComparer.OrdinalIgnoreCase) == false);
}
// Check if there are properties
if (properties?.Any() == true)
{
// Iterate the properties
foreach (var property in properties)
{
// Get the database type
var dbType = property.GetDbType() ??
TypeMapper.Get(property.PropertyInfo.PropertyType.GetUnderlyingType());
// Ensure the type mapping
if (dbType == null)
{
if (property.PropertyInfo.PropertyType == m_bytesType)
{
dbType = DbType.Binary;
}
}
// Create the parameter
var name = PropertyMappedNameCache.Get(property.PropertyInfo, false, dbSetting);
var parameter = CreateParameter(command, name, null, dbType, dbSetting);
// Add the parameter
command.Parameters.Add(parameter);
}
}
}
/// <summary>
/// Converts an instance of <see cref="PropertyInfo"/> object into <see cref="Field"/> object.
/// </summary>
/// <param name="property">The instance of <see cref="PropertyInfo"/> object to be converted.</param>
/// <returns>The converted instance of <see cref="Field"/> object.</returns>
public static Field AsField(this PropertyInfo property)
{
return(new Field(PropertyMappedNameCache.Get(property), property.PropertyType.GetUnderlyingType()));
}
/// <summary>
/// Converts a <see cref="PropertyInfo"/> into a parameterized name.
/// </summary>
/// <param name="property">The instance of the property to be converted.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>A instance of string containing the value of a parameterized name.</returns>
internal static string AsParameterAsString(this PropertyInfo property,
IDbSetting dbSetting)
{
return(string.Concat(dbSetting.ParameterPrefix, PropertyMappedNameCache.Get(property)));
}
/// <summary>
/// Converts a <see cref="PropertyInfo"/> into a mapped name.
/// </summary>
/// <param name="property">The instance of the property to be converted.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>A instance of string containing the value of a mapped name.</returns>
internal static string AsFieldAsString(this PropertyInfo property,
IDbSetting dbSetting)
{
return(PropertyMappedNameCache.Get(property).AsQuoted(true, dbSetting));
}
/// <summary>
/// Converts a property info into a paramertized name.
/// </summary>
/// <param name="property">The instance of the property to be converted.</param>
/// <returns>A instance of string containing the value of a parameterized name.</returns>
public static string AsParameter(this PropertyInfo property)
{
return($"@{PropertyMappedNameCache.Get(property)}");
}
/// <summary>
/// Converts a property info into a mapped name.
/// </summary>
/// <param name="property">The instance of the property to be converted.</param>
/// <returns>A instance of string containing the value of a mapped name.</returns>
public static string AsField(this PropertyInfo property)
{
return($"[{PropertyMappedNameCache.Get(property)}]");
}
/// <summary>
/// Converts a property info into a string qualifier with defined aliases that is usable for SQL Statements.
/// </summary>
/// <param name="property">The property info to be converted.</param>
/// <param name="leftAlias">The left alias to be used.</param>
/// <param name="rightAlias">The right alias to be used.</param>
/// <returns>A instance of string containing the value of converted property info with defined aliases.</returns>
public static string AsJoinQualifier(this PropertyInfo property, string leftAlias, string rightAlias)
{
return($"{leftAlias}.[{PropertyMappedNameCache.Get(property)}] = {rightAlias}.[{PropertyMappedNameCache.Get(property)}]");
}
/// <summary>
/// Converts a property info into a query field object.
/// </summary>
/// <param name="property">The instance of property info to be converted.</param>
/// <param name="entity">The entity object where the value of the property will be retrieved.</param>
/// <returns>An instance of query field object that holds the converted name and values of the property.</returns>
/// <param name="appendParameterPrefix">
/// The value to identify whether the underscope prefix will be appended to the parameter name.
/// </param>
internal static QueryField AsQueryField(this PropertyInfo property, object entity, bool appendParameterPrefix)
{
return(new QueryField(PropertyMappedNameCache.Get(property), Operation.Equal, property.GetValue(entity), appendParameterPrefix));
}
/// <summary>
/// Converts a property info into a paramertized name.
/// </summary>
/// <param name="property">The instance of the property to be converted.</param>
/// <returns>A instance of string containing the value of a parameterized name.</returns>
public static string AsParameter(this PropertyInfo property)
{
return(string.Concat("@", PropertyMappedNameCache.Get(property)));
}