Filtering using IN clause with NSPredicate (DotNet)

[jrlaff]

Hi,

I would like to filter a table based on some property being IN a list I would provide, using an NSPredicate-based query, such as:

List<string> values = new List<string> { "value1", "value2" };
var results = Realm.All<MyObject>().Filter("State IN $0", values);

It's complaining about: CS1503 Argument 3: cannot convert from 'System.Collections.Generic.List<string>' to 'Realms.RealmValue'

Any idea?

Thanks.

[LaPeste]

The signature of Filter is the following:
public static IQueryable<T> Filter<T>(this IList<T> list, string predicate, params RealmValue[] arguments)

As you can see, each argument is expected to be of type Realms.RealmValue. But you're passing a List<string> which can't be accepted because there's no conversion between the two types. This is why you get that error message.

Having very little information on what you're trying to do, I'll try to guess that State could be one of those values in the list. In this case you could achieve that like:

var results = Realm.All<MyObject>().Filter("(State == $0) OR (State == $1)", "value1", "value2");

If you were looking for something completely different, then I'd need more info on your models to be able to help.

Also, it could be of great help to you to take a look at this tutorial. Although it's javascript tutorial, the syntax in the Filter is the same.

I hope this helps.

[jrlaff]

I am looking to get the equivalent of an SQL IN operator.

MyObject.State is just a string and I am interested in a subset of the objects for which State is one of the values of values. Your OR proposal cannot work as I need it to work with any number of items in values.

[nirinchev]

In the code you posted, Id doesn't appear to be a primary key. You need to add the [PrimaryKey] attribute to tell Realm to treat it as such.

[daawaan4U]

My bad, I forgot adding [PrimaryKey]. I tried running the benchmarks again and unfortunately the difference with and without the limit is still negligible.

Results

BenchmarkDotNet=v0.13.1, OS=Windows 10.0.19042.1348 (20H2/October2020Update)
Intel Core i5-10300H CPU 2.50GHz, 1 CPU, 8 logical and 4 physical cores
.NET SDK=6.0.100
  [Host]     : .NET 6.0.0 (6.0.21.52210), X64 RyuJIT
  DefaultJob : .NET 6.0.0 (6.0.21.52210), X64 RyuJIT


|                Method |        Mean |     Error |    StdDev |
|---------------------- |------------:|----------:|----------:|
|      Test_IEnumerable | 13,978.2 us | 243.56 us | 280.49 us |
|       Test_IQueryable |    577.9 us |   2.94 us |   2.60 us |
| Test_IQueryable_Limit |    569.6 us |   1.41 us |   1.18 us |

Code

public class Benchmark_LINQ {

    class Item : RealmObject {
        [PrimaryKey]
        public ObjectId Id { get; private set; } = ObjectId.GenerateNewId();
    }

    Realm Realm { get; }
    ObjectId[] SampleIds { get; }

    public Benchmark_LINQ() {
        var config = new InMemoryConfiguration( "meemoo.db" ) {
            Schema = new Type[] { typeof(Item) }
        };
        Realm = Realm.GetInstance( config );

        Realm.Write( () => {
            Enumerable.Range( 0, 10_000 ).ForEach( _ => {
                Realm.Add( new Item() );
            } );
        } );

        SampleIds = Enumerable.Range( 0, 90 )
            .Select( _ => ObjectId.GenerateNewId() )
            .Concat( Realm.All<Item>().AsEnumerable().TakeLast( 10 ).Select( item => item.Id ) )
            .ToArray();
    }

    [Benchmark]
    public void Test_IEnumerable() {
        _ = Realm.All<Item>().AsEnumerable().Any( x => SampleIds.Contains( x.Id ) );
    }

    [Benchmark]
    public void Test_IQueryable() {
        var predicate = string.Join( " OR ", SampleIds.Select( id => $"Id == oid({id})" ) );
        _ = Realm.All<Item>().Filter( $"{predicate}" ).Any();
    }

    [Benchmark]
    public void Test_IQueryable_Limit() {
        var predicate = string.Join( " OR ", SampleIds.Select( id => $"Id == oid({id})" ) );
        _ = Realm.All<Item>().Filter( $"{predicate} LIMIT(1)" ).Any();
    }
}

[nirinchev]

Hm... I thought this is likely to the way the filter is constructed, but it doesn't appear to be the case. I'll need to re-ping the Core team and see what's going on here.

[nirinchev]

Okay, so turns out there was a bug in the .NET SDK where LIMIT was ignored when .Count()/.Any() is invoked 🤦‍♂️ I have a fix in #2755 and will re-run the benchmark once CI builds a package to see if that resolves the issue.

[nirinchev]

Good news, with the fix in #2755, I can confirm that applying a LIMIT improves the performance of the query:

|                Method |     Mean |     Error |    StdDev |
|---------------------- |---------:|----------:|----------:|
|       Test_IQueryable | 3.829 ms | 0.0511 ms | 0.0478 ms |
| Test_IQueryable_Limit | 3.673 ms | 0.0668 ms | 0.0686 ms |

The reason why the difference is not more pronounced is due to the nature of the test where you have 90 random Ids concatenated with 10 existing ones, meaning that in the LIMIT test case we need to evaluate 91 expressions vs the 100 in the no-LIMIT case. That, combined with some intrinsic overheads of constructing a query and extracting the results means that the performance difference is not as pronounced. If you modify SampleIds to be matches only, such as SampleIds = Realm.All<Item>().AsEnumerable().Take(100).Select(item => item.Id).ToArray();, the perf difference is more noticeable:

|                Method |       Mean |    Error |    StdDev |
|---------------------- |-----------:|---------:|----------:|
|       Test_IQueryable | 3,730.2 us | 73.25 us | 118.28 us |
| Test_IQueryable_Limit |   621.9 us | 12.33 us |  20.94 us |

[tero-paananen]

I managed to make filter into list field in latest Realm.Net

<PackageReference Include="Realm">
      <Version>11.6.1</Version>
</PackageReference>

QueryArgument[] arguments = new QueryArgument[] {"volvo","audi","honda"};
var result = realm.All<Item>().Filter("ANY car == {$0, $1, $2}", arguments);

https://www.mongodb.com/docs/realm/realm-query-language/#query-formats