Not working with IQueryable.Contains

[phuc1640]

Let say we have a DbSet that have model like so

public class User {
  public int Id { get; set; }
  public int UserName { get; set; }
  public DateTimeOffset? SendMoneyAt { get; set; }
  [Projectable]
  public bool IsBacker => SendMoneyAt != null;
}

When we try a query like

var backerUsers = DbSet<User >().Where(u => u.IsBacker);

var usersWithLetterA = DbSet<User >().Where(u => u.UserName.Contains("A")).Select(u => u.Id);

var backerUsersWithLetterA = users.Where(u => usersWithLetterA.Contains(u.Id));

The query of backerUsersWithLetterA cannot be translated.

Is this a bug or a some limitation?

[koenbeuk]

I'm unable to reproduce this:

SELECT [u].[Id], [u].[SendMoneyAt], [u].[UserName]
FROM [User] AS [u]
WHERE [u].[Id] IN (
    SELECT [u0].[Id]
    FROM [User] AS [u0]
    WHERE [u0].[UserName] LIKE N'%A%'
)

test case:

        public class User
        {
            public int Id { get; set; }
            public string? UserName { get; set; }
            public DateTimeOffset? SendMoneyAt { get; set; }
            [Projectable]
            public bool IsBacker => SendMoneyAt != null;
        }

        [Fact]
        public void Issue112()
        {
            var dbContext = new SampleDbContext<User>();
            var usersWithLetterA = dbContext.Set<User>().Where(u => u.UserName!.Contains("A")).Select(u => u.Id);
            var backerUsersWithLetterA = dbContext.Set<User>().Where(u => usersWithLetterA.Contains(u.Id));

            Verifier.Verify(backerUsersWithLetterA.ToQueryString());
        }

[samolesen]

fyi, I hit the same error when calling Contains on a projectable property like this: myDbSet.Where(x => list.Contains(x.ProjectableProperty)).ToList();

Translation of member 'ProjectableProperty' on entity type 'MyType' failed. This commonly occurs when the specified member is unmapped.

@koenbeuk, I believe you carried a small bug in @phuc1640's example to the test case:

var dbContext = new SampleDbContext<User>();
var backerUsers = dbContext.Set<User>().Where(u => u.IsBacker); // missing in your test case
var usersWithLetterA = dbContext.Set<User>().Where(u => u.UserName!.Contains("A")).Select(u => u.Id);
var backerUsersWithLetterA = backerUsers.Where(u => usersWithLetterA.Contains(u.Id)); // I believe the intention was to refer to backerUsers instead of the User set

Verifier.Verify(backerUsersWithLetterA.ToQueryString());

That said I haven't executed this test myself, so I can't say whether it reproduces the error.

[koenbeuk]

The culprit here is this line: backerUsers.Where(u => usersWithLetterA.Contains(u.Id)); where if u.Id was a projectable property then it would throw this error

[Yarith]

I have written some tests and ran them with version 3.0.4 source code. I could create the InvalidOperationException only in the case when the [Projectable] is used inside an IQueryable which then is used by the Contains call. Sadly I was not able to find a simple example which uses a [Projectable] property as the argument of the Contains call which produces an exception. I put the result of the test run above the Verify call of each test case. Maybe this helps to find a fix.

public class User
{
    public int Id { get; set; }
    public string? UserName { get; set; }
    public DateTimeOffset? SendMoneyAt { get; set; }
    [Projectable]
    public bool IsBacker => SendMoneyAt != null;
    [Projectable]
    public int OtherId => Id;
}

[Fact]
public void Issue112_ProjectableQueryableContains()
{
    var dbContext = new SampleDbContext<User>();
    var backerUserIds = dbContext.Set<User>().Where(u => u.IsBacker).Select(u => u.Id);
    var usersWithLetterA = dbContext.Set<User>().Where(u => u.UserName!.Contains("A"));
    var backerUsersWithLetterA = usersWithLetterA.Where(u => backerUserIds.Contains(u.Id));

    // Result with 3.0.4: InvalidOperationException
    /* System.InvalidOperationException : The LINQ expression 'DbSet<User>()
        .Where(u0 => u0.IsBacker)' could not be translated. Additional information: 
        Translation of member 'IsBacker' on entity type 'User' failed. This commonly 
        occurs when the specified member is unmapped. Either rewrite the query in a 
        form that can be translated, or switch to client evaluation explicitly by inserting 
        a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 
        See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
    */
    Verifier.Verify(backerUsersWithLetterA.ToQueryString());
}

[Fact]
public void Issue112_ProjectableQueryableJoin()
{
    var dbContext = new SampleDbContext<User>();
    var backerUserIds = dbContext.Set<User>().Where(u => u.IsBacker);
    var usersWithLetterA = dbContext.Set<User>().Where(u => u.UserName!.Contains("A"));
    var backerUsersWithLetterA = usersWithLetterA.Join(backerUserIds, x => x.Id, x => x.Id, (u, _) => u);

    // Result with 3.0.4: Works
    /* SELECT [u].[Id], [u].[SendMoneyAt], [u].[UserName]
       FROM [User] AS [u]
       INNER JOIN (
           SELECT [u0].[Id]
           FROM [User] AS [u0]
           WHERE [u0].[SendMoneyAt] IS NOT NULL
       ) AS [t] ON [u].[Id] = [t].[Id]
       WHERE [u].[UserName] LIKE N'%A%' 
    */
    Verifier.Verify(backerUsersWithLetterA.ToQueryString());
}

[Fact]
public void Issue112_QueryableContainsUsesProjectableProperty()
{
    var dbContext = new SampleDbContext<User>();
    var backerUsers = dbContext.Set<User>().Where(u => u.IsBacker);
    var usersWithLetterA = dbContext.Set<User>().Where(u => u.UserName!.Contains("A")).Select(x => x.Id);
    var backerUsersWithLetterA = backerUsers.Where(u => usersWithLetterA.Contains(u.OtherId));

    // Result with 3.0.4: Works
    /* SELECT [u].[Id], [u].[SendMoneyAt], [u].[UserName]
       FROM [User] AS [u]
       WHERE [u].[SendMoneyAt] IS NOT NULL AND EXISTS (
           SELECT 1
           FROM [User] AS [u0]
           WHERE ([u0].[UserName] LIKE N'%A%') AND ([u0].[Id] = [u].[Id]))
    */
    Verifier.Verify(backerUsersWithLetterA.ToQueryString());
}

[Fact]
public void Issue112_ArrayContainsUsesProjectableProperty()
{
    var dbContext = new SampleDbContext<User>();
    var backerUsers = dbContext.Set<User>().Where(u => u.IsBacker);
    var usersWithLetterA = new[] { 1, 2, 3 };
    var backerUsersWithLetterA = backerUsers.Where(u => usersWithLetterA.Contains(u.OtherId));

    // Result with 3.0.4: Works
    /* SELECT [u].[Id], [u].[SendMoneyAt], [u].[UserName]
       FROM [User] AS [u]
       WHERE [u].[SendMoneyAt] IS NOT NULL AND [u].[Id] IN (1, 2, 3)
    */
    var text = backerUsersWithLetterA.ToQueryString();
    Verifier.Verify(backerUsersWithLetterA.ToQueryString());
}

[phuc1640]

@samolesen thank you for correcting my example

[wassim-k]

FYI, I noticed this issue and ran the tests against the changes in my PR #124 and as I expected, it's been fixed as a side-effect of the changes made there.

[koenbeuk]

Since #124 has been merged, this should now be fixed