예제 #1
0
        public void Update <T>(IEnumerable <T> entities)
        {
            var conn           = NpgsqlHelper.GetNpgsqlConnection(context);
            var connOpenedHere = EnsureConnected(conn);
            var transaction    = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);

            var mapping = GetEntityInfo <T>();

            try
            {
                // 0. Prepare variables
                var dataColumns   = mapping.ClientDataWithKeysColumnNames;
                var tableName     = mapping.TableNameQualified;
                var tempTableName = "_temp_" + DateTime.Now.Ticks;
                var codeBuilder   = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;

                // 1. Create temp table
                var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
                //var sql = $"CREATE TABLE {tempTableName} AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
                context.Database.ExecuteSqlCommand(sql);

                // 2. Import into temp table
                using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForUpdateQueryPart}) FROM STDIN (FORMAT BINARY)"))
                {
                    foreach (var item in entities)
                    {
                        importer.StartRow();
                        codeBuilder.ClientDataWithKeyWriterAction(item, importer);
                    }
                }

                // 3. Insert into real table from temp one
                foreach (var part in mapping.UpdateQueryParts)
                {
                    // 3.a Needs to accure lock
                    if (LockLevelOnUpdate != TableLockLevel.NoLock)
                    {
                        sql = $"LOCK TABLE {part.TableNameQualified} IN {LockLevelToString(LockLevelOnUpdate)} MODE;";
                        context.Database.ExecuteSqlCommand(sql);
                    }
                    sql = $"UPDATE {part.TableNameQualified} SET {part.SetClause} FROM {tempTableName} as source WHERE {part.WhereClause}";
                    context.Database.ExecuteSqlCommand(sql);
                }

                // 5. Commit
                transaction?.Commit();
            }
            catch
            {
                transaction?.Rollback();
                throw;
            }
            finally
            {
                if (connOpenedHere)
                {
                    conn.Close();
                }
            }
        }
예제 #2
0
        /// <summary>
        /// Simplified version of Insert which works better for huge sets (not calling ToList internally).
        /// Note: it imports directly to target table, doesn't use RETURING, doesn't support inheritance
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="onConflict"></param>
        public async Task ImportAsync <T>(IEnumerable <T> entities)
        {
            var mapping = GetEntityInfo <T>();

            if (mapping.InsertQueryParts.Count > 1)
            {
                throw new NotSupportedException($"Import doesn't support entities with inheritance for now");
            }

            var conn           = NpgsqlHelper.GetNpgsqlConnection(context);
            var connOpenedHere = await EnsureConnectedAsync(conn);

            var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);

            try
            {
                // Prepare variables
                var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;

                // Import
                using (var importer = conn.BeginBinaryImport($"COPY {mapping.TableNameQualified} ({mapping.InsertQueryParts[0].TargetColumnNamesQueryPart}) FROM STDIN (FORMAT BINARY)"))
                {
                    foreach (var item in entities)
                    {
                        importer.StartRow();
                        codeBuilder.ClientDataWriterAction(item, importer);
                    }
                    importer.Complete();
                }

                // Commit
                transaction?.Commit();
            }
            catch
            {
                try
                {
                    transaction?.Rollback();
                }
                catch { }

                throw;
            }
            finally
            {
                if (connOpenedHere)
                {
                    conn.Close();
                }
            }
        }
예제 #3
0
        private async Task UpdateAsyncInternal <T>(IEnumerable <T> entities, EntityInfo mapping)
        {
            var conn           = NpgsqlHelper.GetNpgsqlConnection(context);
            var connOpenedHere = await EnsureConnectedAsync(conn);

            var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);

            try
            {
                // 0. Prepare variables
                var dataColumns   = mapping.ClientDataWithKeysColumnNames;
                var tableName     = mapping.TableNameQualified;
                var tempTableName = GetUniqueName("_temp_");
                var codeBuilder   = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;

                // 1. Create temp table
                var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
                //var sql = $"CREATE TABLE {tempTableName} AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";

#if NETSTANDARD2_1
                await context.Database.ExecuteSqlRawAsync(sql);
#else
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
                await context.Database.ExecuteSqlCommandAsync(sql);

#pragma warning restore EF1000 // Possible SQL injection vulnerability.
#endif

                // 2. Import into temp table
                using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForUpdateQueryPart}) FROM STDIN (FORMAT BINARY)"))
                {
                    foreach (var item in entities)
                    {
                        importer.StartRow();
                        codeBuilder.ClientDataWithKeyWriterAction(item, importer);
                    }
                    importer.Complete();
                }

#pragma warning disable EF1000 // Possible SQL injection vulnerability.
                // 3. Insert into real table from temp one
                foreach (var part in mapping.UpdateQueryParts)
                {
                    // 3.a Needs to accure lock
                    if (LockLevelOnUpdate != TableLockLevel.NoLock)
                    {
                        sql = $"LOCK TABLE {part.TableNameQualified} IN {LockLevelToString(LockLevelOnUpdate)} MODE;";
#if NETSTANDARD2_1
                        await context.Database.ExecuteSqlRawAsync(sql);
#else
                        await context.Database.ExecuteSqlCommandAsync(sql);
#endif
                    }
                    sql = $"UPDATE {part.TableNameQualified} SET {part.SetClause} FROM {tempTableName} as source WHERE {part.WhereClause}";
#if NETSTANDARD2_1
                    await context.Database.ExecuteSqlRawAsync(sql);
#else
                    await context.Database.ExecuteSqlCommandAsync(sql);
#endif
                }
#pragma warning restore EF1000 // Possible SQL injection vulnerability.

                // 5. Commit
                transaction?.Commit();
            }
            catch
            {
                try
                {
                    transaction?.Rollback();
                }
                catch { }

                throw;
            }
            finally
            {
                if (connOpenedHere)
                {
                    conn.Close();
                }
            }
        }
예제 #4
0
        public async Task InsertAsync <T>(IEnumerable <T> entities, InsertConflictAction onConflict)
        {
            var conn           = NpgsqlHelper.GetNpgsqlConnection(context);
            var connOpenedHere = await EnsureConnectedAsync(conn);

            var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);
            var mapping     = GetEntityInfo <T>();

            var ignoreDuplicatesStatement = onConflict?.GetSql(mapping);

            try
            {
                // 0. Prepare variables
                var tempTableName = GetUniqueName("_temp_");
                var list          = entities.ToList();
                var codeBuilder   = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;

                // 1. Create temp table
                var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
                //var sql = $"CREATE {tempTableName} AS {mapping.SelectSourceForInsertQuery} LIMIT 0";


#if NETSTANDARD2_1
                await context.Database.ExecuteSqlRawAsync(sql);

                sql = $"ALTER TABLE {tempTableName} ADD COLUMN __index integer";
                await context.Database.ExecuteSqlRawAsync(sql);
#else
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
                await context.Database.ExecuteSqlCommandAsync(sql);

                sql = $"ALTER TABLE {tempTableName} ADD COLUMN __index integer";
                await context.Database.ExecuteSqlCommandAsync(sql);

#pragma warning restore EF1000 // Possible SQL injection vulnerability.
#endif

#if NETSTANDARD1_5 || NETSTANDARD2_0 || NETSTANDARD2_1
                SetAutoGeneratedFields(list, mapping, codeBuilder);
#endif

                // 2. Import into temp table
                using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForInsertQueryPart}, __index) FROM STDIN (FORMAT BINARY)"))
                {
                    var index = 1;
                    foreach (var item in list)
                    {
                        importer.StartRow();
                        codeBuilder.ClientDataWriterAction(item, importer);
                        importer.Write(index, NpgsqlDbType.Integer);
                        index++;
                    }
                    importer.Complete();
                }

                // 3. Insert into real table from temp one
                foreach (var insertPart in mapping.InsertQueryParts)
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        var baseInsertCmd = $"INSERT INTO {insertPart.TableNameQualified} ({insertPart.TargetColumnNamesQueryPart}) " +
                                            $"SELECT {insertPart.SourceColumnNamesQueryPart} FROM {tempTableName} ORDER BY __index {ignoreDuplicatesStatement}";

                        if (string.IsNullOrEmpty(insertPart.ReturningSetQueryPart))
                        {
                            cmd.CommandText = baseInsertCmd;

                            if (!string.IsNullOrEmpty(insertPart.Returning))
                            {
                                cmd.CommandText  = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
                                cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
                                cmd.CommandText += $"SELECT * FROM source ORDER BY __index";
                            }
                        }
                        else
                        {
                            cmd.CommandText  = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
                            cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
                            cmd.CommandText += $"UPDATE {tempTableName} SET {insertPart.ReturningSetQueryPart} FROM source WHERE {tempTableName}.__index = source.__index\n";
                            cmd.CommandText += $"RETURNING {insertPart.Returning}";
                        }

                        using (var reader = (NpgsqlDataReader)(await cmd.ExecuteReaderAsync()))
                        {
                            // 4. Propagate computed value
                            if (!string.IsNullOrEmpty(insertPart.Returning))
                            {
                                if (onConflict == null)
                                {
                                    var readAction = codeBuilder.IdentityValuesWriterActions[insertPart.TableName];
                                    foreach (var item in list)
                                    {
                                        await reader.ReadAsync();

                                        readAction(item, reader);
                                    }
                                }
                                else
                                {
                                    while (await reader.ReadAsync())
                                    {
                                        // do nothing, for now...
                                    }
                                }
                            }
                        }
                    }
                }

                // 5. Commit
                transaction?.Commit();
            }
            catch
            {
                try
                {
                    transaction?.Rollback();
                }
                catch { }

                throw;
            }
            finally
            {
                if (connOpenedHere)
                {
                    conn.Close();
                }
            }
        }
예제 #5
0
        public void Insert <T>(IEnumerable <T> entities)
        {
            var conn = NpgsqlHelper.GetNpgsqlConnection(context);

            EnsureConnected(conn);
            var transaction = NpgsqlHelper.EnsureOrStartTransaction(context);

            var mapping = GetEntityInfo <T>();

            try
            {
                // 0. Prepare variables
                var tempTableName = "_temp_" + DateTime.Now.Ticks;
                var list          = entities.ToList();
                var codeBuilder   = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;

                // 1. Create temp table
                var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
                //var sql = $"CREATE {tempTableName} AS {mapping.SelectSourceForInsertQuery} LIMIT 0";

                context.Database.ExecuteSqlCommand(sql);
                context.Database.ExecuteSqlCommand($"ALTER TABLE {tempTableName} ADD COLUMN __index integer");

                // 2. Import into temp table
                using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForInsertQueryPart}, __index) FROM STDIN (FORMAT BINARY)"))
                {
                    var index = 1;
                    foreach (var item in list)
                    {
                        importer.StartRow();
                        codeBuilder.ClientDataWriterAction(item, importer);
                        importer.Write(index, NpgsqlDbType.Integer);
                        index++;
                    }
                }

                // 3. Insert into real table from temp one
                foreach (var insertPart in mapping.InsertQueryParts)
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        var baseInsertCmd = $"INSERT INTO {insertPart.TableName} ({insertPart.TargetColumnNamesQueryPart}) SELECT {insertPart.SourceColumnNamesQueryPart} FROM {tempTableName}";
                        if (string.IsNullOrEmpty(insertPart.ReturningSetQueryPart))
                        {
                            cmd.CommandText = baseInsertCmd;
                            if (!string.IsNullOrEmpty(insertPart.Returning))
                            {
                                cmd.CommandText += $" RETURNING {insertPart.Returning}";
                            }
                        }
                        else
                        {
                            cmd.CommandText  = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
                            cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
                            cmd.CommandText += $"UPDATE {tempTableName} SET {insertPart.ReturningSetQueryPart} FROM source WHERE {tempTableName}.__index = source.__index\n";
                            cmd.CommandText += $" RETURNING {insertPart.Returning}";
                        }

                        using (var reader = cmd.ExecuteReader())
                        {
                            // 4. Propagate computed value
                            if (!string.IsNullOrEmpty(insertPart.Returning))
                            {
                                var readAction = codeBuilder.IdentityValuesWriterActions[insertPart.TableName];
                                foreach (var item in list)
                                {
                                    reader.Read();
                                    readAction(item, reader);
                                }
                            }
                        }
                    }
                }

                // 5. Commit
                transaction?.Commit();
            }
            catch
            {
                transaction?.Rollback();
                throw;
            }
        }
예제 #6
0
        public static List <T> BulkSelect <T, TKey>(
            this IQueryable <T> source,
            Expression <Func <T, TKey> > keyExpression,
            IEnumerable <TKey> keyData)
        {
            EnsureNoNavigationProperties(keyExpression);

            BulkSelectInterceptor.StartInterception();

            var keyDataTable = $"_schema_{DateTime.Now.Ticks}";
            var schemaQuery  = source.Select(keyExpression);
            var schemaSql    = $"CREATE TEMP TABLE {keyDataTable} ON COMMIT DROP AS ({schemaQuery} LIMIT 0)";

            var context = NpgsqlHelper.GetContextFromQuery(source);
            var conn    = NpgsqlHelper.GetNpgsqlConnection(context);


            var localTr = NpgsqlHelper.EnsureOrStartTransaction(context);

            try
            {
                context.Database.ExecuteSqlCommand(schemaSql);
                var columnsInfo = NpgsqlHelper.GetColumnsInfo(context, keyDataTable);

                var propsMap = GetPropertiesMap(
                    ((IObjectContextAdapter)context).ObjectContext,
                    schemaQuery.Expression,
                    typeof(TKey));

                var mapsInfo = new List <MappingInfo>();
                foreach (var propMap in propsMap)
                {
                    var cinfo = columnsInfo[propMap.Item2];
                    mapsInfo.Add(new MappingInfo()
                    {
                        Property   = propMap.Item1,
                        ColumnInfo = cinfo,
                        NpgsqlType = NpgsqlBulkUploader.GetNpgsqlType(cinfo)
                    });
                }

                var columnsCsv = string.Join(", ",
                                             mapsInfo.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

                var copySql = $"COPY {keyDataTable} ({columnsCsv}) FROM STDIN (FORMAT BINARY)";
                using (var importer = conn.BeginBinaryImport(copySql))
                {
                    foreach (var kd in keyData)
                    {
                        importer.StartRow();
                        foreach (var kp in mapsInfo)
                        {
                            importer.Write(kp.Property.GetValue(kd), kp.NpgsqlType);
                        }
                    }
                }

                var whereSql = string.Join(" AND ",
                                           mapsInfo.Select(x => $"source.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}" +
                                                           $" = {keyDataTable}.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}"));

                var selectSql = $"SELECT source.* FROM ({source}) as source\n" +
                                $"JOIN {keyDataTable} ON {whereSql}";

                BulkSelectInterceptor.SetReplaceQuery(source.ToString(), selectSql);

                var result = source.ToList();

                localTr?.Commit();

                return(result);
            }
            catch
            {
                localTr?.Rollback();
                throw;
            }
            finally
            {
                BulkSelectInterceptor.StopInterception();
            }
        }