Can expression in WhenMatched use both existing record and an "insert" dataset?

[dmitriifilonenko]

Hi again Artiom,

I want to add just a little bit sophisticated logic in updating existing record. My expression in WhenMatched compares existing value with a new one and makes a decision:

dataContext.CompanyUsers
                    .Upsert(new CompanyUser(companyDto.Id, userDto.Id, userDto.Position))
                    .On(u => new {u.UserId, u.CompanyId})
                    .WhenMatched(inDb =>
                    new CompanyUser
                    {
                        Positions = inDb.Positions.Contains(userDto.Position) ? inDb.Positions : userDto.Position
                    })
                    .Run();

But this fails with UnsupportedExpressionException.

The following variation of the same fails too:

dataContext.CompanyUsers
                    .Upsert(new CompanyUser(companyDto.Id, userDto.Id, userDto.Position))
                    .On(u => new {u.UserId, u.CompanyId})
                    .WhenMatched((inDb, newOne) =>
                    new CompanyUser
                    {
                        Positions = inDb.Positions.Contains(newOne.Positions) ? inDb.Positions : newOne.Positions
                    })
                    .Run();

Out of curiosity I played a bit with it. But even some simple operations like
Positions = inDb.Positions + ','+ userDto.Position fail.
Is there any work-around for this?
Thank you!

[artiomchi]

That's... interesting..

I haven't ignored it, I'm still trying to look into this :)

[APIWT]

@artiomchi maybe what you could do is have a way to register custom expressions that would be implemented on a per database driver basis. Then there could be an overload of WhenMatched that gives each of the custom expression functions in an IEnumerable.

So an example would be:

dataContext.RegisterUpsertWhenMatchedExpression<SqlServerWhenMatchedExpression>("appendValue",
    (tableName, columnName, existingValueReference, newValueReference, args) =>
    $"CASE WHEN {existingValueReference} LIKE ('%' + {newValueReference} + '%') THEN {existingValueReference} ELSE ({existingValueReference} + ',' + {newValueReference}) END");

dataContext.CompanyUsers.
    Upsert(new CompanyUser(companyDto.Id, userDto.Id, userDto.Position))
    .On(u => new {u.UserId, u.CompanyId})
    .WhenMatched((inDb, newOne, registeredExpressions) =>
        new CompanyUser
        {
            Positions = registeredExpressions
                                    .Single((expression) => expression.Name == "appendValue")
                                    .Apply<string>(), // The type here allows it to be compatible with the type Positions expects
        })
    .Run();

If this doesn't make sense let me know and I am happy to elaborate.

[artiomchi]

Sorry it took so long, but I'm back and made a new release. I can't easily add support for all functions (i.e. inDb.Positions.Contains(userDto.Position)), but I rewrote a big chunk of how I'm parsing expression trees, allowing for much more complex expressions, so your second options should work (Positions = inDb.Positions + ','+ userDto.Position)

Let me know if this is satisfactory, and if this issue can be closed

@APIWT Extensibility support is a great idea, and would be a fun thing to come up with on how to implement it.. I'll keep it in mind and open a new issue for it

[dmitriifilonenko]

Hi Artiom,

Thanks a lot for your efforts! I will do my best to try this ASAP. Though can't really promise since I'm quite loaded currently with other stuff. But it's itching to try! :-D

[artiomchi]

I'll be closing this issue since there's been no updates in some time. If you have any other issues, please let me know