Linq query via value conversion defined property

Question

Apologies if title is confusing.

I have a User entity, which stores list of external Ids.

public class User
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public int[] ExternalIds { get; set; }
}

I'm using EF Core value converters to convert those values to comma separated string.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var valueComparer = new ValueComparer<int[]>(
        (c1, c2) => c1.SequenceEqual(c2),
        c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
        c => c.ToArray());

    modelBuilder
        .Entity<User>()
        .Property(user => user.ExternalIds)
        .HasConversion(
            externalIds => string.Join(',', externalIds),
            dbExternalIds => dbExternalIds.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray())
        .Metadata
        .SetValueComparer(valueComparer);
}

Now, I'm having a problem to query via ExternalIds. If I run the following, I get back correctly constructed User objects

using (var context = new PersonDbContext())
{
    var allUsers = context.Users.ToList();
}

However, if I try to query using ExternalIds I'm having problems

First try was a simple query like this:

var user = 
    context.Users.FirstOrDefault(u => u.ExternalIds.Contains(externalId));

But this returns no result, which is not surprising as this is generated SQL

SELECT TOP(1) [u].[Id], [u].[ExternalIds], [u].[Name]
FROM [Users] AS [u]
WHERE CAST(1 AS bit) = CAST(0 AS bit)

I was looking into making Like queries via EF.Functions but can't make it compile due to ExternalIds not being string

var user = context.Users
    .FirstOrDefault(u =>
        EF.Functions.Like(u.ExternalIds, "%1%"));

What's the correct way to query via property which has value converters ?

While I don't like storing values as comma separated string, putting ExternalIds into dedicated table sounds like an overkill - it's going to have only Ids & nothing else.

I'm using EF Core 3.1 on .Net Core 3.1.

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.2" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.2" />

Answer 1

What you can do is trick EntityFramework Core to think it's a string by making it into a first class object and stick an explicit string conversion operator into it.

public class UserExternalIds : int[]
{
    // Add your validation, initialization, overrides, etc..

    // Only to support ef core query.
    public static explicit operator string(UserExternalIds v)
    {
        throw new NotImplementedException();
    }
}

public class User
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public UserExternalIds ExternalIds { get; set; }
}

Then to write your query you would do it like the following:

Because this is an expression used to translate linq into SQL, the exception won't be thrown unless it had to do client side evaluation.

// This compiles because of the explicit conversion operator.
var user = 
    context.Users.FirstOrDefault(u => ((string)u.ExternalIds).Contains(externalId));