How to Insert Or Update (In Case of New Record)?

[aloksharma1]

Hello,

Is there any option in lib that does, automatic insert or update (when existing record)? sorry if its a repeat new to this library trying to understand what's possible with merge option.

[sdanyliv]

You mean InsertOrReplace or Merge or InsertOrUpdate? These three methods can do a job.

[aloksharma1]

where can i find examples for these methods, i tried the documentations but didn't find it there.

[sdanyliv]

Actually there is no documentation only XML doc. Describe what you are trying to do and I'll point you in right direction and choose most optimal way.

[aloksharma1]

Hello This is My Ef Core Code:

if (blogPostUpdateDTO.HeroHtmls?.Count > 0)
                    {
                        var blogHtmlToInsert = blogPostUpdateDTO.HeroHtmls.Select((x, i) => new BlogPostHeroHtml
                        {
                            Id = x.Id ?? Guid.Empty,
                            BlogId = blogPostUpdateDTO.Id,
                            HeroElementHtml = x.HeroElementHtml,
                            SortOrder = i,
                            SiteId = blogPostUpdateDTO.SiteId
                        }).ToList();
                        if (blogHtmlToInsert.Any(x => x.Id.ToString().ValidateGuid()))
                        {                        
                            _blogHeroHtmlRepository.UpdateRange(blogHtmlToInsert.Where(x => x.Id.ToString().ValidateGuid()));
                        }
                        if (blogHtmlToInsert.Any(x => !x.Id.ToString().ValidateGuid()))
                        {
                            await _blogHeroHtmlRepository.AddRangeAsync(blogHtmlToInsert.Where(x => !x.Id.ToString().ValidateGuid()));
                        }
                        await _blogHeroHtmlRepository.SaveChangesAsync();
                    }

how to make it so that InsertorUpdate happens row by row, the pattern used is repository.

[Shane32]

Not sure if this helps, but here's some code I wrote recently to perform an insert or update in a single SQL call:

//where User is the table in the database, and UserInfo is a temporary class that does not exist in the database
var newUserQuery = db.SelectQuery(() => new UserInfo { Id = id, FirstName = firstName, LastName = lastName, EmailAddress = emailAddress }).AsCte();
await db.Users.Merge()
    .Using(newUserQuery)
    .On(target => target.Id, source => source.Id)
    .UpdateWhenMatched((target, source) => new User { FirstName = source.FirstName, LastName = source.LastName, EmailAddress = source.EmailAddress })
    .InsertWhenNotMatched(source => new User { Id = source.Id, FirstName = source.FirstName, LastName = source.LastName, EmailAddress = source.EmailAddress })
    .MergeAsync(cancellationToken);

And this does the exact same thing using InsertOrUpdate:

//where User is a table in the database
await db.GetTable<User>().InsertOrUpdateAsync(
    insertSetter: () => new User { Id = id, FirstName = firstName, LastName = lastName, EmailAddress = emailAddress },
    onDuplicateKeyUpdateSetter: oldUser => new User { FirstName = firstName, LastName = lastName, EmailAddress = emailAddress },
    token: cancellationToken);

[Shane32]

@aloksharma1 If you're updating multiple rows at once, I'd suggest creating a temp table with the new data (using var table = db.CreateTempTableAsync<T>()), then executing a merge between the temp table and the target table to insert or add/update rows as necessary. The end of the using statement will delete the temporary table.

[sdanyliv]

@aloksharma1, what is ValidateGuid()?

[aloksharma1]

@aloksharma1, what is ValidateGuid()?

validate guid is a custom extension i wrote to check whether guid is empty or null or valid, i will implement both your suggestions into my code and will be back with results. thanks guys

[sdanyliv]

Your query can be rewritten in the following way:

var blogHtmlToInsert = blogPostUpdateDTO.HeroHtmls.Select((x, i) => new 
{
    Id = x.Id,
    BlogId = blogPostUpdateDTO.Id,
    HeroElementHtml = x.HeroElementHtml,
    SortOrder = i,
    SiteId = blogPostUpdateDTO.SiteId
});

await blogPostUpdateDTO.HeroHtmls.ToLinqToDBTable()
    .Merge()
    .Using(blogHtmlToInsert)
    .On((t, s) => t.Id == s.Id && t.SiteId == s.SiteId)
    .InsertWhenNotMatched(s => new BlogPostHeroHtml
    {
        Id = Sql.NewGuid(),
        BlogId = s.BlogId,
        HeroElementHtml = s.HeroElementHtml,
        SortOrder = s.SortOrder,
        SiteId = s.SiteId
    })
    .UpdateWhenMatched((t, s) => new BlogPostHeroHtml
    {
        BlogId = s.BlogId,
        HeroElementHtml = s.HeroElementHtml,
        SortOrder = s.SortOrder,
    })
    .MergeAsync();

Ensure that On condition is correct.