How to Join In LiteDb

Question

How can i join between two table in LiteDb Like SQL Example : I Have Two table User and ActivityLog

Here is the Model

public class ActivityLog
{
    [BsonId]
    public int Id { get; set; }
    public string UserId { get; set; }
    public string Action { get; set; }
    public DateTime ActionDateTime { get; set; }
}


public class User
{
    [BsonId]
    public int Id { get; set; }
    public string UserId { get; set; }
    public string UserName { get; set; }
    public DateTime LoginDate { get; set; }

}

I need to join Activity.UserID = User.UserId. Is there any way to join like sql

Answer 1

From official documentation

LiteDB is a document database, so there is no JOIN between collections. If you need reference a document in another document you can use DbRef. This document reference can be loaded when the database is initialized or when a query is run, or after a query is finished.

In your case, you can do smth like

public class ActivityLog
{
    [BsonId]
    public int Id { get; set; }
    public DbRef<User> User { get; set; }
    public string Action { get; set; }
    public DateTime ActionDateTime { get; set; }
}


public class User
{
    [BsonId]
    public int Id { get; set; }
    public string UserId { get; set; }
    public string UserName { get; set; }
    public DateTime LoginDate { get; set; }

}


//usage
// Getting user and activityLog collections
var usersCollection = db.GetCollection<User>("Users");
var activityLogsCollection = db.GetCollection<ActivityLog>("ActivityLogs");

// Creating a new User instance
var user = new User { UserId = 5, ...};
usersCollection.Insert(user);

// Create a instance of ActivityLog and reference to User John
var activityLog = new ActivityLog
{
    OrderNumber = 1,
    OrderDate = DateTime.Now,
    //Add it by DbRef
    User = new DbRef<User>(usersCollection, user.UserId)
};
activityLogsCollection.Insert(activityLog)

See the documentation for more details.