No results when searching for contacts on facet value

Question

I'm trying to query contacts based on their e-mail value using xConnect, but my query doesn't return any results:

var queryable = client.Contacts
                .Where(c => c.GetFacet<EmailAddressList>(EmailAddressList.DefaultFacetKey).PreferredEmail.SmtpAddress == email)
                .WithExpandOptions(new ContactExpandOptions(PersonalInformation.DefaultFacetKey, EmailAddressList.DefaultFacetKey));

var enumerator = queryable.GetBatchEnumeratorSync(50);

However, when I just check if the SmtpAddress isn't empty, it does return results. The only downside is that I then have to search through all those contacts after retrieving them in-memory which is, let's say, kind of a performace hog (my local SQL Server instance is still churning away as I'm typing this):

var queryable = client.Contacts
                .Where(c => c.GetFacet<EmailAddressList>(EmailAddressList.DefaultFacetKey).PreferredEmail.SmtpAddress != string.empty)
                .WithExpandOptions(new ContactExpandOptions(PersonalInformation.DefaultFacetKey, EmailAddressList.DefaultFacetKey));

var enumerator = queryable.GetBatchEnumeratorSync(50);

while (enumerator.MoveNext())
{
    foreach (var contact in enumerator.Current.Where(c => c.GetFacet<EmailAddressList>(EmailAddressList.DefaultFacetKey).PreferredEmail.SmtpAddress == email))
    {...}
}

The e-mail address I'm looking for is definitely in the set of contacts as I can find it just fine with the second method, retrieving all contacts with a non-empty e-mail address and using the .Where() line from the first method when iterating on the batch result.

So what's the difference here, why can't I search for a string ìn the query?

Answer 1

This is not working because of PIISensitive Attribute :

[PIISensitive]
public string SmtpAddress
{
    get;
    set;
}

Solution: In order to enable searching by this sensitive information, you need to enable indexing of this kind of facets (decorated with [PIISensitive] facet).

You need to set “true” in IndexPIISensitiveData setting in XConnect SearchIndexer settings.

You need to rebuild xDB search index afterwards to make changes visible and therefore email facet searchable.

Peter Prochazka wrote a nice blogpost regarding same issue: https://tothecore.sk/2018/06/21/searching-contacts-by-email-with-xconnect-in-sitecore-9/