Querying an array inside a document

Working with Data
1

I know how to retrieve a document from a collection.
This is just an example:

var playerDoc =  trainingsCollection.AsQueryable()
                .SingleOrDefault(p => p.ID == playerID);

The training object is quite simple:

public class Training 
{
    [BsonId]
    public int ID { get; set; }

    // The player's skill data as a nested object
    public List<DaySkills> SkillsHistory { get; set; }
}

where the DaySkills class is defined here:

public class DaySkills
{
    public short Day { get; set; }
    public byte[] Skills { get; set; }
}

If I want to retrieve the last two skills in the list, I can simply create a Linq query on the playerDoc:

var lastTwoDaySkills = playerDoc.DaySkills
    .TakeLast(2);

I wonder if I can retrieve the last two DaySkills in the list from the DB without retrieving the entire document.
Thanks forward for any suggestion.

2

Hi @Leonardo_Daga,

You can try the following:

var query = (from t in collection.AsQueryable<Training>()
                    select t.SkillsHistory.Take(-2));

Which essentially utilises aggregation operator $slice on a $project stage.

Regards,
Wan.

1 Like
3

Thank you @wan for your reply.
I followed your suggestion and I compared the time needed from the instruction you propose (changed just in sintax), that I report here:

Solution1:

var lastTwoDaySkills = collection.AsQueryable()
                        .Where(p => p.ID == playerID)
                        .Select(p => p.SkillsHistory.Take(-2))
                        .SingleOrDefault();

or its equivalent:

Solution 2:

var lastTwoDaySkills = (from t in collection.AsQueryable()
                            where t.ID == playerID
                            select t.SkillsHistory.Take(-2))
                            .SingleOrDefault();

with my first attempt:

Solution 3:

var lastTwoDaySkills  = collection.AsQueryable()
                                     .SingleOrDefault(p => p.ID == playerID)
                                     .SkillsHistory
                                     .TakeLast(2);

Results were slightly favourable in your approach (especially the second solution I wrote), but surprisingly, separating the query and the data extraction as follows:
Solution 4:

var playerDoc = collection.AsQueryable()
                          .SingleOrDefault(p => p.ID == playerID);

var lastTwoDaySkills  = playerDoc.SkillsHistory
                          .TakeLast(2);

solution 4 works slightly faster than 1 and 3, as the solution 2.

I report here the results, for your information (average time for 192 queries like this on the same database):

  • Solution 1: 406ms
  • Solution 2: 366ms
  • Solution 3: 393ms
  • Solution 4: 360ms

In conclusion, it seems that the solution you proposed doesn’t decrease the access time to the information. Better results I obtained in solution 4 are maybe just a lucky run.

I just wonder if the fact I have not used the typed version of AsQueryable respect your proposal and the fact that I added the Where method to restrict the search to a single player means that I’ve missed something from your answer.

I published the project with the four solution tested at the following link: https://github.com/LeonardoDaga/MongoDbSample/tree/master/MongoDbSample/MongoDbConsoleDocumentAccess

Any further suggestion or recommendation is welcome.

King regards,
Leonardo