Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bulk insert 9.0.1 does not insert all columns #1700

Open
thomDesan opened this issue Mar 7, 2025 · 0 comments
Open

Bulk insert 9.0.1 does not insert all columns #1700

thomDesan opened this issue Mar 7, 2025 · 0 comments

Comments

@thomDesan
Copy link

thomDesan commented Mar 7, 2025

Bulk insert create a wrong sql merge statement (was working with version 8)
See generated SQL where in the insert statement the data column is missing

MERGE [dbo].[Locations] WITH (HOLDLOCK) AS T USING 
(SELECT TOP 1 * FROM [dbo].[LocationsTemp8d9864dc] ORDER BY [ExternalId]) AS S ON T.[ExternalId] = S.[ExternalId] 
	WHEN NOT MATCHED BY TARGET 
	THEN INSERT ([ConcernId], [CreatedDate], [DeletedDate], [ExternalId], [ModificationDate]) 
	VALUES (S.[ConcernId], S.[CreatedDate], S.[DeletedDate], S.[ExternalId], S.[ModificationDate]) 
	WHEN MATCHED AND EXISTS (SELECT S.[ConcernId], S.[CreatedDate], S.[Data], S.[DeletedDate], S.[ExternalId], S.[ModificationDate] EXCEPT SELECT T.[ConcernId], T.[CreatedDate], T.[Data], T.[DeletedDate], T.[ExternalId], T.[ModificationDate]) 
	THEN UPDATE SET 
		T.[ConcernId] = S.[ConcernId], 
		T.[Data] = S.[Data], 
		T.[DeletedDate] = S.[DeletedDate], 
		T.[ExternalId] = S.[ExternalId], 
		T.[ModificationDate] = S.[ModificationDate];
go

c# code

var bulkConfig = new BulkConfig
{
    UpdateByProperties = [nameof(Location.ExternalId)],
    PropertiesToExcludeOnUpdate = [nameof(Location.CreatedDate), nameof(Concern.Id)]
};
await SetExternalIds(model);
await ctx.BulkInsertOrUpdateAsync(model, bulkConfig);

table

    public int Id { get; set; }
    public Concern Concern { get; set; } = null!;
    public int ConcernId { get; set; }
    public LocationData Data { get; set; } = new();
    public DateTime CreatedDate { get; set; }
    public DateTime ModificationDate { get; set; }
    public DateTime? DeletedDate { get; set; }
    public ICollection<LocationStartCode> StartCodes { get; set; } = new List<LocationStartCode>();
    public int ExternalId { get; set; }

Where LocationData is een json string
See config

    public void Configure(EntityTypeBuilder<Location> builder)
    {
        builder.HasIndex(x => x.ExternalId).IsUnique();
        builder.Property(e => e.Data).HasConversion(
            v => JsonSerializer.Serialize(v, JsonSerializerOptions),
            v => JsonSerializer.Deserialize<LocationData>(v, (JsonSerializerOptions?)null)!); 
        builder.Property(x => x.Data).Metadata.SetValueComparerJson<LocationData>();
        builder.Property(x => x.Data).HasDefaultValueSql("'{}'");
    }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant