How to create a many-to-many relationship where relations come in set order?

Question

I have 2 entities: TextDocument and TextBit, they have many-to-many relation:

public class TextBit
{
    public int Id { get; set; }
    public string Value { get; set; }
    public List<TextDocument> TextDocuments { get; set; } = new List<TextDocument>();

}


public class TextDocument 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<TextBit> TextBits { get; set; } = new List<TextBit>();
}

First I add some TextBit entities to the context:

        meContext.Database.EnsureDeleted();
        meContext.Database.EnsureCreated();

        TextBit tb1 = new TextBit() { Value = "name " };
        TextBit tb2 = new TextBit() { Value = " Igor " };
        TextBit tb3 = new TextBit() { Value = " Hello " };
        TextBit tb4 = new TextBit() { Value = " is  " };
        TextBit tb5 = new TextBit() { Value = " my  " };

        meContext.AddRange(new TextBit[] { tb1, tb2, tb3, tb4, tb5 });

        meContext.SaveChanges();

Then I create a TextDocument Entity called "introduction" and add those TextBit entities as relations (There can be many TextDocuments, each combines different sets of TextBits in different order.):

        TextDocument doc1 = new TextDocument() { Name = "Introduction" };
        doc1.TextBits.AddRange(new TextBit[] { tb1, tb2, tb3, tb4});
        meContext.Add(doc1);
        meContext.SaveChanges();

The problem is, if I want to get sensible sentence out of this TextDocument, i.e. a "Hello my name is Igor ", I have to preserve the TextBit order for every TextDocument-TextBit pair in the database. As I understand I have to add an extra column to the combined TextBitTextDocument table to represent an order of each textbit in every textdocument, but how do I then access this column to sort the text bits?

score 0 · Answer 1 · answered Jan 29 '22 at 15:44

I have found a suitable answer it this topic. The idea is to create a mediator-entity and connect both existing entities with it through 2 one-with-many relationships. Here's my realisation:

public class TextBit
{
    public int Id { get; set; }
    public string Value { get; set; }
    public List<BitDocument> BitDocuments { get; set; } = new List<BitDocument>();
}

public class TextDocument 
{
    public TextDocument() { }
    public int Id { get; set; }
    public string Name { get; set; }
    public List<BitDocument> BitDocuments { get; set; } = new List<BitDocument>();
    //This method is to create the resulting text
    public string GetText()
    {
        List<BitDocument> tempList = BitDocuments.OrderBy(x => x.TextBitOrder).ToList();
        string result = "";
        foreach(BitDocument b in tempList)
        {
            result = result + b.TextBit.Value;
        }
        return result;
    }
}
//The mediator class
public class BitDocument
{
    public int Id { get; set; }
    public int TextBitId { get; set; }

    public int TextDocumentId { get; set; }
    public TextBit TextBit { get; set; }
    public TextDocument TextDocument { get; set; }
    public int? TextBitOrder { get; set; }
}

Create a new document and 5 text bits and write them into the database:

    private static void populateContext(MeContext meContext)
    {
        meContext.Database.EnsureDeleted();
        meContext.Database.EnsureCreated();

        TextBit tb1 = new TextBit() { Value = "name " };
        TextBit tb2 = new TextBit() { Value = " Igor " };
        TextBit tb3 = new TextBit() { Value = " Hello " };
        TextBit tb4 = new TextBit() { Value = " is  " };
        TextBit tb5 = new TextBit() { Value = " my  " };

        TextDocument doc1 = new TextDocument() { Name = "Introduction" };

        meContext.AddRange(new TextBit[] { tb1, tb2, tb3, tb4, tb5 });
        meContext.Add(doc1);

        meContext.SaveChanges();
    }

Then create the mediator entites (not the most elegant way, but added for visibility):

    private static void populateContext2 (MeContext context)
    {
        BitDocument b1 = new BitDocument() { TextBit = context.TextBits.Find(1), TextDocument = context.TextDocuments.Find(1), TextBitOrder = 3};
        BitDocument b2 = new BitDocument() { TextBit = context.TextBits.Find(2), TextDocument = context.TextDocuments.Find(1), TextBitOrder = 5};
        BitDocument b3 = new BitDocument() { TextBit = context.TextBits.Find(3), TextDocument = context.TextDocuments.Find(1), TextBitOrder = 1};
        BitDocument b4 = new BitDocument() { TextBit = context.TextBits.Find(4), TextDocument = context.TextDocuments.Find(1), TextBitOrder = 4};
        BitDocument b5 = new BitDocument() { TextBit = context.TextBits.Find(5), TextDocument = context.TextDocuments.Find(1), TextBitOrder = 2};

        context.AddRange(new BitDocument[] { b1, b2, b3, b4, b5 });
        context.SaveChanges();
    }

Then you can just get the document and generate the text:

        TextDocument myTextDoc = context.TextDocuments
        .Include(x => x.BitDocuments).ThenInclude(x => x.TextBit)
            .ToList()[0];
        Console.WriteLine(myTextDoc.GetText());

How to create a many-to-many relationship where relations come in set order?

1 Answers1