UPD: I apologize for the misinformation, LINQ knowledge is not enough to make a valid request through realm. I got it a little late.

Suppose I have 2 tables: HistoryItem and Quest

HistoryItem inside has a Quest field and other data. In the Quest table there is a MaxRepeats field.

What I need to get: I need to get a list of quests, which is filtered by date + filtered by the number of entries of THIS QUEST inside HistoryItem.

In this case, I need to show the quest only if it has been completed <MaxRepeats times. That is, for each of the quests to make a subquery that calculates the number of HistoryItem-s in which this quest is recorded in the Quest field. How to do it?

So far, I just have to work out a random code because I do not understand how exactly this entry should look like in principle:

var history = _db.Realm.All<HistoryItem>().Where(a => a.Quest != null).GroupBy(a=>a.Quest); var tmp = _db.Realm.All<Quest>().Where(a => a.StartDate < startDayIsLessThan && a.EndDate > EndDateIsHigherThan);

I found the subquery syntax. For example, the following query finds people who have more than 3 unvaccinated dogs:

 realm.All<Person>() .Filter("SUBQUERY(Dogs, $dog, $dog.Vaccinated == false).@count > 3")
  • one
    Quests.Where(q => q.MaxRepeats > HistoryItems.Count(h => h.Quest == q)) - Andrew NOP

2 answers 2

I think you were moving in the right direction.

  var histCount = _db.Realm.All<HistoryItem>() .GroupBy(h => h.Quest) .Select(gr => new {Q = gr.Key, Count = gr.Count()}); var result = _db.Realm.All<Quest>() // сокращаем коллекцию до join .Where(q => q.StartDate > start && q.EndDate < end) // todo: перепроверить условие // join с группированной коллекцией элементов истории .Join(histCount, quest => quest.Id, hCount => hCount.Q, (quest, hCount) => new {Quest = quest, HCount = hCount.Count}) // фильтрация по условиям кол-ва элементов .Where(it => it.HCount < it.Quest.MaxRepeats) // выборка результата .Select(it => it.Quest) .ToList();
  • Thanks for the reply and the time allotted. It just came to me how to google and it turned out that LINQ knowledge is not enough to make the right subquery through realm = ( - Andrew

It was necessary to make a reverse link inside the Quest from HistoryItem (previously there was only a straight line with HistoryItem in Quest)

 public class Quest : RealmObject { [PrimaryKey] public string Id { get; set; } = Guid.NewGuid().ToString(); public string Name { get; set; } public int MaxRepeats { get; set; } [Backlink(nameof(HistoryItem.Quest))] public IQueryable<HistoryItem> HistoryItems{ get; } } public class HistoryItem : RealmObject { [PrimaryKey] public string Id { get; set; } = Guid.NewGuid().ToString(); public DateTimeOffset DoneTime { get; set; } public Quest Quest { get; set; } }

And then use the query with the filter:

 _db.Realm.All<Quest>().Filter("HistoryItems.@count <= MaxRepeats");

Well, in my case, it was also necessary to ignore the cases when maxRipits is 0:

 _db.Realm.All<Quest>().Filter("MaxRepeats == 0 OR HistoryItems.@count <= MaxRepeats");