private static void AppendRowsAsValues(StringBuilder sb, InterceptionOptions options, DbCommand command, IList parameters, KnownProvider providerType) { #if EFCore string paramPattern = EntityFrameworkCore.MemoryJoin.MemoryJoiner.ParametersPrefix; #else string paramPattern = EntityFramework.MemoryJoin.MemoryJoiner.ParametersPrefix; #endif Interlocked.Increment(ref parametersSequence); paramPattern = paramPattern + parametersSequence + "_"; var innerSb = new StringBuilder(20); var i = 0; var id = 1; sb.Append("( VALUES "); foreach (var el in options.Data) { sb.Append("("); // Let's append Id anyways, as per Issue #1 sb.Append(id).Append(", "); foreach (var columnName in options.ColumnNames) { var value = el[columnName]; var stringValue = TryProcessParameterAsString(value, providerType, innerSb, options.ValuesInjectMethod); if (stringValue != null) { sb.Append(stringValue); } else { var paramName = $"{paramPattern}{i}"; var param = command.CreateParameter(); param.ParameterName = paramName; param.Value = value; parameters.Add(param); sb.Append(paramName); i++; } sb.Append(", "); } sb.Length -= ", ".Length; sb.Append("), "); id++; } sb.Length -= ", ".Length; sb.Append(")"); }
private static void AppendColumnAliases(StringBuilder sb, InterceptionOptions options) { sb.Append(options.DynamicTableName) .Append("(") .Append(options.KeyColumnName) .Append(", "); foreach (var cname in options.ColumnNames) { sb.Append(cname).Append(", "); } sb.Length -= 2; sb.Append(")"); }
internal static void ComposeTableSql( StringBuilder stringBuilder, InterceptionOptions options, DbCommand command, IList parameters) { var providerType = TypeToKnownProvider.GetOrAdd( options.ContextType, (t) => GetKnownProvider(command)); stringBuilder.Append("SELECT * FROM "); // SQLite doesn't support column aliasing in the FROM clause. Instead we have to do it // in a WITH clause. For example "SELECT * FROM (WITH tempTable(col1, col2, col3) AS (VALUES (1, 2, 3)) SELECT * FROM tempTable)". if (providerType == KnownProvider.Sqlite) { stringBuilder.Append("( WITH "); AppendColumnAliases(stringBuilder, options); stringBuilder.Append(" AS "); } if (options.Data.Any()) { AppendRowsAsValues(stringBuilder, options, command, parameters, providerType); } else { // if we have no data, we just generate 1 row consisting of null, because we need one row to define a table AppendNullRow(stringBuilder, options.ColumnNames.Length + 1); // + 1 since primary key column is not included. } if (providerType == KnownProvider.Sqlite) { stringBuilder.Append($" SELECT * FROM {options.DynamicTableName} )"); } else { stringBuilder.Append("AS "); AppendColumnAliases(stringBuilder, options); } // if we have no data, the in-memory table will consist of a null row. We don't want to return any result. As such, // append a condition which will always be false. if (!options.Data.Any()) { stringBuilder.Append(" WHERE 1=0"); } }
private static void AppendRowsAsValues(StringBuilder sb, InterceptionOptions options, DbCommand command, IList parameters, KnownProvider providerType) { const string paramPattern = "@__gen_q_p"; var innerSb = new StringBuilder(20); var i = 0; var id = 1; sb.Append("( VALUES "); foreach (var el in options.Data) { sb.Append("("); // Let's append Id anyways, as per Issue #1 sb.Append(id).Append(", "); foreach (var columnName in options.ColumnNames) { var value = el[columnName]; var stringValue = TryProcessParameterAsString(value, providerType, innerSb, options.ValuesInjectMethod); if (stringValue != null) { sb.Append(stringValue); } else { var paramName = $"{paramPattern}{i}"; var param = command.CreateParameter(); param.ParameterName = paramName; param.Value = value; parameters.Add(param); sb.Append(paramName); i++; } sb.Append(", "); } sb.Length -= ", ".Length; sb.Append("), "); id++; } sb.Length -= ", ".Length; sb.Append(")"); }
internal static void ComposeTableSql( StringBuilder stringBuilder, InterceptionOptions options, DbCommand command, IList parameters) { var providerType = TypeToKnownProvider.GetOrAdd( options.ContextType, (t) => GetKnownProvider(command)); const string paramPattern = "@__gen_q_p"; var sb = stringBuilder; var innerSb = new StringBuilder(20); sb.Append("SELECT * FROM (VALUES "); if (options.Data.Any()) { var i = 0; var id = 1; foreach (var el in options.Data) { sb.Append("("); // Let's append Id anyways, as per Issue #1 sb.Append(id).Append(", "); foreach (var columnName in options.ColumnNames) { var value = el[columnName]; var stringValue = TryProcessParameterAsString(value, providerType, innerSb, options.ValuesInjectMethod); if (stringValue != null) { sb.Append(stringValue); } else { var paramName = $"{paramPattern}{i}"; var param = command.CreateParameter(); param.ParameterName = paramName; param.Value = value; parameters.Add(param); sb.Append(paramName); i++; } sb.Append(", "); } sb.Length -= 2; sb.Append("), "); id++; } sb.Length -= 2; } else { sb.Append("("); sb.Append("NULL, "); foreach (var columnName in options.ColumnNames) { sb.Append("NULL, "); } sb.Length -= 2; sb.Append(")"); } sb.Append(") AS ").Append(options.DynamicTableName).Append(" ("); sb.Append(options.KeyColumnName).Append(", "); foreach (var cname in options.ColumnNames) { sb.Append(cname).Append(", "); } sb.Length -= 2; sb.Append(")"); if (!options.Data.Any()) { sb.Append(" LIMIT 0"); } }