Realm-dotnet: LINQ Query Using Related Objects Not Working?

[barrysohl]

Goal

Need to be able to query using LINQ with related objects. e.g.

var employee = realm.All().SingleOrDefault( e => e.Department.UniqueId == fooId && e.Name == fooName );

Expected Results

Should return the Employee with the name from the specified Department.

Actual Results

Always returns no matches. Where() also returns no matches. However, eliminating the e.Department and searching only on employee name works fine but obviously does not scope to Department as intended.

public class Department : RealmObject
{
  [Primary Key]
  public string UniqueId { get; set; }
}

public class Employee : RealmObject
{
  [Primary Key]
  public string Name { get; set; }

  // Parent
  public Department Department { get; set; }
}

Version of Realm and tooling

Realm version(s): ?
0.80

[sushihangover]

This is the way to do it using v0.80.0 (at least the only way I know how...):

var deptFilter = theRealm.ObjectForPrimaryKey<Department>("HR");
var employeesByDept = theRealm.All<Employee>().Where((Employee emp) => emp.Department == deptFilter);
var employees = employeesByDept.Where((Employee emp) => emp.Name == "StackOverflow");
foreach (var emp in employees)
{
    D.WriteLine(emp.Name);
}

Ref: http://stackoverflow.com/a/40331608/4984832

[barrysohl]

Perfect, thanks. A little more cumbersome than I was hoping, but does the trick. And I am now realizing that in many of these situations, I can just iterate on someDepartment.Employees directly to search for a match rather than running a query. I assume that would be faster than the LINQ query?

<>On Oct 30, 2016, at 10:27 AM, Robert N. [email protected] wrote:

This is that way to do it using v0.80.0:

var deptFilter = theRealm.ObjectForPrimaryKey("HR");
var employeesByDept = theRealm.All().Where((Employee emp) => emp.Department == deptFilter);
var employees = employeesByDept.Where((Employee emp) => emp.Name == "StackOverflow");
foreach (var emp in employees)
{
D.WriteLine(emp.Name);
}

Ref: http://stackoverflow.com/a/40331608/4984832 http://stackoverflow.com/a/40331608/4984832
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/realm/realm-dotnet/issues/902#issuecomment-257164941, or mute the thread https://github.com/notifications/unsubscribe-auth/AI2JOC70kvEZ6Fm-Fjd7eCt_HBEKxthOks5q5NOegaJpZM4KkaHn.

[nirinchev]

Actually, using LINQ query against realm.All<T> would be faster, because what it does internally is build up a query that is executed very efficiently at the C++ layer (our core) and only matches are returned. Whereas if you did something like:

var someDepartment = realm.ObjectForPrymaryKey<Department>("HR");
var employees = someDepartment.Employees.Where(e => e.Name == "some name");

foreach (var employee in employees)
{
    // Do something
}

the linq engine will fetch each item in the employee collection, then for each item, it will extract the name field from the database and compare it to "some name". If you only have a couple hundred items, you'll probably never notice the difference, but if you have thousands or millions of items in the Employees collection, it may be quite slower. So as a rule of thumb, if you want the fastest query possible, you'll execute it against RealmResults<T> (the value returned from realm.All<T>).

Eventually, we'll be looking into ways to enable using LINQ with related objects, but for the time being the solution proposed by @sushihangover is the most efficient supported one (ObjectForPrimaryKey is very efficient, and then comparing objects in the Where clause is very fast too).

On a side note, it seems like a bug that we failed silently (not returning any results) instead of throwing an exception to make it obvious that's not supported query. @kristiandupont should we look into that?

[barrysohl]

Makes sense, thanks. I was actually referring though to the Employee list that the Department class would have at the C# level. In the example below, is Employees pre-read when a Department is loaded, or is it on-demand? If Employees is already in memory, and the count is low, it seems like it would be efficient to just enumerate on Employees rather than hit “disk”. But I am not familiar with how Realm handles lookahead caching, so I could be way off. Any thoughts would be appreciated, thanks!

public class Department : RealmObject
{
public IList Employees { get; }
}

On Oct 30, 2016, at 4:09 PM, Nikola Irinchev [email protected] wrote:

Actually, using LINQ query against realm.All would be faster, because what it does internally is build up a query that is executed very efficiently at the C++ layer (our core) and only matches are returned. Whereas if you did something like:

var someDepartment = realm.ObjectForPrymaryKey("HR");
var employees = someDepartment.Employees.Where(e => e.Name == "some name");

foreach (var employee in employees)
{
// Do something
}
the linq engine will fetch each item in the employee collection, then for each item, it will extract the name field from the database and compare it to "some name". If you only have a couple hundred items, you'll probably never notice the difference, but if you have thousands or millions of items in the Employees collection, it may be quite slower. So as a rule of thumb, if you want the fastest query possible, you'll execute it against RealmResults (the value returned from realm.All).

Eventually, we'll be looking into ways to enable using LINQ with related objects, but for the time being the solution proposed by @sushihangover https://github.com/sushihangover is the most efficient supported one (ObjectForPrimaryKey is very efficient, and then comparing objects in the Where clause is very fast too).

On a side note, it seems like a bug that we failed silently (not returning any results) instead of throwing an exception to make it obvious that's not supported query. @kristiandupont https://github.com/kristiandupont should we look into that?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/realm/realm-dotnet/issues/902#issuecomment-257187082, or mute the thread https://github.com/notifications/unsubscribe-auth/AI2JOPmqBfNHu0zaiHAdwr6kAWtKxpJgks5q5SOmgaJpZM4KkaHn.

[nirinchev]

With Realm, we actually don't preload/cache anything. As long as your object is managed by the Realm, the data in it is "live" - that is, it's the most up-to date version that's in the database on the disk. So, basically, when you access a list or relationship property, we'll get a reference to the exact place that property is stored on disk, but we won't load any items until you specifically request them (e.g. by foreach or via LINQ). That allows us to have a super fast object and property loading - as I said, with a few hundred/thousand items, you'll never notice the difference.

However, it is always more optimal to execute the query against the database directly (by using LINQ on RealmResults), because then, we'll have much fewer roundtrips between the c# layer and the native C++ layer. Also, when querying over IList properties, the LINQ engine is not aware of any indexes, so it can't apply any optimizations that the Realm engine can.

In short, don't be "afraid" to use LINQ over ILists, it will work just fine and should be very fast (if it's not, let us know and we'll fix it 😜). But if you're looking for a few items in a very large collection, Realm.All<T>().Where(...) is the way to go.

[barrysohl]

Ah yes, I keep forgetting about the “live” nature of Realm. Very helpful, thanks!

-Barry

On Oct 31, 2016, at 11:33 AM, Nikola Irinchev [email protected] wrote:

With Realm, we actually don't preload/cache anything. As long as your object is managed by the Realm, the data in it is "live" - that is, it's the most up-to date version that's in the database on the disk. So, basically, when you access a list or relationship property, we'll get a reference to the exact place that property is stored on disk, but we won't load any items until you specifically request them (e.g. by foreach or via LINQ). That allows us to have a super fast object and property loading - as I said, with a few hundred/thousand items, you'll never notice the difference.

However, it is always more optimal to execute the query against the database directly (by using LINQ on RealmResults), because then, we'll have much fewer roundtrips between the c# layer and the native C++ layer. Also, when querying over IList properties, the LINQ engine is not aware of any indexes, so it can't apply any optimizations that the Realm engine can.

In short, don't be "afraid" to use LINQ over ILists, it will work just fine and should be very fast (if it's not, let us know and we'll fix it 😜). But if you're looking for a few items in a very large collection, Realm.All().Where(...) is the way to go.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/realm/realm-dotnet/issues/902#issuecomment-257380028, or mute the thread https://github.com/notifications/unsubscribe-auth/AI2JOAGl1xG8DBa7BXXO1rYyInk1BN6Mks5q5jRegaJpZM4KkaHn.

[raudabaugh]

Any update on this issue? It's a big concern at our company.

[nirinchev]

Can you be more explicit about the exact concerns you have? Obviously it's less than ideal, but in many cases there are workarounds. In the worst case, you can limit your query as much as possible then filter the remainder with LINQ-to-objects, which, while slower, should still be quite performant.

[raudabaugh]

The worst case that you describe is a bit optimistic. It's dependent on reasonably limiting with Realm, but that's not possible for all of our queries. In those cases, LINQ has to process a large number of objects and becomes a bottleneck. Though our app performance hasn't been hurt yet due to this bug, we're getting close to the thousands of items threshold to which you alluded. If you have any estimate for when this will be fixed, it'd help us a lot when we consider our plans to scale out to more Xamarin customers.

[nirinchev]

We definitely would like to improve our query support and it's fairly high on our TODO list. Unfortunately, we don't have a timeline for when it'll land at the moment.

That being said, have you benchmarked your data model to figure out when using LINQ to objects will become a bottleneck? The thousands of items figure I mentioned is not a threshold by any means and instead of taking it at face value, it's best to do some profiling to figure out when it becomes a problem for your app and if there are obvious workarounds.

For example, it's unlikely you need thousands of items displayed on the screen. You can do something like: realm.All<Foo>().Where(some-supported-filter).AsEnumerable().Where(unsupported-filter).Take(100).ToArray() - this way, unless you hit a pathological case, you should get results fairly quickly. Then you can continue paginating: I highly doubt some user will scroll through thousands of records.

[Adam-Langley]

Hi, where is this at today?
I have a very simple model:

User { Phone = "555 5555"  }
Calls { Notes = "query about open hours", Caller = User }

With a required list that will be scrolled through, so I need the performance of a lazy IRealmCollection, not just a pre-loaded list.

var listThatIWillProjectToViewModels = _realm.All<Calls>().Where(x => x.Caller.Phone.Contains(searchstring));

I dont see a way to achieve this today in C#. However, I can execute the desired query in RealmStudio.
Perhaps the quickest way forward is to give use a 'All<>" method which takes a predicate string just like in the ObjC examples?
I don't specifically need Linq - I just need a way to use the same filter power in C# as we have in ObjC.

[nirinchev]

You can use the .Filter method on IQueryable - it accepts a string predicate just like Studio.

[Adam-Langley]

OMG how did I miss this.
Could you please add this to the .NET documentation?
https://realm.io/docs/dotnet/latest/

I should have visually scanned the API/intellisense - I thought it was weird that the other languages could filter on a string but .net couldnt... just missing from the docs page I guess.
My searches just kept taking me back to this topic where the predicate filtering is never mentioned as an alternative.

Thanks!

Now I can write my own LINQ provider that adds all the missing predicate capabilities too... and the skies open!!

[nirinchev]

We're in the process of overhauling our docs which is why the current pages have been a bit stale. But we'll definitely be adding that when the new docs launch.

[nirinchev]

By the way, if you go for your own LINQ provider and it turns out great, we'd be more than happy to accept a PR 😊

[peppy]

Would love to see extended linq support!

[JorgeBeserra]

You can use the .Filter method on IQueryable - it accepts a string predicate just like Studio.

This should really be in bold in the documentation .... lol

[nirinchev]

Added a ticket to document that, so I'm closing that issue.