Exemple #1
0
        internal List <MappingInfo> ConvertFragmentToMapping(
            DbContext context,
            Type type,
            MappingFragment mappingFragment,
            EntityType entityType)
        {
            var tableEntitySet = mappingFragment.StoreEntitySet;
            var tableName      = (tableEntitySet.MetadataProperties["Table"].Value ?? tableEntitySet.Name).ToString();

            var columnsInfo = GetColumnsInfo(context, tableName);
            var innerList   = mappingFragment.PropertyMappings
                              .OfType <ScalarPropertyMapping>()
                              .Select(x =>
            {
                var columnInfo = columnsInfo.FirstOrDefault(c => c.ColumnName == x.Column.Name);
                if (columnInfo == null)
                {
                    throw new InvalidOperationException(
                        $"Column '{x.Column.Name}' (mapped to: '{x.Property.DeclaringType.Name}.{x.Property.Name}') is not found");
                }
                var isDbGenerated = x.Column.IsStoreGeneratedComputed || x.Column.IsStoreGeneratedIdentity;

                return(new MappingInfo()
                {
                    TableName = tableName,
                    TableNameQualified = NpgsqlHelper.GetQualifiedName(tableName, tableEntitySet.Schema),
                    Property = type.GetProperty(x.Property.Name,
                                                BindingFlags.NonPublic | BindingFlags.Public |
                                                BindingFlags.GetProperty | BindingFlags.Instance),
                    ColumnInfo = columnInfo,
                    IsKey = entityType.KeyProperties.Any(y => y.Name == x.Property.Name),
                    DoUpdate = !isDbGenerated,
                    DoInsert = !isDbGenerated
                });
            }).ToList();

            return(innerList);
        }
Exemple #2
0
        internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
        {
            var metadata   = context.Model;
            var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);

            var tableName   = GetTableName(context, type);
            var columnsInfo = GetColumnsInfo(context, tableName);

            if (entityType.BaseType != null)
            {
                var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
                if (baseTableName != tableName)
                {
                    var extraColumnsInfo = GetColumnsInfo(context, baseTableName);
                    columnsInfo.AddRange(extraColumnsInfo);
                }
            }

            var innerList = entityType.GetProperties()
                            .Where(x => x.PropertyInfo != null)
                            .Select(x =>
            {
                var relational = x.DeclaringEntityType.Relational();
                return(new MappingInfo()
                {
                    TableName = relational.TableName,
                    TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.TableName, relational.Schema),
                    Property = x.PropertyInfo,
                    ColumnInfo = columnsInfo.First(c => c.ColumnName == x.Relational().ColumnName),
                    IsDbGenerated = x.ValueGenerated != ValueGenerated.Never,
                    IsKey = x.IsKey(),
                    IsInheritanceUsed = entityType.BaseType != null
                });
            }).ToList();

            return(innerList);
        }
        private List <MappingInfo> GetMappingInfo(Type type, string tableName)
        {
            var mappings = NpgsqlHelper.GetMetadata(context, type);

            mappings.ForEach(x =>
            {
                var sourceAttribute = x.Property?.GetCustomAttribute <BulkMappingSourceAttribute>();
                var modifiers       = x.Property?.GetCustomAttributes <BulkOperationModifierAttribute>();

                x.ModifierAttributes    = modifiers?.ToList() ?? new List <BulkOperationModifierAttribute>();
                x.OverrideSourceMethod  = GetOverrideSouceFunc(type, sourceAttribute?.PropertyName);
                x.NpgsqlType            = GetNpgsqlType(x.ColumnInfo);
                x.TempAliasedColumnName = $"{x.TableName}_{x.ColumnInfo.ColumnName}".ToLower();
                x.QualifiedColumnName   = $"{NpgsqlHelper.GetQualifiedName(x.TableName)}.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}";
            });
            return(mappings);
        }
        private EntityInfo CreateEntityInfo <T>(
            string tableName,
            string tableNameQualified,
            List <MappingInfo> mappingInfo,
            NpgsqlBulkCodeBuilder <T> codeBuilderOuter = null)
        {
            var codeBuilder = codeBuilderOuter ?? new NpgsqlBulkCodeBuilder <T>();

            var info = new EntityInfo()
            {
                TableNameQualified            = tableNameQualified,
                TableName                     = tableName,
                CodeBuilder                   = codeBuilder,
                MappingInfos                  = mappingInfo,
                PropToMappingInfo             = mappingInfo.Where(x => x.Property != null).ToDictionary(x => x.Property.Name),
                TableNames                    = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
                InsertClientDataInfos         = mappingInfo.Where(x => x.DoInsert).ToArray(),
                UpdateClientDataWithKeysInfos = mappingInfo.Where(x => x.DoUpdate || x.IsKey).ToArray(),
                MaxIsOptionalFlag             = mappingInfo.Max(x => x.IsSpecifiedFlag)
            };

#if EFCore
            info.PropertyToGenerators = mappingInfo
                                        .Where(x => x.LocalGenerator != null)
                                        .ToDictionary(x => x.DbProperty, x => x.LocalGenerator);
            info.PropertyNameToGenerators = info.PropertyToGenerators.ToDictionary(x => x.Key.Name, x => x.Value);
#endif

            var tableNames = mappingInfo.Select(x => x.TableNameQualified).Distinct().ToList();
            //var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
            //    .Select(x => new
            //    {
            //        TableName = x.Key,
            //        x.First().TableNameQualified,
            //        KeyInfos = x.Where(y => y.IsKey).ToList(),
            //        ClientDataInfos = x.Where(y => y.DoInsert).ToList(),
            //        ReturningInfos = x.Where(y => y.ReadBack).ToList()
            //    })
            //    .ToList();

            info.InsertQueryParts    = new ConcurrentDictionary <long, List <InsertQueryParts> >();
            info.InsertQueryParts[0] = GetInsertQueryParts(mappingInfo, 0);

            //info.InsertQueryParts = grouppedByTables.Select(x =>
            //{
            //    var others = grouppedByTables.Where(y => y.TableName != x.TableName)
            //        .SelectMany(y => y.ClientDataInfos)
            //        .Select(y => new
            //        {
            //            My = y,
            //            Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
            //        })
            //        .Where(y => y.Others != null)
            //        .ToList();

            //    return new InsertQueryParts()
            //    {
            //        TableName = x.TableName,
            //        TableNameQualified = x.TableNameQualified,
            //        TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
            //        SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
            //        Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
            //        ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
            //            $" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
            //    };
            //}).ToList();

            info.SelectSourceForInsertQuery = "SELECT " +
                                              string.Join(", ", info.InsertClientDataInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", tableNames);
            info.CopyColumnsForInsertQueryPart = string.Join(", ", info.InsertClientDataInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.InsertDbGeneratedInfos = info.MappingInfos.Where(x => x.ReadBack && x.Property != null).ToArray();

            // Now time for updates
            var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
                                   .Select(x => new
            {
                TableName = x.Key,
                x.First().TableNameQualified,
                KeyInfos        = x.Where(y => y.IsKey).ToList(),
                ClientDataInfos = x.Where(y => y.DoUpdate).ToList(),
                ReturningInfos  = x.Where(y => y.ReadBack).ToList()
            })
                                   .ToList();

            info.UpdateQueryParts = grouppedByTables.Select(x =>
            {
                var updateableInfos = x.ClientDataInfos;
                updateableInfos     = updateableInfos
                                      .Where(y => y.DoUpdate)
                                      .Where(y => y.ModifierAttributes == null || y.ModifierAttributes.All(m => m.Modification != BulkOperationModification.IgnoreForUpdate))
                                      .ToList();

                return(new UpdateQueryParts()
                {
                    TableName = x.TableName,
                    TableNameQualified = x.TableNameQualified,
                    SetClause = string.Join(", ", updateableInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        return $"{colName} = source.{y.TempAliasedColumnName}";
                    })),
                    WhereClause = string.Join(" AND ", x.KeyInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        var clause = $"{colName} = source.{y.TempAliasedColumnName}";

                        if (y.IsNullableInClr)
                        {
                            clause = $"({clause} OR ({colName} IS NULL AND source.{y.TempAliasedColumnName} IS NULL))";
                        }

                        return clause;
                    }))
                });
            })
                                    .Where(x => !string.IsNullOrEmpty(x.SetClause))
                                    .ToList();

            info.SelectSourceForUpdateQuery = "SELECT " +
                                              string.Join(", ", info.UpdateClientDataWithKeysInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
            info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.UpdateClientDataWithKeysInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.UpdateDbGeneratedInfos = info.MappingInfos.Where(x => x.ReadBack && x.Property != null).ToArray();

            // Rest info
            info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();

            info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();

            if (codeBuilderOuter == null)
            {
                codeBuilder.InitBuilder(info, ReadValue);
            }

            return(info);
        }
        private List <InsertQueryParts> GetInsertQueryParts(
            List <MappingInfo> mappingInfo,
            long optionalFlags)
        {
            var grouppedByTables = mappingInfo
                                   .GroupBy(x => x.TableName)
                                   .Select(x => new
            {
                TableName = x.Key,
                x.First().TableNameQualified,
                KeyInfos        = x.Where(y => y.IsKey).ToList(),
                ClientDataInfos = x.Where(y => y.DoInsert &&
                                          (y.IsSpecifiedFlag == 0 || (y.IsSpecifiedFlag & optionalFlags) > 0)).ToList(),
                ReturningInfos = x.Where(y => y.ReadBack).ToList()
            })
                                   .ToList();

            return(grouppedByTables.Select(x =>
            {
                var others = grouppedByTables.Where(y => y.TableName != x.TableName)
                             .SelectMany(y => y.ClientDataInfos)
                             .Select(y => new
                {
                    My = y,
                    Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
                })
                             .Where(y => y.Others != null)
                             .ToList();

                return new InsertQueryParts()
                {
                    TableName = x.TableName,
                    TableNameQualified = x.TableNameQualified,
                    TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
                    SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
                    Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
                    ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
                                                                            $" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
                };
            }).ToList());
        }
Exemple #6
0
        internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
        {
            var metadata   = context.Model;
            var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);

            var tableName   = GetTableName(context, type);
            var columnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, tableName);

            if (entityType.BaseType != null)
            {
                var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
                if (baseTableName != tableName)
                {
                    var extraColumnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, baseTableName);
                    columnsInfo.AddRange(extraColumnsInfo);
                }
            }

            int optinalIndex = 0;
            var innerList    = entityType.GetProperties()
                               .Select(x =>
            {
                var relational = x.DeclaringEntityType;
                ValueGenerator localGenerator = null;
                bool isDbGenerated            = false;

                var generatorFactory = x.GetAnnotations().FirstOrDefault(a => a.Name == "ValueGeneratorFactory");
                if (generatorFactory != null)
                {
                    var valueGeneratorAccessor = generatorFactory.Value as Func <IProperty, IEntityType, ValueGenerator>;
                    localGenerator             = valueGeneratorAccessor(x, x.DeclaringEntityType);
                }
                else if (x.GetAnnotations().Any(y => y.Name == "Relational:ComputedColumnSql"))
                {
                    isDbGenerated = true;
                }
                else if (x.GetAnnotations().Any(y => y.Name == "Npgsql:ValueGenerationStrategy"))
                {
                    isDbGenerated = true;
                }

                var indexes       = ((Microsoft.EntityFrameworkCore.Metadata.Internal.Property)x).PropertyIndexes;
                long optionalFlag = 0;
                if (indexes.StoreGenerationIndex >= 0 && localGenerator == null)
                {
                    optionalFlag = 1 << optinalIndex;
                    optinalIndex++;
                }

                return(new MappingInfo()
                {
                    TableName = relational.GetTableName(),
                    TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.GetTableName(), relational.GetSchema()),
                    Property = x.PropertyInfo,
                    ColumnInfo = columnsInfo.First(c => c.ColumnName == x.GetColumnName()),
                    LocalGenerator = localGenerator,
                    ValueConverter = x.GetValueConverter(),
                    IsKey = x.IsKey(),
                    IsInheritanceUsed = entityType.BaseType != null,
                    DbProperty = x,
                    DoUpdate = !isDbGenerated && x.PropertyInfo != null,        // don't update shadow props
                    DoInsert = !isDbGenerated,                                  // do insert of shadow props
                    ReadBack = indexes.StoreGenerationIndex >= 0,
                    IsSpecifiedFlag = optionalFlag
                });
            }).ToList();

            return(innerList);
        }
 private List <ColumnInfo> GetTableSchema(string tableName)
 {
     return(NpgsqlHelper.GetColumnsInfo(context, tableName));
 }
        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();
                }
            }
        }
        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();
                }
            }
        }
        private EntityInfo CreateEntityInfo <T>(
            string tableName,
            string tableNameQualified,
            List <MappingInfo> mappingInfo,
            NpgsqlBulkCodeBuilder <T> codeBuilderOuter = null)
        {
            var codeBuilder = codeBuilderOuter ?? new NpgsqlBulkCodeBuilder <T>();

            var info = new EntityInfo()
            {
                TableNameQualified      = tableNameQualified,
                TableName               = tableName,
                CodeBuilder             = codeBuilder,
                MappingInfos            = mappingInfo,
                PropToMappingInfo       = mappingInfo.ToDictionary(x => x.Property),
                TableNames              = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
                ClientDataInfos         = mappingInfo.Where(x => !x.IsDbGenerated).ToArray(),
                ClientDataWithKeysInfos = mappingInfo.Where(x => !x.IsDbGenerated || x.IsKey).ToArray()
            };

#if NETSTANDARD1_5 || NETSTANDARD2_0 || NETSTANDARD2_1
            info.PropertyToGenerators     = mappingInfo.Where(x => x.LocalGenerator != null).ToDictionary(x => x.Property, x => x.LocalGenerator);
            info.PropertyNameToGenerators = info.PropertyToGenerators.ToDictionary(x => x.Key.Name, x => x.Value);
#endif

            var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
                                   .Select(x => new
            {
                TableName = x.Key,
                x.First().TableNameQualified,
                KeyInfos        = x.Where(y => y.IsKey).ToList(),
                ClientDataInfos = x.Where(y => !y.IsDbGenerated).ToList(),
                ReturningInfos  = x.Where(y => y.IsDbGenerated).ToList()
            })
                                   .ToList();

            info.InsertQueryParts = grouppedByTables.Select(x =>
            {
                var others = grouppedByTables.Where(y => y.TableName != x.TableName)
                             .SelectMany(y => y.ClientDataInfos)
                             .Select(y => new
                {
                    My     = y,
                    Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
                })
                             .Where(y => y.Others != null)
                             .ToList();

                return(new InsertQueryParts()
                {
                    TableName = x.TableName,
                    TableNameQualified = x.TableNameQualified,
                    TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
                    SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
                    Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
                    ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
                                                                            $" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
                });
            }).ToList();

            info.SelectSourceForInsertQuery = "SELECT " +
                                              string.Join(", ", info.ClientDataInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
            info.CopyColumnsForInsertQueryPart = string.Join(", ", info.ClientDataInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.UpdateQueryParts = grouppedByTables.Select(x =>
            {
                var updateableInfos = x.ClientDataInfos;
                updateableInfos     = updateableInfos.Where(
                    y => y.ModifierAttributes == null ||
                    y.ModifierAttributes.All(
                        m => m.Modification != BulkOperationModification.IgnoreForUpdate)
                    ).ToList();

                return(new UpdateQueryParts()
                {
                    TableName = x.TableName,
                    TableNameQualified = x.TableNameQualified,
                    SetClause = string.Join(", ", updateableInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        return $"{colName} = source.{y.TempAliasedColumnName}";
                    })),
                    WhereClause = string.Join(" AND ", x.KeyInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        var clause = $"{colName} = source.{y.TempAliasedColumnName}";

                        if (y.IsNullableInClr)
                        {
                            clause = $"({clause} OR ({colName} IS NULL AND source.{y.TempAliasedColumnName} IS NULL))";
                        }

                        return clause;
                    }))
                });
            })
                                    .Where(x => !string.IsNullOrEmpty(x.SetClause))
                                    .ToList();

            info.SelectSourceForUpdateQuery = "SELECT " +
                                              string.Join(", ", info.ClientDataWithKeysInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
            info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.ClientDataWithKeysInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.ClientDataColumnNames = string.Join(", ",
                                                     info.ClientDataInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();

            info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();

            info.ClientDataWithKeysColumnNames = string.Join(", ",
                                                             info.ClientDataWithKeysInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            info.DbGeneratedInfos = info.MappingInfos.Where(x => x.IsDbGenerated).ToArray();

            info.DbGeneratedColumnNames = string.Join(", ",
                                                      info.DbGeneratedInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            if (codeBuilderOuter == null)
            {
                codeBuilder.InitBuilder(info, ReadValue);
            }

            return(info);
        }
        private string GetTableNameQualified(Type t)
        {
            var tableAttr = t.GetCustomAttribute <TableAttribute>();

            return(NpgsqlHelper.GetQualifiedName(tableAttr.Name, tableAttr.Schema));
        }
        private EntityInfo CreateEntityInfo <T>()
        {
            var t           = typeof(T);
            var tableName   = GetTableName(t);
            var mappingInfo = GetMappingInfo(t, tableName);
            var codeBuilder = new NpgsqlBulkCodeBuilder <T>();

            var info = new EntityInfo()
            {
                TableNameQualified      = GetTableNameQualified(t),
                TableName               = tableName,
                CodeBuilder             = codeBuilder,
                MappingInfos            = mappingInfo,
                TableNames              = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
                ClientDataInfos         = mappingInfo.Where(x => !x.IsDbGenerated).ToArray(),
                ClientDataWithKeysInfos = mappingInfo.Where(x => !x.IsDbGenerated || x.IsKey).ToArray()
            };

            var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
                                   .Select(x => new
            {
                TableName       = x.Key,
                KeyInfos        = x.Where(y => y.IsKey).ToList(),
                ClientDataInfos = x.Where(y => !y.IsDbGenerated).ToList(),
                ReturningInfos  = x.Where(y => y.IsDbGenerated).ToList()
            }).ToList();

            info.InsertQueryParts = grouppedByTables.Select(x =>
            {
                var others = grouppedByTables.Where(y => y.TableName != x.TableName)
                             .SelectMany(y => y.ClientDataInfos)
                             .Select(y => new
                {
                    My     = y,
                    Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
                })
                             .Where(y => y.Others != null)
                             .ToList();

                return(new InsertQueryParts()
                {
                    TableName = x.TableName,
                    TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => y.ColumnInfo.ColumnName)),
                    SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => y.TempAliasedColumnName)),
                    Returning = string.Join(", ", x.ReturningInfos.Select(y => y.ColumnInfo.ColumnName)),
                    ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{y.My.TempAliasedColumnName} = source.{y.Others.ColumnInfo.ColumnName}"))
                });
            }).ToList();

            info.SelectSourceForInsertQuery = "SELECT " +
                                              string.Join(", ", info.ClientDataInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableName));
            info.CopyColumnsForInsertQueryPart = string.Join(", ", info.ClientDataInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.UpdateQueryParts = grouppedByTables.Select(x =>
            {
                var updateableInfos = x.ClientDataInfos;
                updateableInfos     = updateableInfos.Where(
                    y => y.ModifierAttributes == null ||
                    y.ModifierAttributes.All(
                        m => m.Modification != BulkOperationModification.IgnoreForUpdate)
                    ).ToList();

                return(new UpdateQueryParts()
                {
                    TableName = x.TableName,
                    SetClause = string.Join(", ", updateableInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        return $"{colName} = source.{y.TempAliasedColumnName}";
                    })),
                    WhereClause = string.Join(", ", x.KeyInfos.Select(y =>
                    {
                        var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
                        return $"{colName} = source.{y.TempAliasedColumnName}";
                    }))
                });
            }).ToList();

            info.SelectSourceForUpdateQuery = "SELECT " +
                                              string.Join(", ", info.ClientDataWithKeysInfos
                                                          .Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
                                              " FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableName));
            info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.ClientDataWithKeysInfos
                                                             .Select(x => x.TempAliasedColumnName));

            info.ClientDataColumnNames = string.Join(", ",
                                                     info.ClientDataInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();

            info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();

            info.ClientDataWithKeysColumnNames = string.Join(", ",
                                                             info.ClientDataWithKeysInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            info.DbGeneratedInfos = info.MappingInfos.Where(x => x.IsDbGenerated).ToArray();

            info.DbGeneratedColumnNames = string.Join(", ",
                                                      info.DbGeneratedInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));

            codeBuilder.InitBuilder(info.ClientDataInfos,
                                    info.ClientDataWithKeysInfos,
                                    info.DbGeneratedInfos,
                                    ReadValue);

            return(info);
        }
        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;
            }
        }
        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();
            }
        }
        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);
                    }
                    importer.Complete();
                }

                // 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
            {
                try
                {
                    transaction?.Rollback();
                }
                catch { }

                throw;
            }
            finally
            {
                if (connOpenedHere)
                {
                    conn.Close();
                }
            }
        }
Exemple #16
0
        internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
        {
            var metadata   = context.Model;
            var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);

            var tableName   = GetTableName(context, type);
            var columnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, tableName);

            if (entityType.BaseType != null)
            {
                var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
                if (baseTableName != tableName)
                {
                    var extraColumnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, baseTableName);
                    columnsInfo.AddRange(extraColumnsInfo);
                }
            }

            var valueGenSelector = ((IInfrastructure <IServiceProvider>)context).Instance.GetService <IValueGeneratorSelector>();

            int optinalIndex = 0;
            var innerList    = entityType
                               .GetProperties()
                               .Where(x => x.GetColumnName() != "xmin") // For now we don't support xmin
                               .Select(x =>
            {
                var relational = x.DeclaringEntityType;
                ValueGenerator localGenerator = null;
                bool isDbGenerated            = false;

                var generatorFactory = x.GetAnnotations().FirstOrDefault(a => a.Name == "ValueGeneratorFactory");
                if (generatorFactory != null)
                {
                    var valueGeneratorAccessor = generatorFactory.Value as Func <IProperty, IEntityType, ValueGenerator>;
                    localGenerator             = valueGeneratorAccessor(x, x.DeclaringEntityType);
                }
                else if (x.GetAnnotations().Any(y => y.Name == "Relational:ComputedColumnSql"))
                {
                    isDbGenerated = true;
                }
                else
                {
                    var autoGenStrategy = x.GetAnnotations().FirstOrDefault(y => y.Name == "Npgsql:ValueGenerationStrategy");
                    if (autoGenStrategy != null)
                    {
                        var npgsqlStartegy = (NpgsqlValueGenerationStrategy)autoGenStrategy.Value;
                        isDbGenerated      = npgsqlStartegy != NpgsqlValueGenerationStrategy.SequenceHiLo &&
                                             npgsqlStartegy != NpgsqlValueGenerationStrategy.None;
                    }
                }

                if (!isDbGenerated && localGenerator == null)
                {
                    try
                    {
                        localGenerator = valueGenSelector.Select(x, entityType);
                    }
                    catch
                    {
                        // ignore
                    }
                }

#if DotNet6
                var readBack = x.GetStoreGeneratedIndex() >= 0;
#else
                var indexes  = ((Microsoft.EntityFrameworkCore.Metadata.Internal.Property)x).PropertyIndexes;
                var readBack = indexes.StoreGenerationIndex >= 0;
#endif

                long optionalFlag = 0;

                // We don't support genertion based on Foreign Keys.
                if (readBack && !x.IsForeignKey() && localGenerator == null)
                {
                    optionalFlag = 1 << optinalIndex;
                    optinalIndex++;
                }

                return(new MappingInfo()
                {
                    TableName = relational.GetTableName(),
                    TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.GetTableName(), relational.GetSchema()),
                    Property = x.PropertyInfo,
                    ColumnInfo = columnsInfo.First(c => c.ColumnName == x.GetColumnName()),
                    LocalGenerator = localGenerator,
                    ValueConverter = x.GetValueConverter(),
                    IsKey = x.IsKey(),
                    IsInheritanceUsed = entityType.BaseType != null,
                    DbProperty = x,
                    DoUpdate = !isDbGenerated && x.PropertyInfo != null,        // don't update shadow props
                    DoInsert = !isDbGenerated,                                  // do insert of shadow props
                    ReadBack = readBack,
                    IsSpecifiedFlag = optionalFlag
                });
            }).ToList();

            return(innerList);
        }