Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature suggestion: BulkInsertOrUpdateOrDelete expression filter #424

Closed
Arithmomaniac opened this issue Nov 8, 2020 · 6 comments
Closed

Comments

@Arithmomaniac
Copy link

The "BulkInsertOrUpdateOrDelete" method would be even more powerful if you could use it to synchronize just part of a table:

public static void BulkInsertOrUpdateOrDelete<T>(
   this DbContext context, IList<T> entities, Expression<Func<T, bool>> mergePredicate = null/*, ...*/)

The mergePredicate could then be used to build a CTE for the merge target.

(The discussion at linq2db/linq2db#1166 has bearing on this.)

@AhmedFay
Copy link

AhmedFay commented Mar 2, 2021

this too helpful to check removed data from source in specific parameter (sources that have limitation)

i use this way but there some data from another sources will lost

GetQuerable().Where(x => x.commonProperty == commonProperty).BatchDeleteAsync();
BulkInsertOrUpdate(hugeData, b => b.PropertiesToExclude = new List<string> { "anotherSourceData" });

hugeData => can't check one by one if its removed from source

@borisdj
Copy link
Owner

borisdj commented Mar 16, 2021

Partial sync is now possible with extended Config.
In README:

When using the BulkInsert_/OrUpdate methods, you may also specify the PropertiesToIncludeOnCompare and PropertiesToExcludeOnCompare properties. By adding a column name to the PropertiesToExcludeOnCompare, will allow it to be inserted and updated but will not update the row if any of the other columns in that row did not change. For example, if you are importing bulk data and want to keep an internal CreateDate or UpdateDate, you add those columns to the PropertiesToExcludeOnCompare.

Another option that may be used in the same scenario are the PropertiesToIncludeOnUpdate and PropertiesToExcludeOnUpdate properties. These properties will allow you to specify insert-only columns such as CreateDate and CreatedBy.

If we want only to Insert new ones and to skip those that exist in Db then we can use BulkInsertOrUpdate with config
PropertiesToIncludeOnUpdate = new List<string> { "" }

Additionaly there is UpdateByProperties for specifying custom properties, by which we want update to be done.

Using UpdateByProperties while also having Identity column requires that Id property be Excluded.

@borisdj borisdj closed this as completed Mar 16, 2021
@Arithmomaniac
Copy link
Author

Could you leave some pseudocode as to how you'd map my proposed syntax (i.e. db.BulkInsertOrUpdateOrDelete<Widget>(items, x => x.Code == "C")? The new feature seems to solve a different problem.

@borisdj
Copy link
Owner

borisdj commented Mar 16, 2021

You are right, new feature can filter columns, not rows.
I'm not sure how to implement expression filter.
You could get needed result the following way:
Load into memory (ToList or ToDictionary) all required q = ItemsSet.Where(a => a.Code == "C")
and then split it into 2 Lists for calls to BulkInsertOrUpdate followed by BulkDelete.

@Arithmomaniac
Copy link
Author

I was thinking you could use the expression filter to build a CTE and filter on that CTE.
As an aside, this library is awesome. Thank you ❤️

@borisdj
Copy link
Owner

borisdj commented Mar 16, 2021

I'll take a look when I find the time, meanwhile if you think you could make a PR send one.
Glad it's useful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants