xConnect Filter expression for DateTime

Question

Has anyone worked on filter expression from xConnect, which filters contacts based on custom facet property (modified in last 24hrs)?

We have written a custom converter and expression fro the same

Converter:

Protected override ConvertResult<IEntityExpressionBuilder> ConvertSupportedItem(ItemModel source)
{
        var convertResult = ConverttoDateTime(this.GetDateTimeValue(source,"ModifiedOn"));
        if (!convertResult.WasConverted)
        {
            return NegativeResult(source, $"Cannot convert GUID condition expression to expression type.");
        }
        var expressionBuilder = new FacetPropertyModifiedOnExpression(convertResult.ConvertedValue,
            base.GetFacetType(source),
            base.GetFacetKeyName(source),
            base.GetFacetPropertyName(source));
        return PositiveResult(expressionBuilder);}

Since ModifiedOn is a Datetime, we could not get a Converter which would solve the purpose, so we had to take ConverttoDateTime(this.GetDateTimeValue(source,"ModifiedOn")) ConverttoDateTime doesn’t do anything in specific it returns a positive result.

Custom Expression

        var left = Expression.Property(getFacetExpression, this.FacetPropertyName);
        var daybefore = Expression.Constant(DateTime.UtcNow.AddDays(-1));
        var daynow = Expression.Constant(DateTime.UtcNow);
        var ex1 =Expression.GreaterThanOrEqual(Expression.Convert(left, typeof(DateTime)), daybefore);
        var ex2 = Expression.LessThan(Expression.Convert(left, typeof(DateTime)), daynow);
        var finalexpression = Expression.AndAlso(ex1, ex2);
        var exp1 = Expression.Lambda<Func<TEntity, bool>>(finalexpression, pe);

The result of exp1 is {entity => ((Convert(entity.GetFacet("DynamicsCrm").ProfileLastUpdatedOn) >= 6/9/2019 10:27:59 AM) AndAlso (Convert(entity.GetFacet("DynamicsCrm").ProfileLastUpdatedOn) < 6/10/2019 10:27:59 AM))} which seems to be correct.

With the above two custom code, it doesn't sync the leads/contacts in the past 24 hrs.

Kindly help

Answer 1

You can use the example below to filter on the date of a date field in your facet. Im my example, my membership facet has a Registration property with a RegistrationDate date property I am filtering on.

using (XConnectClient client = SitecoreXConnectClientConfiguration.GetClient())
{

    var queryable = client.Contacts
        .Where(x => x.GetFacet<MembershipFacet>(MembershipFacet.DefaultFacetKey).Registration.RegistrationDate > DateTime.Now.AddDays(-1))
        .WithExpandOptions(new ContactExpandOptions(MembershipFacet.DefaultFacetKey));

    var contacts = await queryable.GetBatchEnumerator(20);

    while (await contacts.MoveNext())
    {
       // do you stuff
    }
}

If you are looking for pure "When was this data saved" like an interaction, you can use the data extraction query. https://doc.sitecore.com/developers/91/sitecore-experience-platform/en/extracting-contacts-and-interactions.html

Answer 2

The problem is that Convert is part of your expression, so it falls into the custom method calls limitations.

Below an example of an expression that works:

var expression = Expression.Lambda<Func<Contact, bool>>(
    Expression.GreaterThan(
        Expression.Property(Expression.Call(contact, "GetFacet", new[] { typeof(EngagementMeasures) }), "MostRecentInteractionStartDateTime"),
        Expression.Constant(DateTime.UtcNow)),
    contact);

A good way to work it out is to begin by a linq expression and to see in the debugger how it looks. For example, I was checking the above like this:

Expression<Func<Contact, bool>> expression = c => c.GetFacet<EngagementMeasures>().MostRecentInteractionStartDateTime > DateTime.UtcNow;

Notice there is no overload of GetFacet without the generics argument:

That is important, since its what allows the system to know the type of the property in this expression is DateTime:

Expression.Property(Expression.Call(contact, "GetFacet", new[] { typeof(EngagementMeasures) }), "MostRecentInteractionStartDateTime")