artiomchi / flexlabs.upsert Goto Github PK

FlexLabs.Upsert is a library that brings UPSERT functionality to common database providers for Entity Framework in their respective native SQL syntax

License: MIT License

C# 99.68% Batchfile 0.32%

flexlabs.upsert's People

Contributors

Stargazers

Watchers

Forkers

drive-revenue longhronshen pratoservices gangstatracer frankchen021 marcoronline obea apiwt pfrozi gyulad kowalot konstwise cri5ti beta2k gevorg2 dystudio merifondnewmarkets bbrandt markchipman chadbh digitalplatform symbiogenesis y-cherdantsev aicun andrewsilver grante egorpavlikhin bsmith-bt vladimirlapacek jlucaspains kolky romangolovanov adremes-gmbh-co-kg frontlinersnl marazattila gorragid novichikhinalexey myjetwallet hflexgrig andrewlock quinntynebrown estaldo esowers rlightner orifjonmelibayev rsr-maersk ababilone msawczyn fulviocanducci perrycodes am-creations tsf9810292 onderakdemir mcigor alex6djforkedrepos ibrahim-elsakka quentez cnaught-inc mookid8000 red1408192 jivkopiskov oliwerxi vamsidogiparthi fredatgithub anhgeeky kation kevin-mueller daghb thinkgandhi pengweiqhca bohirjon habeebj arleahmi allreadyexists dadyarri kkamikadzee khiemtong

flexlabs.upsert's Issues

Upsert is replacing my previous entity

I've an entity class like this one:

public class Device
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public string DeviceId { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.None), JsonIgnore]
        public string GroupId { get; set; }

        public string CustomName { get; set; }
        public string Tags { get; set; }
        public string Location { get; set; }

        public string DeviceInfo { get; set; }
        public string LastCmd { get; set; }

        public bool? Online { get; set; }

        public Device() { /* Constructor needed for query */ }

        public Device(string groupId, string deviceId)
        {
            GroupId = groupId;
            DeviceId = deviceId;
        }
    }

And then on various method on my applications creating a new device with a parameter changed, like..

            var entity = new Device(groupId,/deviceId)
            {
                CustomName = name
            };

And inserting it using upsert with the main idea of doing a merge between the new value and the database current one:

var db = _contextFactory.GetContext();
await db.Upsert(deviceEntity).RunAsync();

But what's happening is the new entity is replacing the previous one on database, instead of changing the field that's new.

What I'm doing wrong?

Reloading DbContext for updated entity

Hey @artiomchi!

I am having some trouble with stale data sticking around in the DbContext, and I was hoping you could maybe help me. Take the following example:

public class User
{
    public long Id { get; set; }

    public IEnumerable<Pet> Pets { get; set; }
}

public class Pet
{
    public long Id { get; set; }

    public long UserId { get; set; }

    public User User { get; set; }

    public uint Age { get; set; }
}

and imagine the following code:

// pretend that before this line the user that we are about to load has a pet with the age of 5
// load the user from the database
var user = await dbContext.User.Include(u => u.Pets).FirstOfDefaultAsync(u => u.Id == 123);

// the pet is now 7, just for an example
var somePet = user.Pet.FirstOrDefault();
somePet.Age = 7;

await this.context.Pet.Upsert(somePet)
    .On(v => new { p.Id })
    .WhenMatched(existingPet => new Pet
    {
        Age = somePet.Age,
    })
    .RunAsync();

// some time later, get the user back from the database / context
var updatedUser = await dbContext.User.Include(u => u.Pets).FirstOrDefaultAsync(u => u.Id == 123);

var samePet = updatedUser.Pet.FirstOrDefault();

Console.WriteLine(samePet.Age); // this line should print out 7 but it prints out 5 instead, which is the age of the pet from before upserting

I am able to work around the issue by calling await dbContext.Entry(somePet).ReloadAsync() right after the Upsert call.

Is this functionality intended? Is there a recommended pattern for avoiding this type of issue?

Thanks as always for your help!

Extensibility support - adding custom expressions

Suggesion by @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();

Proposed feature add: .UpdateAllColumns()

I propose the addition of the following:

            await context.Set<Thing>()
                .Upsert(thing)
                .On(x => x.SomeColumn)
                .UpdateAllColumns()
                .RunAsync();

It is unfortunate, especially for wide tables, to need to go and update the contents of .WhenMatched(...) whenever the column layout changes.

Issue with 'hstore' type in PostgreSQL

In my PostgreSQL DB I have a table with a column of 'hstore' type. This column is projected to Dictionary type in C#. Here is how thic column described in code:

public class Product
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }

        public Dictionary<string,string> Properties { get; set; }

        public DateTime Created { get; set; }
        public DateTime Updated { get; set; }
}

public class DataContext : DbContext
{
        public DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.HasPostgresExtension("hstore");

            modelBuilder.Entity<Product>()
                .Property(p => p.Properties)
                .HasColumnType("hstore");
   
            ...
          }
}

Here is how I'm trying to upsert a record to this table:

serviceDataContext.Products.Upsert(new Product
                                    {
                                        Id = backendProduct.Id,
                                        Properties = backendProduct.Properties,
                                        Created = backendProduct.CreatedAt,
                                        Updated = backendProduct.UpdatedAt
                                    })
                                    .On(product => product.Id)
                                    .WhenMatched((productInDb, productNew) =>
                                        new Product
                                        {
                                            Properties = backendProduct.Properties,
                                            Updated = backendProduct.UpdatedAt
                                        })
                                    .Run();

At this stage I get an exception:

Npgsql.NpgsqlException (0x80004005): The NpgsqlDbType 'Hstore' isn't present in your database. You may need to install an extension or upgrade to a newer version.
at Npgsql.TypeMapping.ConnectorTypeMapper.GetByNpgsqlDbType(NpgsqlDbType npgsqlDbType) in C:\projects\npgsql\src\Npgsql\TypeMapping\ConnectorTypeMapper.cs:line 106
at Npgsql.NpgsqlParameter.ResolveHandler(ConnectorTypeMapper typeMapper) in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 523
at Npgsql.NpgsqlCommand.ValidateParameters() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 796
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1140
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable1 parameters) at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.Run[TEntity](DbContext dbContext, IEntityType entityType, ICollection1 entities, Expression1 matchExpression, Expression1 updateExpression, Boolean noUpdate, Boolean useExpressionCompiler) in D:\Development\FlexLabs.Upsert\src\FlexLabs.EntityFrameworkCore.Upsert\Runners\RelationalUpsertCommandRunner.cs:line 234
at FlexLabs.EntityFrameworkCore.Upsert.UpsertCommandBuilder`1.Run() in D:\Development\FlexLabs.Upsert\src\FlexLabs.EntityFrameworkCore.Upsert\UpsertCommandBuilder.cs:line 144

As an experiment I replaced an upsert with standard 'Add' functionality from EF Core. Then everything works fine:

serviceDataContext.Products.Add(new Product
                {
                    Id = backendProduct.Id,
                    Properties = backendProduct.Properties,
                    Created = backendProduct.CreatedAt,
                    Updated = backendProduct.UpdatedAt
                });

So for me it looks likes the knowldege of data context about 'hstore' type is lost somewhere in between.

Handling of jsonb in Postgres

When using with Postgres, I get the following:
42804: column xxx is of type jsonb but expression is of type text

Support for IQueryable<TEntity> as data source for UpsertRange<TEntity>?

I am faced with a scenario where it would be super desireable to be able to pass an IQueryable<TEntity> to UpsertRange<TEntity> instead of an IEnumerable<TEntity> or TEntity[].

This should then result in the UPSERT being run entirely on the DB side as opposed to loading entities into memory. I.e.:

var someSourceEntitiesProjectionToDailyVisits = dataContext.SomeSourceEntities
    .Where(x => x.SomeSourceEntityProperty == someValue)
    .Select(x => new {
        UserID = x.SomeFieldMatchedToUserId,
        Date = DateTime.UtcNow.Date
    });

dataContext.DailyVisits
    .UpsertRange(someSourceEntitiesProjectionToDailyVisits)
    .On(v => new { v.UserID, v.Date })
    .WhenMatched(v => new DailyVisit
    {
        Visits = v.Visits + 1,
    })
    .RunAsync();

Is something like this on the roadmap?

[Proposal] API redesign discussion

API design proposal, as mentioned in PR #15
This would make the API designs more semetically pretty.

dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdate((matched, newItem) => new Item()
    {
        Field = newItem.Field,
		Visit = matched.Visit + 1
    });
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdateAsync((matched, newItem) => new Item()
    {
        Field = newItem.Field,
		Visit = matched.Visit + 1
    });
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdate();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdateAsync();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoNothing();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoNothingAsync();

// Infer the match fields to detected primary keys or alternative keys,
// this would make OnConflict funcional call optional
dbContext.Items.UpsertRange(newItems);
dbContext.Items.UpsertRangeAsync(newItems);

@artiomchi

Exception when updating DateTime? Field

First off, awesome library!

I'm trying to set my UpdatedAt timestamp using the WhenMatched() call. Here is my code:

_context.Earnings.Upsert(new Earnings
{
    EmployeeId = employee.Id,
    ContributionGroupId = employee.CurrentEmploymentRecord.ContributionGroupId,
    JobPosition = employee.CurrentEmploymentRecord.JobPosition,
    PositionStatus = employee.CurrentEmploymentRecord.PositionStatus,
    PaymentReason = employee.CurrentEmploymentRecord.PaymentReason,
    ImportType = ImportType.MonthEnd,
    PostingDate = new DateTime(UploadEarnings.Year, UploadEarnings.Month, 1),
    GrossEarnings = earning.Amount.GetValueOrDefault(),
    CreatedAt = DateTime.Now,
})
.On(c => new { c.EmployeeId, c.ContributionGroupId, c.JobPosition, c.PositionStatus, c.PaymentReason, c.ImportType, c.PostingDate })
.WhenMatched(c => new Earnings
{
    UpdatedAt = DateTime.Now,
    GrossEarnings = earning.Amount.GetValueOrDefault()
})
.Run();

I receive the following exception when trying to set the UpdatedAt value:

The method or operation is not implemented.

If I omit UpdatedAt or try to set CreatedAt in WhenMatched() I do not get the exception. The only difference between the two is that CreatedAt is a DateTime type and UpdatedAt is DateTime?. Also, If I set UpdatedAt in the Upsert() call, I do not get the exception. I'm a newbie, so maybe I'm missing something?

Auto-Generable KEY is not included into "AS" and "INSERT" part of query

Hello.
I'm using the version 2.0.4 from Nuget.

My object have a complex key, with two columns:
OrderNumber and OrderChannel

The second one is not included into AS and into INSERT query, and an exception will be thrown from SQL.

The reason is that "OrderChannel" have an auto-generated default value.
The problem is this line of code:

var properties = entityType.GetProperties()
                .Where(p => p.ValueGenerated == ValueGenerated.Never)
                .ToArray();

Here the wrong generated query:

MERGE INTO [DhlReportData] WITH (HOLDLOCK) AS [T] USING ( VALUES (@p0, @p1, @p2, @p3, @P4, @p5, @p6, @P7, @p8, @p9, @p10, @p11, @p12) )

AS [S] ([OrderNumber], [BillToAccount], [CompanyName], [LicensePlateNo], [OriginStation], [PickupResponse], [Pieces], [PltIndicator], [Product], [ShipmentContents], [ShipmentNumber], [ShipmentResponse], [Weight])

ON [T].[OrderNumber] = [S].[OrderNumber] AND [T].[OrderChannel] = [S].[OrderChannel]

WHEN NOT MATCHED BY TARGET THEN INSERT ([OrderNumber], [BillToAccount], [CompanyName], [LicensePlateNo], [OriginStation], [PickupResponse], [Pieces], [PltIndicator], [Product], [ShipmentContents], [ShipmentNumber], [ShipmentResponse], [Weight]) VALUES ([OrderNumber], [BillToAccount], [CompanyName], [LicensePlateNo], [OriginStation], [PickupResponse], [Pieces], [PltIndicator], [Product], [ShipmentContents], [ShipmentNumber], [ShipmentResponse], [Weight])

WHEN MATCHED THEN UPDATE SET [BillToAccount] = [S].[BillToAccount], [CompanyName] = [S].[CompanyName], [LicensePlateNo] = [S].[LicensePlateNo], [OriginStation] = [S].[OriginStation], [PickupResponse] = [S].[PickupResponse], [Pieces] = [S].[Pieces], [PltIndicator] = [S].[PltIndicator], [Product] = [S].[Product], [ShipmentContents] = [S].[ShipmentContents], [ShipmentNumber] = [S].[ShipmentNumber], [ShipmentResponse] = [S].[ShipmentResponse], [Weight] = [S].[Weight];

This is my fix, but I'm not sure that is correct in all scenarios:

var properties = entityType.GetProperties()
                .Where(p => p.ValueGenerated == ValueGenerated.Never || p.IsKey())
                .ToArray();

Navication Properties Throw Exceptions

I was not sure how best to describe the issue so I forked the code, created a new branch to exercise the code the same way I was in my project that was throwing the exception.

Take a look at the file NavigationPropertyTests.cs in my branch
https://github.com/CraigSelbert/FlexLabs.Upsert/tree/bug/NavigationProperty
for an example of the issue. If I have time I will see if I can fix but I figured I would at lease show you what I am experiencing.

Exception when passing an empty collection to UpsertRange(...)

When an empty collection is passed to .UpsertRange(...), an InvalidOperationException is thrown. The .First() call here is at the top of the stack trace:

https://github.com/artiomchi/FlexLabs.Upsert/blob/2.0.3/src/FlexLabs.EntityFrameworkCore.Upsert/Runners/PostgreSqlUpsertCommandRunner.cs#L28

Excluding columns when updating

Hi, thanks for creating the library!

We'd like to use to upsert rows based on secondary IDs. If the secondary ID is matched, we want to update all columns except the PK. We tried this:

ctx.Upsert(newItem)
	.On(i => new { i.SecondaryID1, i.SecondaryID2 })
	.UpdateColumns(existingItem => new Item
	{
		ID = existingItem.ID, // We want to preserve the original ID
		Field1 = default(string),
		Field2 = default(string)
	})

Unfortunately this throws System.Reflection.TargetException: 'Object does not match target type.' in ExpressionHelper.cs:25

Could you please advise if we're doing it wrong or this is not supported? Thanks!

Upserting a List

Unless I am being dumb, this only works one entity at a time and I have a List of entities to Upsert.

Is that possible?

Many thanks

Transactional support

Hey @artiomchi!

I have a question. What is your recommendation in using one or more upserts in a transaction?
https://docs.microsoft.com/en-us/ef/core/saving/transactions

We have a couple instances of mixing typical Add/Update calls with Upsert and we want it to rollback if any of them fail.

I do understand that it might not be possible to modify this library to use SaveChangesAsync rather than RunAsync, but is there a workaround you can suggest?

Hope you are having a great day!

fails to insert into SQLite

i have a completely empty database/table (sqlite) and upsert fails to insert the first object.

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (11ms) [Parameters=[@p2='?' (Size = 5), @p0='?' (Size = 14), @p1='?' (Size = 5)], CommandType='Text', CommandTimeout='30']
      UPDATE "Activities" SET "Description" = @p0, "RoleModelName" = @p1
      WHERE "ID" = @p2;
      SELECT changes();
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'GIA.WebServices.Settings.Models.PermissionContext'.
      Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagation(Int32 commandIndex, RelationalDataReader reader)
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
         at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagation(Int32 commandIndex, RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
fail: GIA.WebServices.Settings.Logic.SettingsMigration[0]
      Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
fail: GIA.WebServices.Settings.Logic.SettingsMigration[0]
      settings migration failed

CI Build Failed & NuGet Certificate Error

The ci build is failing because of certificate error:
https://ci.appveyor.com/project/artiomchi/flexlabs-upsert

Also I could not install the nuget package because of certificate error:

NU3012: Package 'FlexLabs.EntityFrameworkCore.Upsert 2.0.6' from source 'https://api.nuget.org/v3/index.json': The author primary signature found a chain building issue: Das Zertifikat wurde gesperrt.
Package restore failed. Rolling back package changes for 'Crawler'.
...
[Notification][Install] Install failed (project: Crawler, package: FlexLabs.EntityFrameworkCore.Upsert v2.0.6)
Package restore failed. Rolling back package changes for 'Crawler'.

Add support for constructor expressions in WhenMatched

Currently it's implicitely allowed to create objects in WhenMatched method by using only MemberInitExpression which causes an exception in such case:

dataContext.Users.UpsertRange(users)
          .On(u => u.Id)
          .WhenMatched((uInDb, uNew) =>
                    new User(uInDb.Id, uNew.Username, uNew.FirstName, uNew.LastName, uInDb.Created));

The exception comes from this code in RelationalUpsertCommandRunner.PrepareCommand method:

                if (!(updater.Body is MemberInitExpression entityUpdater))
                    throw new ArgumentException("updater must be an Initialiser of the TEntity type", nameof(updater));

I'm not sure but maybe it will be enough just to add a check if updater.Body is NewExpression:

                if (!(updater.Body is MemberInitExpression) && !(updater.Body is NewExpression))
                    throw new ArgumentException("updater must be an Initialiser of the TEntity type");

I would prefer to have initialization of an object in a constructor in order to keep its properties as readonly, without setters. Now in order to make Upsert work I have to expose properties' setters in entities which makes their contract too broad.

Return Id of newly upserted row

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

Does not support netstandard2.0

Trying to install on a netstandard2.0 project:

Package FlexLabs.EntityFrameworkCore.Upsert 1.0.1 is not compatible with netstandard2.0 (.NETStandard,Version=v2.0). Package FlexLabs.EntityFrameworkCore.Upsert 1.0.1 supports: netcoreapp2.0 (.NETCoreApp,Version=v2.0)

Maybe you can target netstandard2.0 instead of netcoreapp2.0?

Thanks

'match must be an anonymous object initialiser'

Hi, thanks for this helpful library.
I'm encountering a problem when using a string field as the "On" parameter.
How can I fix this please.

Here's the code I'm writing:

public class Settings
{
public int Id { get; set; }
public string Name { get; set; }
public string Type { get; set; }
public string Value { get; set; }
public string Comment { get; set; }
}

db.Upsert(new Settings { Name = "BaseUrl", Type = "String", Value = "1", Comment = "1" })
.On(c => c.Name).RunAsync();

Upsert with condition?

Would it be possible to implement an upserts with a condition like "upsert if date is newer"?
I only want to update my entity if the entity that I received is newer.
Or is this already possible?

Adding support for EntityFramework (non core)

To do this, I'll probably extract the core of the engine in a "Core" project, and have a separate NuGet package for the non core EF support

Support for InMemoryDatabase provider

Seems like I forgot to add support for the in memory database provider for EF, which would be quite useful for testing!

I might have to create a separate generator, since the in memory database wouldn't be able to "run a sql query"

Thanks to Lance for pointing this out

3.0 support

I used in 3.0 but got an exception

System.MissingMethodException: Method not found: 'Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior Microsoft.EntityFrameworkCore.Metadata.IProperty.get_AfterSaveBehavior()'.
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.<>c__14`1.<PrepareCommand>b__14_1(IProperty p)
   at System.Linq.Utilities.<>c__DisplayClass1_0`1.<CombinePredicates>b__0(TSource x)
   at System.Linq.Enumerable.WhereEnumerableIterator`1.ToArray()
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.PrepareCommand[TEntity](IEntityType entityType, ICollection`1 entities, Expression`1 match, Expression`1 updater, Expression`1 updateCondition, Boolean noUpdate, Boolean useExpressionCompiler)
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.RunAsync[TEntity](DbContext dbContext, IEntityType entityType, ICollection`1 entities, Expression`1 matchExpression, Expression`1 updateExpression, Expression`1 updateCondition, Boolean noUpdate, Boolean useExpressionCompiler, CancellationToken cancellationToken)

InMemory provider leaves entities in ChangeTracker

The InMemory provider puts entities into the change tracker, so that code that works on a real database fails when using the InMemory provider. For example:

await context.Upsert(new Thing { Key = "a" })
   .On(x => x.Key)
  .RunAsync();

context.Update(new Thing { Key = "a", OtherCol = "b" });
await context.SaveChangesAsync();

Using a real database this works fine. On the InMemory database, this blows up with System.InvalidOperationException : The instance of entity type 'Thing' cannot be tracked because another instance with the same key value for {'Key'} is already being tracked.

Syntax error with Postgres when omitting `WhenMatched(...)`

I'm getting a syntax error from postgres when doing an upsert. The SQL it generates is of the form

INSERT INTO "TableName" AS "T" ("ColumnA", "ColumnB") VALUES (@p0, @p1) ON CONFLICT ("ColumnA", "ColumnB") DO UPDATE SET

It seems that there's an assumption that the WhenMatched(...) expression will get translated to SQL and inserted following the SET, but I'm using the library like this:

DataContext.TableNames
    .Upsert(new TableName
    {
        ColumnA = val1,
        ColumnB = val2
    })
    .On(v => new { v.UserID, v.Date })
    .RunAsync();

based on some code-surfing, it seems that the design of the UpsertCommandBuilder<T> makes assumptions that calls to .On(...) will be followed by calls to .WhenMatched(...). This isn't obvious to the programmer, and won't blow up until runtime. In my case, my automated tests against the in-memory store also worked as expected, so it passed my CI build.

In my humble opinion, a short-term solution might be to default _noUpdate to true and set it to false when .WhenMatched(...) is called. This all depends on the intention of the design. In any case, though, it should be impossible (i.e. a compiler error) to call .Run() or .RunAsync() if the generated SQL would be invalid.

I'd also be glad to help you work through the fluent API design details in the longer term if you'd like.
It just so happens I wrote a Pluralsight course about this very topic - there are some concepts there that might be helpful.

When properties are defined in the model but not found on the entity to upsert, we get a NullReferenceException

Hi,
In our model, we inject properties that are not found on the entity we are upserting but that we want to populate automatically at execution time for Audit purposes.

We do something similar to this:
Our entity:

public class Synchronization
    {
        public string Id { get; set; }
        public DateTime LastSyncedAt { get; set; }
        public DateTime UpdatedAt { get; set; }
    }

Our Upsert statement looks like this:

            await _dbContext.Synchronizations.Upsert(s).On(c => c.Id).WhenMatched((db, on) => new Synchronization
            {
                LastSyncedAt = on.LastSyncedAt
            }).RunAsync(ct);

In the Model configuration inside OnModelCreating:

        private void ModelSynchronization(ModelBuilder builder)
        {
            var model = builder.Entity<Synchronization>();
            model.ToTable("Synchronization");
            model.HasKey(s => s.Id);
            model.Property(c => c.Id).ValueGeneratedNever();
            model.ConfigureAuditProperties();
        }

Using this extension ConfigureAuditProperties method, we automatically update the audit columns (which are not found on the entity to persist):

public static class AuditExtensions
    {
        private static readonly string AuditCreatedAtPropertyName = "AuditCreatedAt";
        private static readonly string AuditLastUpdatedAtPropertyName = "AuditLastUpdatedAt";

        public static void ConfigureAuditProperties<T>(this EntityTypeBuilder<T> model) where T : class
        {
            model.Property<DateTime?>(AuditCreatedAtPropertyName);
            model.Property<DateTime?>(AuditLastUpdatedAtPropertyName);
        }

        public static void AuditEntityChanges(this BiDbContext context)
        {
            context.ChangeTracker.Entries()
                .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified)
                .ForEach(e => AuditChangesFor(e));
        }

        private static void AuditChangesFor(EntityEntry tracked)
        {
            var now = DateTime.Now;

            var entityType = tracked.Context.Model.FindEntityType(tracked.Entity.GetType());
            if (entityType == null)
            {
                return;
            }

            var createdAtProperty = entityType.FindProperty(AuditCreatedAtPropertyName);
            var lastUpdatedAtProperty = entityType.FindProperty(AuditLastUpdatedAtPropertyName);

            if (tracked.State == EntityState.Added)
            {
                if (createdAtProperty != null)
                {
                    tracked.Property(AuditCreatedAtPropertyName).CurrentValue = now;
                }
                if (lastUpdatedAtProperty != null)
                {
                    tracked.Property(AuditLastUpdatedAtPropertyName).CurrentValue = now;
                }
            }
            else if (tracked.State == EntityState.Modified)
            {
                if (lastUpdatedAtProperty != null)
                {
                    tracked.Property(AuditLastUpdatedAtPropertyName).CurrentValue = now;
                }
            }
        }
    }

Executing the Upsert will generate the following exception, because the property from the model that is being searched doesn't actually exists on the entity type:

System.NullReferenceException
  HResult=0x80004003
  Message=Object reference not set to an instance of an object.
  Source=FlexLabs.EntityFrameworkCore.Upsert
  StackTrace:
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.<>c__DisplayClass13_2`1.<PrepareCommand>b__7(IProperty p)
   at System.Linq.Enumerable.SelectArrayIterator`2.ToArray()
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.<>c__DisplayClass13_0`1.<PrepareCommand>b__2(TEntity e)
   at System.Linq.Enumerable.SelectArrayIterator`2.ToArray()
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.PrepareCommand[TEntity](IEntityType entityType, ICollection`1 entities, Expression`1 match, Expression`1 updater, Boolean noUpdate, Boolean useExpressionCompiler)
   at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.<RunAsync>d__18`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at SynchronizationRepository.<Upsert>d__2.MoveNext() in C:\src\SynchronizationRepository.cs:line 21

Code that throws:

 var newEntities = entities
                .Select(e => properties
                    .Select(p =>
                    {
                        var columnName = p.Relational().ColumnName;
                        var value = new ConstantValue(p.PropertyInfo.GetValue(e), p); // BUG: PropertyInfo is null here because it's not on the entity
                        return (columnName, value);
                    })
                    .ToArray() as ICollection<(string ColumnName, ConstantValue Value)>)
                .ToArray();

Thanks!

Key property excluded from SQL

When you have an entity with a Guid key, that is not set to get its value from the DB, Upsert still excludes it because EF sets the ValueGenerated property to OnAdd, so the following line:

if (prop.ValueGenerated != ValueGenerated.Never) continue;

excludes the key and I get a SQL error saying the key column doesn't accept nulls. The key is configured as such:

class Entity
{
	public Guid Id { get; set; }
	public string Name { get; set; }
}

class MyContext : DbContext
{
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder
			.Entity<Entity>()
			.HasKey(x => x.Id);
	}
}

No idea why EF specifies it this way, but the model works when inserting with EF normally (i.e. uses the value on the entity, rather than generating it on the DB)

SqlServerUpsertCommandRunner (and possibly others) do not support null column matchers

When using SQL Server, if you have a column that allows null as a value in the On matcher, the search condition produced in the query does not properly check null and causes the query to fail. This is because when you are checking for null in most relational databases you should use IS NULL or IS NOT NULL rather than the equality operator.

The following pull request should fix this issue: #41

EF uses it's own service collection that we're not populating

The AddUpsertCommandGenerator extension method is trying to populate the global service collection, but EF is using it's own service collection internally, so this method ends up doing nothing useful.

Thanks for Lance for pointing out an issue that led me to this

Overriding SaveChanges()

I add timestamps and modified by on SaveChanges() override. Is this possible?

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

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!

API design proposal

Proposal by @LonghronShen
This would make the API designs more semetically pretty.

dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdate((matched, newItem) => new Item()
    {
        Field = newItem.Field,
		Visit = matched.Visit + 1
    });
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdateAsync((matched, newItem) => new Item()
    {
        Field = newItem.Field,
		Visit = matched.Visit + 1
    });
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdate();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoUpdateAsync();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoNothing();
dbContext.Items.UpsertRange(newItems)
    .OnConflict(x => x.IdentitcalField)
    .DoNothingAsync();

// Infer the match fields to detected primary keys or alternative keys,
// this would make OnConflict funcional call optional
dbContext.Items.UpsertRange(newItems);
dbContext.Items.UpsertRangeAsync(newItems);

version 2.x DateTime.Now Throw Exceptions

for example:

db.Agreements.Upsert(new Agreement{ 
         Id=1,
         Name="bill",
         CreatedOn= DateTime.Now,
         ModifiedOn = DateTime.Now
}).on(x=>new{ x.Id})
.WhenMatched( x=>new Agreement{   Id=1,
         ModifiedOn = DateTime.Now})
.RunAsync();

System.NullReferenceException:"Object reference not set to an instance of an object"

Extend the expression parser

Add optional configuration option to enable generic expression compilation, and create a page with instructions on how to handle/report new expressions that aren't handled right now

Properly handle cases when using identity columns for Upsert join

Need to throw an exception when passing identity columns in the .On() call, or when not calling .On() at all on tables with an identity column

Handle the new type conversion introduced in EF core 2.1

When utilizing the upsert command it does not recognize type conversions added through fluent API.

modelBuilder.Entity<Counter>().Property(c => c.Discriminator).HasConversion(d => JsonConvert.SerializeObject(d), s => JsonConvert.DeserializeObject<Dictionary<string, string>>(s));

Throwing exception for property when HasDefaultValue specified, but no value is passed

    public class Schedule
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }

        public ScheduleStatus Status { get; set; }

        //other stuff
    }

    public enum ScheduleStatus
    {
        Test,
        Pilot,
        Prod
    }

And DataContext is configured this way:

    public class DataContext : DbContext
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Schedule>()
                .Property(schedule => schedule.Status)
                .HasConversion(new EnumToStringConverter<ScheduleStatus>())
                .HasDefaultValue(ScheduleStatus.Test);
           
             modelBuilder.Entity<Schedule>()
             .Property(e => Schedule.SalesChannel)
                    .IsRequired()
                    .HasColumnName("sales_channel")
                    .HasMaxLength(10)
                    .HasDefaultValueSql("(N'B')");
           //other stuff
        }
}

Then I upsert entites this way:

var scheduleStatus = CalculateStatus(someData);
serviceDataContext.Schedules.Upsert(new Schedule
                    {
                        Id = backendSchedule.Id,                        
                        Status = scheduleStatus,
                    })
                    .On(scenario => scenario.Id)
                    .WhenMatched((scenarioInDb, scenarioNew) =>
                        new Schedule
                        {
                            Status = scheduleStatus,
                        })
                    .Run();

Throwing null exception for SalesChannel should not be null. This is started after recent fix of Default value of column is not working now #52

IT is expecting pass all the values even it has Has HasDefaultValueSql("(N'B')"); set.

Update using Upsert fails to update values in Context in SQLite

Having taken the steps to enable SQLite support, the upsert command inserts a row when the old version exists, but fails to update the same row.
CallerHandlerTest.cs

using CtCloudUpload.Data.Context;
using CtCloudUpload.Data.Entities;
using CtCloudUpload.Domain.Files;
using CtCloudUpload.Domain.UploadHandlers;
using FluentAssertions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Xunit;
using System;
using System.IO;
using System.Net.NetworkInformation;
using Xunit;
using Xunit.Abstractions;

namespace CtCloudUpload.Domain.Tests.UploadHandlers
{
    public class CallerHandlerTest : IDisposable
    {
        private readonly ITestOutputHelper outputHelper;
        private MyContext Context { get; }
        private Hub Hub { get; }

        public CallerHandlerTest(ITestOutputHelper outputHelper)
        {
            this.outputHelper = outputHelper;

            var serviceProvider = new ServiceCollection()
                           .AddLogging(context => context.AddProvider(new XunitLoggerProvider(outputHelper)))
                           .BuildServiceProvider();

            var factory = serviceProvider.GetService<ILoggerFactory>();

            // If we are on Windows platform, we can copy Sqlite 3.24.0 binary to the output directory.
            // The dynamic libraries in the current execution path will load first.
            if (Environment.OSVersion.Platform == PlatformID.Win32NT && !File.Exists("sqlite3.dll"))
            {
                File.Copy(Environment.Is64BitProcess ? "sqlite3_x64.dll" : "sqlite3_x86.dll", "sqlite3.dll", true);
            }
            // Using the SQLitePCLRaw.provider.sqlite3.netstandard11 package
            // which loads the external sqlite3 standard dynamic library instead of the embeded old one.
            SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
            // Stop other packages from loading embeded sqlite3 library.
            SQLitePCL.raw.FreezeProvider();

            var options = new DbContextOptionsBuilder<MyContext>()
                .UseSqlite("DataSource=:memory:")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(factory)
                .Options;

            Context = new MyContext(options);

            Context.Database.OpenConnection();

            Context.Database.EnsureCreated();

            Context.Tenant.Add(new Tenant
            {
                Id = 123,
                Name = "test tenant"
            });

            Context.Site.Add(new Site
            {
                Id = 123,
                TenantId = 123,
                Name = "test site"
            });

            Context.HouseCode.Add(new HouseCode
            {
                SiteId = 123,
                HouseCodeId = 1
            });

            Hub = new Hub
            {
                SiteId = 123,
                Licence = "ABC",
                MacAddress = PhysicalAddress.Parse("00-11-22-33-44-55")
            };

            Context.Hub.Add(Hub);

            Context.SaveChanges();
        }

        public void Dispose()
        {
            Context.Database.CloseConnection();
            Context.Database.EnsureDeleted();
            Context.Dispose();
        }

        [Fact]
        public void Updated_caller_should_updated()
        {
            var original = new Caller
            {
                SiteId = 123,
                HouseCodeId = 1,
                CallerId = 1,
                Name = "Zone 1 > NICK TEST"
            };

            //Add matching caller
            Context.Caller.Add(original);

            Context.SaveChanges();

            var models = new MyFileModel[]
            {
                new MyFileModel
                {
                    Id = 1,
                    Date = new DateTime(2018, 10, 1, 10, 0, 0),
                    Response = null,
                    CallTypeId = 1,
                    CallType = "Call",
                    CallerName = "UPDATED",
                    Active = true,
                    CallerId = 1,
                    TagName = string.Empty,
                    ZoneId = 7,
                    TagId = null,
                    HouseCodeId = 1,
                    Notes = string.Empty,
                    PagerCode = string.Empty,
                    CallAccepted = null,
                    DayNightMode = "Day",
                    SignalStrength = 100,
                    TxnType = 1,
                    SdTransactionTimestamp = string.Empty,
                    CallAcceptedCount = 0,
                    UserName = string.Empty,
                    Scaled = false,
                    UnscaledCallerId = null
                }
            };

            CallerHandler.UpsertCallers(Context, Hub, models);

            Context.Caller.Should().ContainSingle()
                .Which.Name.Should().Be("UPDATED");
        }
    }
}

MyContext.cs

using CtCloudUpload.Data.Entities;
using Microsoft.EntityFrameworkCore;
using System;

namespace CtCloudUpload.Data.Context
{
    public partial class MyContext: DbContext
    {
        public MyContext(DbContextOptions<MyContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Caller> Caller { get; set; }
        public virtual DbSet<HouseCode> HouseCode { get; set; }
        public virtual DbSet<Hub> Hub { get; set; }
        public virtual DbSet<Site> Site { get; set; }
        public virtual DbSet<Tenant> Tenant { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Caller>(entity =>
            {
                entity.HasKey(e => new { e.SiteId, e.CallerId, e.HouseCodeId });

                entity.ToTable("caller");

                entity.Property(e => e.SiteId).HasColumnName("site_id");

                entity.Property(e => e.CallerId).HasColumnName("caller_id");

                entity.Property(e => e.HouseCodeId).HasColumnName("house_code_id");

                entity.Property(e => e.Name)
                    .HasColumnName("name")
                    .HasColumnType("character varying");

                entity.HasOne(d => d.Site)
                    .WithMany(p => p.Caller)
                    .HasForeignKey(d => d.SiteId)
                    .HasConstraintName("caller_site_id_fkey");

                entity.HasOne(d => d.HouseCode)
                    .WithMany(p => p.Caller)
                    .HasForeignKey(d => new { d.SiteId, d.HouseCodeId })
                    .HasConstraintName("caller_site_id_house_code_id_fkey");
            });

            modelBuilder.Entity<HouseCode>(entity =>
            {
                entity.HasKey(e => new { e.SiteId, e.HouseCodeId });

                entity.ToTable("house_code");

                entity.Property(e => e.SiteId).HasColumnName("site_id");

                entity.Property(e => e.HouseCodeId).HasColumnName("house_code_id");

                entity.HasOne(d => d.Site)
                    .WithMany(p => p.HouseCode)
                    .HasForeignKey(d => d.SiteId)
                    .HasConstraintName("house_code_site_id_fkey");
            });

            modelBuilder.Entity<Hub>(entity =>
            {
                entity.HasKey(e => e.MacAddress);

                entity.ToTable("hub");

                entity.HasIndex(e => e.Licence)
                    .HasName("hub_licence_key")
                    .IsUnique();

                var macAddressProperty = entity.Property(e => e.MacAddress)
                .HasColumnName("mac_address");

                if (!Database.IsNpgsql())
                {
                    macAddressProperty.HasConversion(
                        property => property.ToString(),
                        columnName => System.Net.NetworkInformation.PhysicalAddress.Parse(columnName)
                    );
                }

                entity.Property(e => e.Created)
                    .HasColumnName("created")
                    .HasDefaultValueSql("CURRENT_TIMESTAMP");

                entity.Property(e => e.Licence)
                    .IsRequired()
                    .HasColumnName("licence")
                    .HasColumnType("character(30)");

                entity.Property(e => e.Modified).HasColumnName("modified");

                entity.Property(e => e.SiteId).HasColumnName("site_id");

                entity.HasOne(d => d.Site)
                    .WithMany(p => p.Hub)
                    .HasForeignKey(d => d.SiteId)
                    .HasConstraintName("hub_site_id_fkey");
            });

            modelBuilder.Entity<Site>(entity =>
            {
                entity.ToTable("site");

                entity.Property(e => e.Id)
                    .HasColumnName("id")
                    .ValueGeneratedNever();

                entity.Property(e => e.Active)
                    .IsRequired()
                    .HasColumnName("active")
                    .HasDefaultValue(true);

                entity.Property(e => e.Created)
                    .HasColumnName("created")
                    .HasDefaultValueSql("CURRENT_TIMESTAMP");

                entity.Property(e => e.Description).HasColumnName("description");

                var majorThresholdProperty = entity.Property(e => e.MajorThreshold)
                    .HasColumnName("major_threshold")
                    .HasDefaultValue(new TimeSpan(0, 4, 30));

                if (Database.IsNpgsql())
                {
                    majorThresholdProperty.HasColumnType("time without time zone");
                }
                else
                {
                    majorThresholdProperty.HasColumnType("text")
                    .HasConversion(
                        property => property.ToString(),
                        columnName => TimeSpan.Parse(columnName)
                    );
                }

                var minorThresholdProperty = entity.Property(e => e.MinorThreshold)
                    .HasColumnName("minor_threshold")
                    .HasDefaultValue(new TimeSpan(0, 4, 00));

                if (Database.IsNpgsql())
                {
                    minorThresholdProperty.HasColumnType("time without time zone");
                }
                else
                {
                    minorThresholdProperty.HasColumnType("text")
                    .HasConversion(
                        property => property.ToString(),
                        columnName => TimeSpan.Parse(columnName)
                    );
                }

                entity.Property(e => e.Modified).HasColumnName("modified");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasColumnName("name");

                entity.Property(e => e.SiteOrder).HasColumnName("site_order");

                entity.Property(e => e.TenantId).HasColumnName("tenant_id");

                entity.HasOne(d => d.Tenant)
                    .WithMany(p => p.Site)
                    .HasForeignKey(d => d.TenantId)
                    .HasConstraintName("site_tenant_id_fkey");
            });

            modelBuilder.Entity<Tenant>(entity =>
            {
                entity.ToTable("tenant");

                entity.Property(e => e.Id)
                    .HasColumnName("id")
                    .ValueGeneratedNever();

                entity.Property(e => e.Active)
                    .IsRequired()
                    .HasColumnName("active")
                    .HasDefaultValue(true);

                entity.Property(e => e.BreachTimeLabel)
                    .HasColumnName("breach_time_label")
                    .HasMaxLength(40)
                    .HasDefaultValue("Breach");

                entity.Property(e => e.Created)
                    .HasColumnName("created")
                    .HasDefaultValueSql("CURRENT_TIMESTAMP");

                entity.Property(e => e.Modified).HasColumnName("modified");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasColumnName("name");
            });
        }
    }
}

CallerHandler.cs

using CtCloudUpload.Data.Context;
using CtCloudUpload.Data.Entities;
using CtCloudUpload.Domain.Files;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CtCloudUpload.Domain.UploadHandlers
{
    public static class CallerHandler
    {
        private static IEnumerable<Caller> GetCallers(Hub hub, IEnumerable<MyFileModel> models)
        {
            if (hub == null)
            {
                throw new ArgumentNullException(nameof(hub));
            }

            if (models == null)
            {
                throw new ArgumentNullException(nameof(models));
            }

            return from file in models
                where file.CallerId.HasValue && file.HouseCodeId.HasValue
                group file by new { CallerId = file.CallerId.Value, HouseCodeId = file.HouseCodeId.Value } into fileGroup
                let latest = fileGroup.Max(file => file.Date)
                select new Caller
                {
                    SiteId = hub.SiteId,
                    CallerId = fileGroup.Key.CallerId,
                    HouseCodeId = fileGroup.Key.HouseCodeId,
                    Name = fileGroup.Where(file => file.Date == latest).FirstOrDefault().CallerName
                };
        }

        public static void UpsertCallers(MyContext context, Hub hub, IEnumerable<MyFileModel> models)
        {
            if (context == null)
            {
                throw new ArgumentNullException(nameof(context));
            }

            if (hub == null)
            {
                throw new ArgumentNullException(nameof(hub));
            }

            if (models == null)
            {
                throw new ArgumentNullException(nameof(models));
            }

            context.Caller.UpsertRange(GetCallers(hub, models).ToList())
            .WhenMatched((dbCaller, insertCaller) => new Caller {
                Name = insertCaller.Name
            })
            .Run();
        }
    }
}

Expression.Convert

line 136 through 138 commented out for ExpressionConvert in file ExpressionHelpers.cs

FlexLabs.Upsert/src/FlexLabs.EntityFrameworkCore.Upsert/Internal/ExpressionHelpers.cs

Line 136 in aaa1354

//return Expression.Lambda<Func<object>>(

FlexLabs.Upsert/src/FlexLabs.EntityFrameworkCore.Upsert/Internal/ExpressionHelpers.cs

Line 137 in aaa1354

// Expression.Convert(expression, typeof(object)))

FlexLabs.Upsert/src/FlexLabs.EntityFrameworkCore.Upsert/Internal/ExpressionHelpers.cs

Line 138 in aaa1354

// .Compile()();

Adding access to existing entities in the `UpdateColumns` command

It was less important with the single entity upsert, but when upserting multiple entities, it's quite important to be able to update entries to new values in the passed dataset. So I need to add the existing entity in the update delegate (and be able to process that in the sql command builders)

Default value of column overrides provided non-default value

I have such entity class:

public class Schedule
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }

        public ScheduleStatus Status { get; set; }

        //other stuff
    }

    public enum ScheduleStatus
    {
        Test,
        Pilot,
        Prod
    }

And DataContext is configured this way:

public class DataContext : DbContext
{
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Schedule>()
                .Property(schedule => schedule.Status)
                .HasConversion(new EnumToStringConverter<ScheduleStatus>())
                .HasDefaultValue(ScheduleStatus.Test);
  
           //other stuff
        }
}

Then I upsert entites this way:

var scheduleStatus = CalculateStatus(someData);
serviceDataContext.Schedules.Upsert(new Schedule
                    {
                        Id = backendSchedule.Id,                        
                        Status = scheduleStatus,
                    })
                    .On(scenario => scenario.Id)
                    .WhenMatched((scenarioInDb, scenarioNew) =>
                        new Schedule
                        {
                            Status = scheduleStatus,
                        })
                    .Run();

And eventually all entites in the DB are populated with the default value of Status, regardless of what is actually assigned in the code above.
I tried to use standard Add method instead of Upsert. This works correctly: when non-default value is provided it's used.

How to setup / import the library?

This might be a stupid question, but I'm new to the C#/.NET world, and I haven't quite gotten my head around how importing libraries works yet.

I am trying to use this library along with .NET Core MVC 2.2. And I am getting the error:

'DbSet' does not contain a definition for 'Upsert' and no accessible extension method 'Upsert' accepting a first argument of type 'DbSet' could be found (are you missing a using directive or an assembly reference?

I have the following line in my .csproj file:

<PackageReference Include="FlexLabs.EntityFrameworkCore.Upsert" Version="2.0.4" />

And my code is:

using FlexLabs.EntityFrameworkCore.Upsert;

...

await _context.Members.Upsert(member).RunAsync();

where _context.Members has the type Microsoft.EntityFrameworkCore.DbSet<Member>, and other EF methods such as Select and Update are available on this object.

Do you have any idea what I need to do to make this work?

No mapping to a relational type can be found for the CLR type 'JObject

When I try to add JObject value I got 'No mapping to a relational type can be found for the CLR type 'JObject

Postgres - UpdateIf() with DateTime throws exception

Today I tried the new method UpdateIf() with a DateTime but it doesn't seem to work.

await _context.MyEntity.Upsert(myEntity)
               .On(x => new { x.Id })
               .UpdateIf(x => x.Date >= date)
               .RunAsync();

I get the following error:

"ERROR"
"operator does not exist: timestamp without time zone >= integer"
"No operator matches the given name and argument types. You might need to add explicit type casts."

Is it currently not possible to use DateTimes in the UpdateIf() method?

InvalidMatchColumndException but not autogenerated keys in sqlite

Hi,

first and foremost: Cool library, that was exactly what I was searching for 👍

I am having a very simple database with sqlite and EF Core and want to use Upsert like this:

await _context.Querries.Upsert(new QueryDbo { Type = query.GetType().Name, Payload = _converter.Serialize(query) }) .RunAsync();

The Type is marked as [key] in the dbContext so I should not need the .On notation. I also tried to use the .On and .When with many combinations, but this just does not work. I also read the sqlite guide and added the references, but the problem still persists. Am I doing something wrong here?

I have the solution on a branch if you want to have a closer look:
this is my usage: https://github.com/Lauchi/EventStoreFacade/blob/upsertTest/Adapters.Framework.Queries/QueryRepository.cs
this is the context:
https://github.com/Lauchi/EventStoreFacade/blob/upsertTest/Adapters.Framework.Queries/QueryStorageContext.cs
this is some tests, that throw the error:
https://github.com/Lauchi/EventStoreFacade/blob/upsertTest/Adapters.Framework.Queries.UnitTests/QueryRepositoryTest.cs

Condition for null checks

Hey @artiomchi!

I noticed that if you use a condition that checks for NULL, the query that is generated is something along these lines:
CASE(SomeCol = NULL, 123, 456)
CASE(SomeCol != NULL, 123, 456)

I believe that it needs to be more like:
CASE(SomeCol IS NULL, 123, 456)
CASE(SomeCol IS NOT NULL, 123, 456)

I think this has to do with ExpandValue/GetSimpleOperator.

Hope you are doing well!

Thanks

support for MySQL / MariaDB

Cool project! Do you know how difficult it would be to add support for MySQL/MariaDB 's INSERT ON DUPLICATE KEY UPDATE? My guess is that it wouldn't be too different from PostgreSQL. I rolled my own in one of my projects, but not sure how flexible/secure/performant it really is.

Can't process 100k entities at once time

When I try to upsert 100k items in the PostgreSQL database at once time:

var context = new TestContext();

context.TestEntities
	.UpsertRange(Enumerable.Range(1, 100000)
		.Select(x => new TestEntity {Id = x, SomeName = x.ToString()}))
	.Run();

context.SaveChanges();

the library throws the following exception:

System.Exception: A statement cannot have more than 65535 parameters
  at Npgsql.NpgsqlCommand.ProcessRawQuery(Boolean deriveParameters) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 784
  at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1157
  at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
  at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
  at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable`1 parameters)
  at FlexLabs.EntityFrameworkCore.Upsert.Runners.RelationalUpsertCommandRunner.Run[TEntity](DbContext dbContext, IEntityType entityType, ICollection`1 entities, Expression`1 matchExpression, Expression`1 updateExpression, Boolean noUpdate, Boolean useExpressionCompiler) in D:\Development\FlexLabs.Upsert\src\FlexLabs.EntityFrameworkCore.Upsert\Runners\RelationalUpsertCommandRunner.cs:line 234
  at FlexLabs.EntityFrameworkCore.Upsert.UpsertCommandBuilder`1.Run() in D:\Development\FlexLabs.Upsert\src\FlexLabs.EntityFrameworkCore.Upsert\UpsertCommandBuilder.cs:line 144
  at X.Program.Main(String[] args) in C:\Users\X\source\repos\X\Program.cs:line 14

Probably it's because PostgreSQL can't process INSERT statement with more than 65535 values.

artiomchi / flexlabs.upsert Goto Github PK

flexlabs.upsert's People

Contributors

Stargazers

Watchers

Forkers

flexlabs.upsert's Issues

Recommend Projects

Recommend Topics

Recommend Org