Exclude properties from insert and update in BulkInsertOrUpdateOrDelete method

[akordowski]

I have an object which contains the properties CreatedAt and UpdatedAt. I would like to exclude the UpdatedAt property on insert and the CreatedAt property at update. As I don't know if the object already exists, both properties are set with a datetime. On executing the BulkInsertOrUpdateOrDelete method I'm getting an Microsoft.Data.SqlClient.SqlException with the message Invalid column name "UpdatedAt".

This is my configuration:

_dbContext.BulkInsertOrUpdateOrDeleteAsync(
    items,
    options =>
    {
        options.PropertiesToExclude = new List<string> { nameof(Item.UpdatedAt), nameof(Item.DeletedAt) };
        options.PropertiesToIncludeOnUpdate = new List<string> { nameof(Item.UpdatedAt) };
        options.EnableShadowProperties = true;
    });

public class Item
{
    public int Id { get; set; }
    public string? Text { get; set; }
    public DateTimeOffset? CreatedAt { get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
    public bool Deleted { get; set; }
}

This is the generated sql statement:

MERGE [dbo].[Item] WITH (HOLDLOCK) AS T USING
(
    SELECT TOP 1 *
    FROM [dbo].[ItemTempf9f7258f]
    ORDER BY [Id]
)

AS S ON T.[Id] = S.[Id] WHEN NOT MATCHED BY TARGET THEN

INSERT ([CreatedAt], [Deleted], [Text])
VALUES (S.[CreatedAt], S.[Deleted], S.[Text])

WHEN MATCHED AND EXISTS

(
    SELECT S.[CreatedAt], S.[Deleted], S.[Text]
    EXCEPT
    SELECT T.[CreatedAt], T.[Deleted], T.[Text]
)

THEN

UPDATE SET T.[UpdatedAt] = S.[UpdatedAt];

WHEN NOT MATCHED BY SOURCE THEN DELETE;

What am I doing wrong?
Any help is much appreciated!

[akordowski]

I tried it also with this configuration:

_dbContext.BulkInsertOrUpdateOrDeleteAsync(
    items,
    options =>
    {
        options.PropertiesToExclude = new List<string> { nameof(Item.UpdatedAt), nameof(Item.DeletedAt) };
        options.PropertiesToExcludeOnUpdate = new List<string> { nameof(Item.CreatedAt), nameof(Item.DeletedAt) };
        options.EnableShadowProperties = true;
    });

This generated the following sql statement:

MERGE [dbo].[Item] WITH (HOLDLOCK) AS T USING
(
    SELECT TOP 1 *
    FROM [dbo].[ItemTempc6b85062]
    ORDER BY [Id]
)

AS S ON T.[Id] = S.[Id] WHEN NOT MATCHED BY TARGET THEN

INSERT ([Text], [CreatedAt], [Deleted])
VALUES (S.[Text], S.[CreatedAt], S.[Deleted])

WHEN MATCHED AND EXISTS

(
    SELECT S.[Text], S.[CreatedAt], S.[Deleted]
    EXCEPT
    SELECT T.[Text], T.[CreatedAt], T.[Deleted]
)

THEN

UPDATE SET
    T.[Text] = S.[Text],
    T.[UpdatedAt] = S.[UpdatedAt],
    T.[Deleted] = S.[Deleted];

WHEN NOT MATCHED BY SOURCE THEN DELETE;

But I'm still getting the Microsoft.Data.SqlClient.SqlException with the message Invalid column name "UpdatedAt".

[yelhsawm]

I am pretty sure I have the exact same scenario/requirement. I have a Created and Updated column but I want to exclude the Updated from the original INSERT.

It feels like we need the equivalent of PropertiesToExcludeOnUpdate for the Insert. Something like PropertiesToExcludeOnInsert?