Return Id of newly upserted row

[akraines]

How can I retrieve the Id of the upserted row? Something like this: https://stackoverflow.com/a/31578591/5683904

[artiomchi]

Hi @akraines,

That's definitely a good feature to have. I've been having some thoughts on this, and I'm planning to add this in a future release, most likely in v2.1

[schmitch]

Actually it would be good if the whole thing can be returned (i.e. in Npgsql you can have Returning *)

[PeteOnTheGitHub]

Is this feature still planned?

[jlucaspains]

I might be able to help with this as I am looking for the same solution. I was checking the code to see what it takes to implement and this is what I came up with:

1. In the UpsertCommandBuilder, create a new method WithResults that define the format the results need to be returned on:

public UpsertCommandBuilder<TEntity> WithResults(Expression<Func<TEntity, TEntity, TEntity>> resultDefinition)
        {
            if (_resultExpression != null)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.CantCallMethodTwice, nameof(WithResults)));

            _resultExpression = resultDefinition ?? throw new ArgumentNullException(nameof(resultDefinition));
            return this;
        }

2. Change the int Run and Task RunAsync methods is probably not a good idea so I would create 2 new methods:

// note that they return a collection of TEntity to account for multiple records affected
IEnumerable<TEntity> RunWithResults();
Task<IEnumerable<TEntity>> RunWithResultsAsync(CancellationToken token = default)

3. New overload methods should be created in the IUpsertCommandRunner implementations. This is similar to UpsertCommandBuilder.

4. Update the PrepareCommand and GenerateCommand methods for each implementation to build the output clause. I can easily do the SQL Server version, but might need help with the others.

@artiomchi this is an extra simplistic description of the changes, but I don't want to start anything without knowing the direction is right.

Cheers, Lucas

[kzeitz]

What is the status of this request/issue? Seems like the requirement is to return the ID of the new or existing record is important to the upsert pattern. Is there a correct way to do this? I don't see my model entities receive the IDs after running dbcontext.upsert().

[crazyfx1]

This library would be perfect if it has this feature (return the existing / new row). Unfortunately without it it's not usable :(

[radenkozec]

@crazyfx1 Could not agree more. Without returning the existing/new row this is not usable.

[owns]

I was lucky enough to be upserting with a client-generated guid, so a simple select gets me the new row back. Not ideal, but definitely working.

[AntonSmolkov]

@artiomchi Hi! Is it still planned?

[kbzowski]

+1

[vamsidogiparthi]

I might be able to help with this as I am looking for the same solution. I was checking the code to see what it takes to implement and this is what I came up with:

1. In the UpsertCommandBuilder, create a new method WithResults that define the format the results need to be returned on:

public UpsertCommandBuilder<TEntity> WithResults(Expression<Func<TEntity, TEntity, TEntity>> resultDefinition)
        {
            if (_resultExpression != null)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.CantCallMethodTwice, nameof(WithResults)));

            _resultExpression = resultDefinition ?? throw new ArgumentNullException(nameof(resultDefinition));
            return this;
        }

2. Change the int Run and Task RunAsync methods is probably not a good idea so I would create 2 new methods:

// note that they return a collection of TEntity to account for multiple records affected
IEnumerable<TEntity> RunWithResults();
Task<IEnumerable<TEntity>> RunWithResultsAsync(CancellationToken token = default)

3. New overload methods should be created in the IUpsertCommandRunner implementations. This is similar to UpsertCommandBuilder.
4. Update the PrepareCommand and GenerateCommand methods for each implementation to build the output clause. I can easily do the SQL Server version, but might need help with the others.

@artiomchi this is an extra simplistic description of the changes, but I don't want to start anything without knowing the direction is right.

Cheers, Lucas

I think along with you might need to update GetCommandRunner to include a select statement at the end of the merge statement to return both the inserted or updated rows after the merge is complete for these two new methods. Or allow to pass the runners as a config value. So that we can create our own instance of IUpsertCommandRunner. This might not be as simple of as adding two new methods. Needs some more to it.