Can't use "Contains" collection method to filter tables

[heinsenberg82]

I'm trying to filter a database with a method like this:

var test = await _client
            .From<TestTable>()
            .Where(u => listOfInts.Contains(u.fieldOfTypeInt))
            .Get();

However, this give me an error like this:

System.InvalidOperationException: variable 'u' of type 'Database.Models.TestTable' referenced from scope '', but it is not defined
   at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at Postgrest.Linq.WhereExpressionVisitor.GetArgumentValues(MethodCallExpression methodCall)
   at Postgrest.Linq.WhereExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at Postgrest.Table`1.Where(Expression`1 predicate)
   ...

If i try a filter method that does not use the Contains collection method, there is no problem. Like:

var test = _client
            .From<TestTable>()
            .Where(u=> user.fieldOfTypeInt == 10);

(this works)

[acupofjose]

Can you try this instead?

var test = await _client
            .From<TestTable>()
            .Filter(u => u.fieldOfTypeInt, Operator.ContainedIn, listOfInts)
            .Get();

[heinsenberg82]

Can you try this instead?

var test = await _client
            .From<TestTable>()
            .Filter(u => u.fieldOfTypeInt, Operator.ContainedIn, listOfInts)
            .Get();

What I really want could be achieved, in your example, by an operator like "NotContainedIn". Is there anything like that?

[acupofjose]

Apologies on the delay getting back to you. Getting back to the swing of things after thanksgiving.

This Not method is actually missing the (better) linq expression, but the following should work:

var test = await _client
            .From<TestTable>()
            .Not(nameof(TestTable.fieldOfTypeInt), Operator.ContainedIn, listOfInts)
            .Get();

[heinsenberg82]

Apologies on the delay getting back to you. Getting back to the swing of things after thanksgiving.

This Not method is actually missing the (better) linq expression, but the following should work:

var test = await _client
            .From<TestTable>()
            .Not(nameof(TestTable.fieldOfTypeInt), Operator.ContainedIn, listOfInts)
            .Get();

Thanks for the feedback. I tried your code, but it gives me this error:

Cannot resolve method 'Not(string, Postgrest.Constants.Operator, System.Collections.Generic.List<int>)', candidates are:   Postgrest.Table<Database.Models.TestTable> Not(string, Postgrest.Constants.Operator, string) (in interface IPostgrestTable<TestTable>)   Postgrest.Table<Database.Models.TestTable> Not(string, Postgrest.Constants.Operator, System.Collections.Generic.Dictionary<string,object>) (in interface IPostgrestTable<TestTable>)   Postgrest.Table<Database.Models.TestTable> Not(string, Postgrest.Constants.Operator, System.Collections.Generic.List<object>) (in interface IPostgrestTable<TestTable>)

It seems I can't fit my list of int inside the pararameter of type List<object>.

[acupofjose]

Oooookay - this should be fixed in v3.3.0!

[mathewgrabau]

Hello, @acupofjose I don't know that this issue is fixed. I defined the list of ints that were to be passed, downcasting them from longs. I got the same result with both ContainedIn, and In.

I tried the suggested query and I am getting the following exception:

Postgrest.Exceptions.PostgrestException: Unknown criterion type, is it of type `string`, `int`, `float`, `List`, `Dictionary<string, object>`, `FullTextSearchConfig`, or `Range`?
         at Postgrest.Table`1.Filter[TCriterion](String columnName, Operator op, TCriterion criterion)
         at Postgrest.Table`1.Filter[TCriterion](Expression`1 predicate, Operator op, TCriterion criterion)

When I try to using a cast to List<object> I receive the following:

Postgrest.Exceptions.PostgrestException: {"code":"42725","details":null,"hint":"Could not choose a best candidate operator. You might need to add explicit type casts.","message":"operator is not unique: bigint <@ unknown"}

[acupofjose]

Can you give the code you’re using for the call so I can debug please?

[mathewgrabau]

I can give you snippets (it's not a public/open source project). I have reproduced this with several different types of List: List<int>, List<string>, List<long>, List<object>:

// Model class 
[Table("example_model")]
 public class ExampleModel: Postgrest.Models.BaseModel
    {
        [PrimaryKey("id")]
        public Guid Id { get; set; }
        
        [Column("search_id")]
        public Guid SearchId { get; set; }
        
        [Column("ref_id")]
        public Guid RefId { get; set; }
        
        [Column("created_at", ignoreOnInsert: true)]
        public DateTime? CreatedAt { get; set; }
        
        [Column("updated_at", ignoreOnInsert: true)]
        public DateTime? UpdatedAt { get; set; }

        [Column("deleted")]
        public bool Deleted { get; set; } = false;
    }

// Search code example:
var searchList = new List<string>() { Guid.NewGuid().ToString() };
 var growerUsers = await _client.From<ExampleModel>()
            .Filter("id", Constants.Operator.ContainedIn, searchList())
            .Get();
// Also tried casting to List<object>

Not sure if that helps enough, sorry.

[acupofjose]

Ah - I think I see the problem. You're correct, my fix wasn't complete 🤷‍♂️ !

[acupofjose]

If you could try on v3.5.0 the following:

var searchList = new List<string>() { Guid.NewGuid().ToString() };
var growerUsers = await _client.From<ExampleModel>()
            .Filter(x => x.Id, Operator.In, searchList)
            .Get();

[mathewgrabau]

Sorry, I will give that go and confirm for you!