public static Task <int> MergeAsync <T>(
this ITable <T> table,
IEnumerable <T> source,
string?tableName = default,
string?databaseName = default,
string?schemaName = default,
string?serverName = default,
TableOptions tableOptions = default,
CancellationToken cancellationToken = default)
where T : class
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var withUpdate = MergeWithUpdate(table);
if (!source.Any())
{
return(TaskCache.Zero);
}
var target = table;
if (tableName != null)
{
target = target.TableName(tableName);
}
if (databaseName != null)
{
target = target.DatabaseName(databaseName);
}
if (schemaName != null)
{
target = target.SchemaName(schemaName);
}
if (serverName != null)
{
target = target.ServerName(serverName);
}
if (tableOptions.IsSet())
{
target = target.TableOptions(tableOptions);
}
var query = target
.Merge()
.Using(source)
.OnTargetKey();
if (withUpdate)
{
query = query.UpdateWhenMatched();
}
return(query
.InsertWhenNotMatched()
.MergeAsync(cancellationToken));
}
static Query <object> CreateQuery(
IDataContext dataContext,
EntityDescriptor descriptor,
[DisallowNull] T obj,
InsertColumnFilter <T>?columnFilter,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var sqlQuery = new SelectQuery();
var insertStatement = new SqlInsertStatement(sqlQuery)
{
Insert = { Into = sqlTable, WithIdentity = true }
};
var ei = new Query <object>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = insertStatement,
} }
};
foreach (var field in sqlTable.Fields.Where(x => columnFilter == null || columnFilter(obj, x.ColumnDescriptor)))
{
if (field.IsInsertable && !field.ColumnDescriptor.ShouldSkip(obj !, descriptor, SkipModification.Insert))
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
insertStatement.Insert.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
public static int Merge <T>(
this ITable <T> table,
IQueryable <T> source,
Expression <Func <T, bool> > predicate,
string?tableName = default,
string?databaseName = default,
string?schemaName = default,
string?serverName = default,
TableOptions tableOptions = default
)
where T : class
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var withUpdate = MergeWithUpdate(table);
var target = table;
if (tableName != null)
{
target = target.TableName(tableName);
}
if (databaseName != null)
{
target = target.DatabaseName(databaseName);
}
if (schemaName != null)
{
target = target.SchemaName(schemaName);
}
if (serverName != null)
{
target = target.ServerName(serverName);
}
if (tableOptions.IsSet())
{
target = target.TableOptions(tableOptions);
}
var query = target
.Merge()
.Using(source.Where(predicate).AsEnumerable())
.OnTargetKey();
if (withUpdate)
{
query = query.UpdateWhenMatched();
}
return(query
.InsertWhenNotMatched()
.DeleteWhenNotMatchedBySourceAnd(predicate)
.Merge());
}
static Query <int> CreateQuery(
IDataContext dataContext,
EntityDescriptor descriptor,
T obj,
InsertColumnFilter <T>?columnFilter,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null || schemaName != null || databaseName != null || databaseName != null)
{
sqlTable.TableName = new(
tableName ?? sqlTable.TableName.Name,
Server : serverName ?? sqlTable.TableName.Server,
Database : databaseName ?? sqlTable.TableName.Database,
Schema : schemaName ?? sqlTable.TableName.Schema);
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var insertStatement = new SqlInsertStatement {
Insert = { Into = sqlTable }
};
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = insertStatement
} }
};
foreach (var field in sqlTable.Fields.Where(x => columnFilter == null || columnFilter(obj, x.ColumnDescriptor)))
{
if (field.IsInsertable && !field.ColumnDescriptor.ShouldSkip(obj !, descriptor, SkipModification.Insert))
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].AddParameterAccessor(param);
insertStatement.Insert.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
public static async Task QueryAsync(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
bool?ifExists,
TableOptions tableOptions,
CancellationToken token)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
var dropTable = new SqlDropTableStatement(sqlTable);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
sqlTable.Set(ifExists, TableOptions.DropIfExists);
var query = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = dropTable,
} }
};
SetNonQueryQuery(query);
await query.GetElementAsync(dataContext, Expression.Constant(null), null, null, token).ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
public static void Query(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
bool?ifExists,
TableOptions tableOptions)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
var dropTable = new SqlDropTableStatement(sqlTable);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
sqlTable.Set(ifExists, TableOptions.DropIfExists);
var query = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = dropTable
} }
};
SetNonQueryQuery(query);
query.GetElement(dataContext, Expression.Constant(null), null, null);
}
public static void Query(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
bool?ifExists,
TableOptions tableOptions)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
var dropTable = new SqlDropTableStatement(sqlTable);
if (tableName != null || schemaName != null || databaseName != null || databaseName != null)
{
sqlTable.TableName = new(
tableName ?? sqlTable.TableName.Name,
Server : serverName ?? sqlTable.TableName.Server,
Database : databaseName ?? sqlTable.TableName.Database,
Schema : schemaName ?? sqlTable.TableName.Schema);
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
sqlTable.Set(ifExists, TableOptions.DropIfExists);
var query = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = dropTable
} }
};
SetNonQueryQuery(query);
query.GetElement(dataContext, ExpressionInstances.UntypedNull, null, null);
}
static Query <int>?CreateQuery(
IDataContext dataContext,
EntityDescriptor descriptor,
T obj,
UpdateColumnFilter <T>?columnFilter,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var sqlQuery = new SelectQuery();
var updateStatement = new SqlUpdateStatement(sqlQuery);
sqlQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = updateStatement,
} }
};
var keys = sqlTable.GetKeys(true).Cast <SqlField>().ToList();
var fields = sqlTable.Fields
.Where(f => f.IsUpdatable && !f.ColumnDescriptor.ShouldSkip(obj !, descriptor, SkipModification.Update) && (columnFilter == null || columnFilter(obj, f.ColumnDescriptor)))
.Except(keys);
var fieldCount = 0;
foreach (var field in fields)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
updateStatement.Update.Items.Add(new SqlSetExpression(field, param.SqlParameter));
fieldCount++;
}
if (fieldCount == 0)
{
if (Configuration.Linq.IgnoreEmptyUpdate)
{
return(null);
}
throw new LinqException(
keys.Count == sqlTable.Fields.Count ?
$"There are no fields to update in the type '{sqlTable.Name}'. No PK is defined or all fields are keys." :
$"There are no fields to update in the type '{sqlTable.Name}'.");
}
foreach (var field in keys)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
sqlQuery.Where.Field(field).Equal.Expr(param.SqlParameter);
if (field.CanBeNull)
{
sqlQuery.IsParameterDependent = true;
}
}
SetNonQueryQuery(ei);
return(ei);
}
static Query <int> CreateQuery(
IDataContext dataContext,
EntityDescriptor descriptor,
T obj,
InsertOrUpdateColumnFilter <T>?columnFilter,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var fieldDic = new Dictionary <SqlField, ParameterAccessor>();
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null || schemaName != null || databaseName != null || databaseName != null)
{
sqlTable.TableName = new(
tableName ?? sqlTable.TableName.Name,
Server : serverName ?? sqlTable.TableName.Server,
Database : databaseName ?? sqlTable.TableName.Database,
Schema : schemaName ?? sqlTable.TableName.Schema);
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var sqlQuery = new SelectQuery();
ParameterAccessor?param = null;
var insertOrUpdateStatement = new SqlInsertOrUpdateStatement(sqlQuery);
insertOrUpdateStatement.Insert.Into = sqlTable;
insertOrUpdateStatement.Update.Table = sqlTable;
sqlQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = insertOrUpdateStatement,
} }
};
var supported = ei.SqlProviderFlags.IsInsertOrUpdateSupported && ei.SqlProviderFlags.CanCombineParameters;
// Insert.
//
foreach (var field in sqlTable.Fields)
{
if (field.IsInsertable && !field.ColumnDescriptor.ShouldSkip(obj !, descriptor, SkipModification.Insert))
{
if (columnFilter == null || columnFilter(obj, field.ColumnDescriptor, true))
{
if (!supported || !fieldDic.TryGetValue(field, out param))
{
param = GetParameter(type, dataContext, field);
ei.Queries[0].AddParameterAccessor(param);
if (supported)
{
fieldDic.Add(field, param);
}
}
insertOrUpdateStatement.Insert.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
}
public static async Task <ITable <T> > QueryAsync(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
string?statementHeader,
string?statementFooter,
DefaultNullable defaultNullable,
TableOptions tableOptions,
CancellationToken token)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
var createTable = new SqlCreateTableStatement(sqlTable);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
createTable.StatementHeader = statementHeader;
createTable.StatementFooter = statementFooter;
createTable.DefaultNullable = defaultNullable;
var query = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = createTable,
} }
};
SetNonQueryQuery(query);
await query.GetElementAsync(dataContext, ExpressionInstances.UntypedNull, null, null, token).ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
ITable <T> table = new Table <T>(dataContext);
if (sqlTable.PhysicalName != null)
{
table = table.TableName(sqlTable.PhysicalName);
}
if (sqlTable.Server != null)
{
table = table.ServerName(sqlTable.Server);
}
if (sqlTable.Database != null)
{
table = table.DatabaseName(sqlTable.Database);
}
if (sqlTable.Schema != null)
{
table = table.SchemaName(sqlTable.Schema);
}
if (sqlTable.TableOptions.IsSet())
{
table = table.TableOptions(sqlTable.TableOptions);
}
return(table);
}
public static ITable <T> Query(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
string?statementHeader,
string?statementFooter,
DefaultNullable defaultNullable,
TableOptions tableOptions)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
var createTable = new SqlCreateTableStatement(sqlTable);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
createTable.StatementHeader = statementHeader;
createTable.StatementFooter = statementFooter;
createTable.DefaultNullable = defaultNullable;
var query = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = createTable,
} }
};
SetNonQueryQuery(query);
query.GetElement(dataContext, Expression.Constant(null), null, null);
ITable <T> table = new Table <T>(dataContext);
if (sqlTable.PhysicalName != null)
{
table = table.TableName(sqlTable.PhysicalName);
}
if (sqlTable.Server != null)
{
table = table.ServerName(sqlTable.Server);
}
if (sqlTable.Database != null)
{
table = table.DatabaseName(sqlTable.Database);
}
if (sqlTable.Schema != null)
{
table = table.SchemaName(sqlTable.Schema);
}
if (sqlTable.TableOptions.IsSet())
{
table = table.TableOptions(sqlTable.TableOptions);
}
return(table);
}
static Query <int> CreateQuery(
IDataContext dataContext,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var deleteStatement = new SqlDeleteStatement();
deleteStatement.SelectQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = deleteStatement,
} }
};
var keys = sqlTable.GetKeys(true).Cast <SqlField>().ToList();
if (keys.Count == 0)
{
throw new LinqException($"Table '{sqlTable.Name}' does not have primary key.");
}
foreach (var field in keys)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
deleteStatement.SelectQuery.Where.Field(field).Equal.Expr(param.SqlParameter);
if (field.CanBeNull)
{
deleteStatement.IsParameterDependent = true;
}
}
SetNonQueryQuery(ei);
return(ei);
}
public static int Merge <T>(
this ITable <T> table,
bool delete,
IEnumerable <T> source,
string?tableName = default,
string?databaseName = default,
string?schemaName = default,
string?serverName = default,
TableOptions tableOptions = default
)
where T : class
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var withUpdate = MergeWithUpdate(table);
if (!source.Any())
{
return(0);
}
var target = table;
if (tableName != null)
{
target = target.TableName(tableName);
}
if (databaseName != null)
{
target = target.DatabaseName(databaseName);
}
if (schemaName != null)
{
target = target.SchemaName(schemaName);
}
if (serverName != null)
{
target = target.ServerName(serverName);
}
if (tableOptions.IsSet())
{
target = target.TableOptions(tableOptions);
}
var query = target
.Merge()
.Using(source)
.OnTargetKey();
Linq.IMergeable <T, T>?merge = null;
if (withUpdate)
{
merge = query.UpdateWhenMatched();
}
merge = (merge ?? query).InsertWhenNotMatched();
if (delete)
{
merge = merge.DeleteWhenNotMatchedBySource();
}
return(merge.Merge());
}