[QUESTION] Many to Many relation in LiteDB. Is it possible or use other approach?

[mdmoura]

Using LiteDB I have the following Pocos:

public class Post { 
  public ObjectId Id { get; set; }
  public String Title { get; set; }
  public String Content { get; set; }
}

public class Tag { 
  public ObjectId Id { get; set; }
  public String Name { get; set; }
}

Each Post can have many Tags and each Tag can be used in many Posts.

Is it possible to create a Many to Many relation in LiteDB?

My idea would be create a new POCO named PostTag:

public class PostTag { 
  public ObjectId PostId { get; set; }
  public ObjectId TagId { get; set; }
}

And then update the Post and Tag Pocos to the following:

public class Post { 
  public ObjectId Id { get; set; }
  public String Title { get; set; }
  public String Content { get; set; }
  public List<PostTag> { get; set; }
}

public class Tag { 
  public ObjectId Id { get; set; }
  public String Name { get; set; }
  public List<PostTag> { get; set; }
}

I am not sure if this makes sense. Should I simply use the following?

public class Post { 
  public ObjectId Id { get; set; }
  public String Title { get; set; }
  public String Content { get; set; }
  public List<Tag> { get; set; }
}

And what if I need all Tags used in posts?

I would get all tags in all posts and create a list with unique names?

[lbnascimento]

@mdmoura I recommend storing each class in its collection and a list of tags as BsonRef in every post.

public class Post
{
    public ObjectId Id { get; set; }
    public String Title { get; set; }
    public String Content { get; set; }

    [BsonRef("tags")]
    public List<Tag> Tags { get; set; }
}

public class Tag
{
    public ObjectId Id { get; set; }
    public String Name { get; set; }
}

This would allow you to efficiently list all posts, list all ags and list all tags in a Post. If you wanted to list all Posts with a specific tag, you could just get the id of the tag you're searching and do something like postsCollection.Find(x => x.Tags.Count(x => x.Id == myTag) > 0) even without the Include, which is more efficient.

[mdmoura]

@lbnascimento I tried the following but got really strange results:

        BsonMapper mapper = BsonMapper.Global;
        
        mapper.Entity<Post>().DbRef(x => x.Tags, "tags");

        List<Post> model = new List<Post> {
          new Post {
            Title = "First Post",
            Slug = "first-post",
            Tags = new List<Tag> { 
              new Tag { Name = "Book", Slug = "book" },
              new Tag { Name = "Europe", Slug = "europe" } 
            },
          },
          new Post {
            Title = "Second Post",
            Slug = "second-post",
            Tags = new List<Tag> { 
              new Tag { Name = "Travel", Slug = "travel" },
              new Tag { Name = "Europe", Slug = "europe" } 
            }
          }
        };
        ILiteCollection<Tag> tags = database.GetCollection<Tag>("tags");
        tags.EnsureIndex(x => x.Slug);
        tags.InsertBulk(data.SelectMany(x => x.Tags));

        ILiteCollection<Post> posts = database.GetCollection<Post>("posts");
        posts.EnsureIndex(x => x.Slug);
        posts.InsertBulk(data);

        var a = tags.FindAll();

        var b = posts.Include(x => x.Tags).Find(x => x.Tags.Count(x => x.Slug == "book") > 0);

When I check a I have 4 tags: Book, Europe, Travel, Europe. Shouldn't Europe appear only once?

And when I check b I have the two posts ... The second post should not be returned because it does not have the tag with slightly "book".

What am I missing? Am I inserting the data incorrectly?

[mdmoura]

@lbnascimento I also tried the following:

posts.Include(x => x.Tags).Find(x => x.Tags.Select(x => x.Slug).Any(x => x == "book"));

And this returns an empty list ...

I think something might be wrong when using filters with child collections. No?

[mdmoura]

@lbnascimento The following query returns the one post, as expected, that contaoins a Tag with slug book:

       var a = lessons.Include(x => x.Topics).FindAll()
                    .Where(x => x.Topics.Count(x => x.Slug == "book") > 0)
                    .ToList();

The following return all record like it wasn't filtered.

       var b= lessons.Include(x => x.Topics)
                   .Find(x => x.Topics.Count(x => x.Slug == "book") > 0)
                   .ToList();

Any idea why?

[zmira]

@mdmoura a will apply an in-memory filter (.NET LINQ), after retrieving all items from litedb, whereas b will attempt to apply the filter while retrieving the desired items from litedb.

Have you tried the below?

var c = lessons.Include(x => x.Topics)
                   .Find("Topics[*].Slug ANY = @0", "book")
                   .ToList();

[lbnascimento]

@mdmoura Regarding a, what I believe is happening is that you're using autoid for tags (by not setting an Id value for your tags, LiteDB creates one automatically upon insertion) and it doesn't know that the "Europe" tag in the first post and the "Europe" tag in the same post are actually the same, and ends up inserting it twice. Try creating the tags beforehand and attributing them to each post.

Regarding b, there was a small bug with Linq-to-BsonExpression mapping, which I fixed in the master. There is also a limitation in the mapping regarding nested Linq expressions with the same parameter name (I believe this limitation was undocumented, I'll update the documentation). So, if you use our latest master and change your Linq to Find(x => x.Tags.Count(y => y.Slug == "book") > 0), it should work.

Using Find("Tags[*].Slug any = 'book'"), simliar to what @zmira proposed, should work too.