internal static void SetSqlParameters(this DbContext context, DbCommand command)
{
foreach (DbParameter param in command.Parameters)
{
// Item1 = Filter Name
// Item2 = Parameter/Column Name
var filterNameAndParam = DynamicFilterDefinition.GetFilterAndParamFromDBParameter(param.ParameterName);
if (filterNameAndParam == null)
{
continue; // Not dynamic filter param
}
object value = context.GetFilterParameterValue(filterNameAndParam.Item1, filterNameAndParam.Item2);
// If not found, leave as the default that EF assigned (which will be a DBNull)
if (value == null)
{
continue;
}
// Check to see if it's a collection. If so, this is an "In" parameter
var valueType = value.GetType();
if (valueType.IsGenericType && typeof(IEnumerable).IsAssignableFrom(valueType))
{
// Generic collection. The EF query created for this collection was an '=' condition.
// We need to convert this into an 'in' clause so that we can dynamically set the
// values in the collection.
SetParameterList(value as IEnumerable, param, command, IsOracle(context));
context.Database.Log(string.Format("Manually replaced single parameter value with list, new SQL=\r\n{0}", command.CommandText));
}
else
{
param.Value = value;
}
}
}
internal static void SetSqlParameters(this DbContext context, DbCommand command)
{
foreach (DbParameter param in command.Parameters)
{
// Item1 = Filter Name
// Item2 = Parameter/Column Name
var filterNameAndParam = DynamicFilterDefinition.GetFilterAndParamFromDBParameter(param.ParameterName);
if (filterNameAndParam == null)
{
continue; // Not dynamic filter param
}
object value = context.GetFilterParameterValue(filterNameAndParam.Item1, filterNameAndParam.Item2);
// If not found, set to DBNull. It should already be set to that, but it's not in Postgre and we get
// errors if we don't do that now.
if (value == null)
{
if (filterNameAndParam.Item2 == DynamicFilterConstants.FILTER_DISABLED_NAME)
{
// This is the "is disabled" param and the filter is not disabled. There are cases where
// including the "OR (@DynamicFilterParam_1 IS NOT NULL)" clause causes problems with
// SQL Server index usage (it may decide not to use a multi-column index where the first
// column is the filtered column and another column is also included in the where clause).
// See issue #53.
// So we're manually removing that entire condition from the sql. This will not remove the
// parameter from being sent, but it will not be part of the sql statement.
RemoveFilterDisabledConditionFromQuery(command, param);
#if (DEBUG)
if (!string.IsNullOrEmpty(command.CommandText) && command.CommandText.Contains(param.ParameterName))
{
throw new ApplicationException(string.Format("CommandText still contains ParameterName {0} after RemoveFilterDisabledConditionFromQuery", param.ParameterName));
}
#endif
}
param.Value = DBNull.Value;
}
else
{
// Check to see if it's a collection. If so, this is an "In" parameter
var valueType = value.GetType();
if (valueType.IsGenericType && typeof(IEnumerable).IsAssignableFrom(valueType))
{
// Generic collection. The EF query created for this collection was an '=' condition.
// We need to convert this into an 'in' clause so that we can dynamically set the
// values in the collection.
SetParameterList(value as IEnumerable, param, command, IsOracle(context), IsMySql(context));
if (context.Database.Log != null)
{
context.Database.Log(string.Format("Manually replaced single parameter value with list, new SQL=\r\n{0}", command.CommandText));
}
}
else
{
param.Value = value;
}
}
}
}
