I am trying to create a data model where there are Document
entities, which need to be in a defined order to each other. Think of an article that has sections which build up on one another.
Example:
Document1 -> Document3 -> Document2 -> Document6 -> Document5 -> Document4
I was trying to implement a linked list
to achieve that. Unfortunately EF Core 2.0
does not support recursive queries. Therefore I needed a parent entity, named DocumentCollection
. The requirement is to be able to get the Documents
in the predefined order.
Please suggest improvements or alternative approaches.
Entity definitions
DocumentCollection
model:
public class DocumentCollection
{
public int Id { get; set; }
public ICollection<Document> Documents { get; set; }
public DocumentCollection()
{
Documents = new HashSet<Document>();
}
}
Document
model:
public class Document
{
public int Id { get; set; }
public string Name { get; set; }
#region Navigation Properties
public int CollectionId { get; set; }
public DocumentCollection Collection { get; set; }
public int? PredecessorId { get; set; }
public Document Predecessor { get; set; }
public int? SuccessorId { get; set; }
public Document Successor { get; set; }
#endregion
}
DbContext
relationship mapping:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>().HasKey(product => product.Id);
modelBuilder.Entity<Product>().Property(product => product.Name).HasMaxLength(70);
modelBuilder.Entity<DocumentCollection>()
.HasKey(collection => collection.Id);
modelBuilder.Entity<Document>().HasKey(document => document.Id);
modelBuilder.Entity<Document>().Property(document => document.Name).HasMaxLength(70);
modelBuilder.Entity<DocumentCollection>()
.HasMany(collection => collection.Documents)
.WithOne(document => document.Collection)
.HasPrincipalKey(collection => collection.Id)
.HasForeignKey(document => document.CollectionId)
.OnDelete(DeleteBehavior.Cascade);
modelBuilder.Entity<Document>()
.HasOne(document => document.Predecessor)
.WithOne(document => document.Successor)
.HasPrincipalKey<Document>(principal => principal.Id)
.HasForeignKey<Document>(dependent => dependent.PredecessorId);
modelBuilder.Entity<Document>()
.HasOne(document => document.Successor)
.WithOne(document => document.Predecessor)
.HasPrincipalKey<Document>(principal => principal.Id)
.HasForeignKey<Document>(dependent => dependent.SuccessorId);
base.OnModelCreating(modelBuilder);
}
Service used to manage the entities:
public class DocumentService
{
private readonly DataContext _context;
public DbSet<Document> Entities => _context.Documents;
public DocumentService(DataContext context)
{
_context = context;
}
[...]
}
Creating an initial Document
:
public async Task<Document> AddDocument(Document document)
{
document.Collection = new DocumentCollection();
var result = await Entities.AddAsync(document);
await _context.SaveChangesAsync();
return result.Entity;
}
Adding Document
to a specific position:
public async Task<Document> AddDocumentSuccessor(int parentId, Document document)
{
var predecessor = await Entities
.Include(entity => entity.Successor)
.SingleOrDefaultAsync(entity => entity.Id == parentId);
if (predecessor == null)
{
return null;
}
document.CollectionId = predecessor.CollectionId;
var result = await Entities.AddAsync(document);
await _context.SaveChangesAsync();
var newEntity = result.Entity;
newEntity.PredecessorId = predecessor.Id;
if (predecessor.Successor != null)
{
newEntity.SuccessorId = predecessor.SuccessorId;
}
predecessor.SuccessorId = newEntity.Id;
if (predecessor.Successor != null)
{
predecessor.Successor.PredecessorId = newEntity.Id;
}
await _context.SaveChangesAsync();
return result.Entity;
}
Getting an ordered list of related Document
entities:
public async Task<IEnumerable<Document>> GetRelatedDocuments(int id)
{
var collectionId = await Entities
.Where(entity => entity.Id == id)
.Select(entity => entity.CollectionId)
.SingleOrDefaultAsync();
if (collectionId == 0)
{
return null;
}
var result = await Entities
.Where(entity => entity.CollectionId == collectionId)
.Select(entity => new Document
{
Id = entity.Id,
Name = entity.Name,
CollectionId = entity.CollectionId,
PredecessorId = entity.PredecessorId,
SuccessorId = entity.SuccessorId
})
.ToArrayAsync();
return OrderByHierarchy(result);
}
Method used for sorting:
private static IEnumerable<Document> OrderByHierarchy(
IReadOnlyCollection<Document> documents)
{
if (documents.Count == 0)
{
yield break;
}
var dict = documents.ToDictionary
(
entity => entity.PredecessorId ?? 0,
entity => entity
);
var key = 0;
Document document;
do
{
document = dict[key];
yield return document;
key = document.Id;
}
while (document.SuccessorId != null);
}
Ordinal
with the index of the document in the list. \$\endgroup\$Document
entities, I probably have to update allot of them at once. \$\endgroup\$