Modeling mongodb stored referenced relationships in .net core

Question

I'm new to mongo and I'm looking for some guidance on best practices when dealing with referenced relationships and modeling them in .net core.

Yes, it's the usual "joins in mongodb??" question. But I haven't found a good answer to this.

To make this simple, lets say I've got a simple API i'm building out with controllers to manage users and accounts.

In mongo there are two collections, Accounts and Users. Users belong to their parent Account. I don't want to go the embedded document route in this case, so each User document will have an AccountID in it to link the user back to their parent Account.

My current entities in .net are:

public class User
{

    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }

    [BsonElement("firstName")]
    public string FirstName { get; set; }

    [BsonElement("lastName")]
    public string LastName { get; set; }

    [BsonElement("email")]
    public string Email { get; set; }

    [BsonElement("status")]
    public string Status { get; set; }

    [BsonElement("type")]
    public string Type { get; set; }

    [BsonElement("createdDateTime")]
    public DateTime CreatedDateTime { get; set; }

    [BsonElement("modifiedDateTime")]
    public DateTime ModifiedDateTime { get; set; }

    [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("accountId")]
    public string AccountId { get; set; }

}

 public class Account
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }

    [BsonElement("name")]
    public string Name { get; set; }

    [BsonElement("status")]
    public string Status { get; set; }

    [BsonElement("type")]
    public string Type { get; set; }

    [BsonElement("createdDateTime")]
    public DateTime CreatedDateTime { get; set; }

    [BsonElement("modifiedDateTime")]
    public DateTime ModifiedDateTime { get; set; }

}

Those are then mapped to models using AutoMapper in the controller

public class UserModel
{

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    [Required]
    public string Email { get; set; }

    [Required]
    public string Status { get; set; }

    [Required]
    public string Type { get; set; }

    [Required]
    public DateTime CreatedDateTime { get; set; }

    [Required]
    public DateTime ModifiedDateTime { get; set; }

    [Required]
    public string AccountId { get; set; }

}

public class AccountModel
{

    [Required]
    public string Name { get; set; }

    [Required]
    public string Status { get; set; }

    [Required]
    public string Type { get; set; }

    [Required]
    public DateTime CreatedDateTime { get; set; }

    [Required]
    public DateTime ModifiedDateTime { get; set; }

}

And an example of a controller method where the mapper is used:

[HttpGet]
    public async Task<ActionResult<List<AccountModel>>> Get()
    {
        try
        {
            var results = await _repository.Get();

            return _mapper.Map < List < AccountModel >>(results);
        }
        catch (Exception ex)
        {
            return this.StatusCode(StatusCodes.Status500InternalServerError, "Database Failure");
        }


    }

All that works just fine. I can call the controller methods, get the data, and it gets mapped from entity to model and then returned from the controller method.

The issue is this: I'd like to return the user data with information from the account (example: account name). So just a simple join.

I think I have an handle on how to do the join itself, using one of the methods outlined in this answer. But my question is, is there a best practice on how to set up my entities and models to make storing this as clean as possible?

I was thinking of adding a property to the User entity to store the related account. Tagged with the [BsonIgnore] attribute so that it stays out of the db.

 [BsonIgnore]
    public Account Account { get; set; }

The property

       [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("accountId")]
    public string AccountId { get; set; }

would still remain in the user entity, so the reference is preserved.

Then, the User model could have properties like

 public string AccountName { get; set; }

They get populated using the mapper.

Is this the best way to set this up when you want to reference related objects rather then embed them? Is there some gotcha here I'm missing?

Answer 1

have a look at the code below. it uses my library MongoDB.Entities which has built-in support for one-to-one, one-to-many and many-to-many relationships between entities.

using MongoDB.Entities;
using System.Linq;

namespace StackOverflow
{
    public class Program
    {
        public class Account : Entity
        {
            public string Name { get; set; }
            public Many<User> Users { get; set; }

            public Account() => this.InitOneToMany(() => Users);
        }

        public class User : Entity
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public One<Account> Account { get; set; }

            [Ignore]
            public string AccountName { get; set; }
        }

        private static void Main(string[] args)
        {
            new DB("test");

            var account = new Account { Name = "parent account" };
            account.Save();

            var user = new User
            {
                FirstName = "dave",
                LastName = "mathews",
                Account = account.ToReference()
            };
            user.Save();

            account.Users.Add(user);

            //find parent by ID
            var parent = DB.Find<Account>().One(account.ID);

            //get first user of parent
            var dave = parent.Users.ChildrenQueryable()
                                   .FirstOrDefault();

            //get dave's account
            var davesAccount = dave.Account.ToEntity();

            //get dave with account name filled in by a single mongo query
            var daveExtra = (from u in DB.Queryable<User>().Where(u => u.ID == dave.ID)
                             join a in DB.Queryable<Account>() on u.Account.ID equals a.ID
                             select new User
                             {
                                 ID = u.ID,
                                 FirstName = u.FirstName,
                                 LastName = u.LastName,
                                 AccountName = a.Name
                             }).SingleOrDefault();
        }
    }
}