Filters with >= or <=

[maxrodro]

Hi all,
I am developing a project and it requires putting a filter with a date that is >= X Date, with the objective of only synchronizing 7 days from today, is there a way in the filter to do it?

Result I am looking for in stored procedures

[Mimetis]

Because your comparison is >=, you need to use the AddCustomWhere method, like this:

(I take ProductCategory as my table and I want to create a filter on column ModifiedDate)

var setup = new SyncSetup(new string[] { "ProductCategory" });

// Create a filter on table ProductCategory 
var productCategoryFilter = new SetupFilter("ProductCategory");
// Parameter ModifiedDate mapped to the ModifiedDate column
// Allow Null = true
productCategoryFilter.AddParameter("ModifiedDate", "ProductCategory", true);
// Since we are using a >= in the query, we should use a custom where
productCategoryFilter.AddCustomWhere("base.ModifiedDate >= @ModifiedDate Or @ModifiedDate Is Null");

The generated SQL will be

ALTER PROCEDURE [dbo].[ProductCategory_ModifiedDate_changes]
	@sync_min_timestamp bigint = 0,
	@sync_scope_id uniqueidentifier = NULL,
	@ModifiedDate datetime = NULL
AS
BEGIN
SELECT DISTINCT	[side].[ProductCategoryID], 
	[base].[ParentProductCategoryID], 
	[base].[Name], 
	[base].[rowguid], 
	[base].[ModifiedDate], 
	[side].[sync_row_is_tombstone], 
	[side].[update_scope_id] 
FROM [ProductCategory] [base]
RIGHT JOIN [ProductCategory_tracking] [side] ON [base].[ProductCategoryID] = [side].[ProductCategoryID]
WHERE (
(
  base.ModifiedDate >= @ModifiedDate Or @ModifiedDate Is Null
)
AND 
	[side].[timestamp] > @sync_min_timestamp
	AND ([side].[update_scope_id] <> @sync_scope_id OR [side].[update_scope_id] IS NULL)
)

END

Be careful, this technic does not work with MySql (MySql needs parameters with prefix in_ instead of @)

[gentledepp]

Note, though, that in this case,

• Dotmim.Sync will not automagically remove records from your client database, if their @ModifiedDate is is finally older than 7 days.
• even worse: If you synced some record X as it was modified 7 days ago - if you do update this record, but do not raise its modified date, this change will not be propagated to the client.
  If your client, however, changes the record, it will be sent to the server, but be rejected, as the server already has a newer version of record X.

Please keep those constellations in mind!