Combine multiple realm query results .net

Question

As a Contains implementation, I am using a bit tweaked method written by Andy Dent to query my realm database:

        private IQueryable<Entry> FilterEntriesByIds(IQueryable<Entry> allEntries, int[] idsToMatch)
        {
            // Fancy way to invent Contains<>() for LINQ
            ParameterExpression pe = Expression.Parameter(typeof(Entry), "Entry");

            Expression chainedByOr = null;
            Expression left = Expression.Property(pe, typeof(Entry).GetProperty("Id"));

            for (int i = 0; i < idsToMatch.Count(); i++) {
                Expression right = Expression.Constant(idsToMatch[i]);
                Expression anotherEqual = Expression.Equal(left, right);
                if (chainedByOr == null)
                    chainedByOr = anotherEqual;
                else
                    chainedByOr = Expression.OrElse(chainedByOr, anotherEqual);
            }
            MethodCallExpression whereCallExpression = Expression.Call(
              typeof(Queryable),
              "Where",
              new Type[] { allEntries.ElementType },
              allEntries.Expression,
              Expression.Lambda<Func<Entry, bool>>(chainedByOr, new ParameterExpression[] { pe }));

            return allEntries.Provider.CreateQuery<Entry>(whereCallExpression);
        }

It all works just fine as long as I pass less than 2-3K of ids, when I go with larger amounts the app just crashes with what seems to be a stackoverflow exception.

My first thought to solve this was to break the query into chunks and later combine the results, but the Concat and Union methods do not work on these realm IQueryables, so, how else can I merge such chunked results? Or is there any other workaround?

I can't just convert the results to a List or something and then merge, I have to return realm objects as IQueryable<>

The call stack:

=================================================================
    Native Crash Reporting
=================================================================
Got a SIGSEGV while executing native code. This usually indicates
a fatal error in the mono runtime or one of the native libraries 
used by your application.
=================================================================

No native Android stacktrace (see debuggerd output).

=================================================================
    Basic Fault Address Reporting
=================================================================
Memory around native instruction pointer (0x7c326075c8):0x7c326075b8  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0x7c326075c8  fd 7b bb a9 fd 03 00 91 a0 0b 00 f9 10 0e 87 d2  .{..............
0x7c326075d8  10 d0 a7 f2 90 0f c0 f2 b0 0f 00 f9 10 00 9e d2  ................
0x7c326075e8  f0 d5 a9 f2 90 0f c0 f2 b0 13 00 f9 a0 a3 00 91  ................

=================================================================
    Managed Stacktrace:
============================
=====================================
      at Realms.QueryHandle:GroupBegin <0x00000>
      at Realms.RealmResultsVisitor:VisitCombination <0x0007f>
      at Realms.RealmResultsVisitor:VisitBinary <0x003f7>
      at System.Linq.Expressions.BinaryExpression:Accept <0x00073>
      at System.Linq.Expressions.ExpressionVisitor:Visit <0x00087>
      at Realms.RealmResultsVisitor:VisitCombination <0x000d7>
      at Realms.RealmResultsVisitor:VisitBinary <0x003f7>
      at System.Linq.Expressions.BinaryExpression:Accept <0x00073>
      at System.Linq.Expressions.ExpressionVisitor:Visit <0x00087>
      at Realms.RealmResultsVisitor:VisitCombination <0x000d7>
      at Realms.RealmResultsVisitor:VisitBinary <0x003f7>
      at System.Linq.Expressions.BinaryExpression:Accept <0x00073>
      at System.Linq.Expressions.ExpressionVisitor:Visit <0x00087>
      at Realms.RealmResultsVisitor:VisitCombination <0x000d7>
      at Realms.RealmResultsVisitor:VisitBinary <0x003f7>
      at System.Linq.Expressions.BinaryExpression:Accept <0x00073>
      at System.Linq.Expressions.Ex
pressionVisitor:Visit <0x00087>

UPD

I found the exact number of elements after which this error gets thrown: 3939, if I pass anything larger than that, it crashes.

You might have a case of [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) here. :) Sending so many ID-s to a query is not expected. Instead you could extract them to a separate database table (you probably don't have to hold thousands of IDs in memory) and use that in your query. — Šimon Kocúrek, May 10 '20 at 09:08
@ŠimonKocúrek considering that this is a search mechanism, wouldn't it be too much of overhead to write and read from the database each time when making a query? — Movsar Bekaev, May 10 '20 at 09:22
If there is a database provider for the query, mind the maximum number of parameters in one sql command, which in case of SQL Server is 2100. — Martin Staufcik, May 10 '20 at 09:31
@MartinStaufcik yes, it seems that is the problem, but how can I avoid doing this and get the "same" result without crashes? That's the question — Movsar Bekaev, May 10 '20 at 09:36
Since it is a contains operation, you could send multiple queries and then combine the results. — Martin Staufcik, May 10 '20 at 09:39
@MartinStaufcik I probably could but I don't know how, that's what I am asking )) It's a realm database not a SQL DB — Movsar Bekaev, May 10 '20 at 09:41

score 1 · Accepted Answer · answered May 10 '20 at 09:50

I'm not a database expert, just have worked at varying levels (inc Realm) usually on top of someone's lower-level engine. (The c-tree Plus ISAM engine or the C++ core by the real wizards at Realm).

My first impression is that you have mostly-static data and so this is a fairly classical problem where you want a better index generated up front.

I think you can build such an index using Realm but with a bit more application logic.

It sounds a bit like an Inverted Index problem as in this SO question.

For all the single and probably at least double-word combinations which map to your words, you want another table that links to all the matching words. You can create those with a fairly easy loop that creates them from the existing words.

eg: your OneLetter table would have an entry for a that used a one-to-many relationship to all the matching words in the main Words table.

That will delivery a very fast Ilist you can iterate of the matching words.

Then you can flip to your Contains approach at 3 letters or above.

Note that this approach, because it's based on Realm tables, does **not** requiring building the indexes up front. You can also add new words and add the OneLetter and TwoLetter entries for them. Deleting words will also work. — Andy Dent, May 10 '20 at 10:23

Martin Staufcik · Answer 2 · 2020-05-10T10:19:24.107

The SQL provider for SQL Server can handle a contains operation with more items than is the limit for maximum number of SQL parameters in one query, which is 2100.

This sample is working:

var ids = new List<int>();
for (int i = 0; i < 10000; i++)
{
    ids.Add(i);
}

var testQuery = dbContext.Entity.Where(x => ids.Contains(x.Id)).ToList();

It means, you could try to rewrite your method to use ids.Contains(x.Id).

An example how to do it is in this post.

UPDATE: sorry did not mention this is of Realm, but perhaps it is still worth trying.

Combine multiple realm query results .net

2 Answers2