I started to use Entity Framework Core in a new project, and so far with a little help the database structure (with it's relationships) worked out fine. I now encountered a situation which I cannot get to work in Entity Framework Core. I will try to describe the current Entities and their relations, and the situation that does not work.
In the project you can manage Tags with nested TagValueCategories and their nested TagValues. I can get all Tags and their nested properties using the below code:
databaseContext.Tags
.Include(tag => tag.TagValueCategories)
.ThenInclude(tagValueCategory => tagValueCategory.TagValues);
This works great and as expected.
In the project you can also manage Files. A File contains multiple properties and can contain specific selected Tags and optionally TagValues.
So for example FileA.png can contain the tag Animal and tagvalues Dog and Cat.
I have used the below entity FileTag to make it possible to store the Tags and TagValues related to a File. This also works, but reading the data from the database is not as friendly as I would like.
For example I cannot do this, which is the situation I would like to achieve:
databaseContext.Files
.Include(file => file.Tags)
.ThenInclude(tag => tag.TagValueCategories)
.ThenInclude(tagValueCategory => tagValueCategory.TagValues);
In the current situation I can read the related Tags from a File but the output is not as desired:
File > FileTags > Tag
FileTags > TagValue
The above Animals example would result in 2 records:
File: FileA.png, Tag: Animals, TagValue: Dog
File: FileA.png, Tag: Animals, TagValue: Cat
From testing and troubleshooting the current situation I now know that the FileTag entity way will not work for my desired situation.
Is there anyone that ever had to create a similar structure? Or does anyone know how to get to the desired situation?
public class FileTagEntityTypeConfiguration : IEntityTypeConfiguration<FileTag>
{
public void Configure(EntityTypeBuilder<FileTag> builder)
{
builder.HasKey(fileTag => fileTag.Id);
builder.HasIndex(fileTag => new { fileTag.FileId, fileTag.TagId, fileTag.TagValueId });
builder.HasOne<File>(fileTag => fileTag.File)
.WithMany(file => file.Tags)
.HasForeignKey(fileTag => fileTag.FileId)
.OnDelete(DeleteBehavior.Cascade);
builder.HasOne<Tag>(fileTag => fileTag.Tag)
.WithMany(tag => tag.FileTags)
.HasForeignKey(fileTag => fileTag.TagId)
.OnDelete(DeleteBehavior.NoAction);
builder.HasOne<TagValue>(fileTag => fileTag.TagValue)
.WithMany(tagValue => tagValue.FileTags)
.HasForeignKey(fileTag => fileTag.TagValueId)
.OnDelete(DeleteBehavior.NoAction);
builder.Property(fileTags => fileTags.TagValueId)
.IsRequired(false);
}
}
[EntityTypeConfiguration(typeof(FileTagEntityTypeConfiguration))]
public class FileTag
{
public int Id { get; set; }
public required int FileId { get; set; }
public virtual required File File { get; set; }
public required int TagId { get; set; }
public virtual required Tag Tag { get; set; }
public int? TagValueId { get; set; }
public virtual TagValue? TagValue { get; set; }
}
public class File
{
public int Id { get; set; }
public virtual ICollection<FileTag> Tags { get; set; } = new List<FileTag>();
}
public class Tag : ITagContent
{
public int Id { get; set; }
public virtual ICollection<FileTag> FileTags { get; set; }
public virtual ICollection<TagValueCategory> TagValueCategories { get; set; } = new List<TagValueCategory>();
}
[Index(nameof(Name), nameof(TagId), IsUnique = true)]
public class TagValueCategory
{
public int Id { get; set; }
[MaxLength(255)]
public required string Name { get; set; }
public virtual ICollection<TagValue> TagValues { get; set; } = new List<TagValue>();
public virtual int TagId { get; set; }
public virtual required Tag Tag { get; set; }
}
[Index(nameof(Name), nameof(TagValueCategoryId), IsUnique = true)]
public class TagValue
{
public int Id { get; set; }
[MaxLength(255)]
public required string Name { get; set; }
public virtual int TagValueCategoryId { get; set; }
public virtual TagValueCategory TagValueCategory { get; set; }
public virtual ICollection<FileTag> FileTags { get; set; }
}
After changing the code and database layout according to @vonc his answer I have come to the following:
https://imgur.com/a/LzAvLUz https://imgur.com/a/PzijMgn
Overall the structure is the same as before, but now the Tag and TagValue entities are in a separate list and table.
This still does not fix the original reason for my question though. One of the mayor downsides I have with use of Relationship Entities is the output.
The desired output is:
{
"files": [
{
"id": 717,
"name": "file1.jpg",
"tags": [
{
"id": 262,
"name": "product",
"tagValueCategories": [
{
"id": 1,
"name": "Category 1",
"tagValues": [
{
"id": 11105,
"name": "Value 1"
}
]
},
{
"id": 2,
"name": "Category 2",
"tagValues": [
{
"id": 11115,
"name": "Value 1"
},
{
"id": 11108,
"name": "Value 2"
}
]
}
]
}
]
}
]
}
But I currently get the following output:
{
"files": [
{
"id": 717,
"name": "file1.jpg",
"tags": [
{
"tag": {
"id": 289,
"name": "Tag without TagValues",
"tagValueCategories": [
{
"id": 289,
"name": "Uncategorized",
"tagValues": []
}
]
}
}
],
"tagValues": [
{
"tagValue": {
"id": 11105,
"name": "Value 1",
"tagValueCategory": {
"id": 1,
"name": "Category 1"
}
}
},
{
"tagValue": {
"id": 11115,
"name": "Value 1",
"tagValueCategory": {
"id": 2,
"name": "Category 2"
}
}
},
{
"tagValue": {
"id": 11108,
"name": "Value 2",
"tagValueCategory": {
"id": 2,
"name": "Category 2"
}
}
}
]
}
]
}
Is there a way to keep the Relationshop Entities, but somehow make the output reflect the desired output above?
