Entity Framework Core :multiple relations to a table-CodePudding

Using Entity Framework Core 7.0.2 in ASP.NET Core Web API written in C#.

I have multiple tables and they are all connected with one-to-many relation.

See the following tree how all tables are connected.

I just added 2 extra tables which are connected to Trades.

This is how my modal looks like:

public class Trade
{
    [Required][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][Column(TypeName = "varchar(50)")] public string TradingJournalId { get; set; }
    public ICollection<Attachment> Attachments { get; set; }
    public ICollection<Improvement> Improvements { get; set; }
    public ICollection<Tag> Tags { get; set; }
}

Before I added the other 2 tables called Improvements and Tags my query for collecting the user with the relation is the following:

var user = _context.Users
    .Include(u => u.TradingJournal)
    .ThenInclude(t => t.Trades)
    .ThenInclude(f => f.Attachments)
    .SingleOrDefault(u => u.Id == Id);

If I try the following it will result in a error because in will look into the Attachment table.

var user = _context.Users
    .Include(u => u.TradingJournal)
    .ThenInclude(t => t.Trades)
    .ThenInclude(f => f.Attachments)
    .ThenInclude(f => f.Tags)
    .ThenInclude(f => f.Improvements)
    .SingleOrDefault(u => u.Id == Id);

This is my preferable output.

{
    "Id": "114af6db-0124-4573-9ab9-d676f8c05a48",
    "TradingJournal": [
        {
            "Id": "fa0eef8c-16fd-416e-97ec-df6f43c448f2",
            "UserId": "114af6db-0124-4573-9ab9-d676f8c05a48",
            "Trades": [
                {
                    "Id": "4e7ebfd4-54dc-48c7-9e23-01312295f431",
                    "TradingJournalId": "fa0eef8c-16fd-416e-97ec-df6f43c448f2",
                    "Attachments": [
                        {
                            "Id": "362be948-8bc3-4172-ad19-9625bb5aebf4",
                            "TradeId": "4e7ebfd4-54dc-48c7-9e23-01312295f431",
                        }
                    ],
                    "Tags": [
                        {
                            "Id": "362be948-8bc3-4172-ad19-96252b54ebf2",
                            "TradeId": "4e7ebfd4-54dc-48c7-9e23-01312295f431",
                        }
                    ],
                    "Improvements": [
                        {
                            "Id": "362be948-8bc3-4172-ad19-9625bb5ae3f8",
                            "TradeId": "4e7ebfd4-54dc-48c7-9e23-01312295f431",
                        }
                    ],
                }
            ]
        }
    ]
}

At the moment the Tags and Improvements are both null.

I got no clue how I need to solve this by adding a reference to the other 2 tables. I did some research how to do it but my case is much more complicated due the multiple relations hence the help I need.

Edit:

Here are all the entities modals.

public class User
{
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    public ICollection<TradingJournal> TradingJournal { get; set; }
}

public class TradingJournal
{
    [Required][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][Column(TypeName = "varchar(50)")] public string UserId { get; set; }
    public ICollection<Trade> Trades { get; set; }
}

public class Trade
{
    [Required][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][Column(TypeName = "varchar(50)")] public string TradingJournalId { get; set; }
    public ICollection<Attachment> Attachments { get; set; }
    public ICollection<Improvement> Improvements { get; set; }
    public ICollection<Tag> Tags { get; set; }
}

public class Attachment
{
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string TradeId { get; set; }
}

public class Improvement
{
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string TradeId { get; set; }
}

public class Tags
{
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string Id { get; set; }
    [Required][StringLength(50)][Column(TypeName = "varchar(50)")] public string TradeId { get; set; }
}

I have removed all the irrelevant properties in the modals which aren't needed for the reference tables so that this code is much more readable.

CodePudding user response：

ThenInclude allows to include properties of "previous" entity, so in this case it would be Attachments which have not Tags or Improvements. AFAIK EF Core still does not allow "walk backs" so try the following:

var user = _context.Users
    .Include(u => u.TradingJournal)
        .ThenInclude(t => t.Trades)
            .ThenInclude(f => f.Attachments)
    .Include(u => u.TradingJournal)
        .ThenInclude(t => t.Trades)
            .ThenInclude(f => f.Tags)
    .Include(u => u.TradingJournal)
        .ThenInclude(t => t.Trades)
            .ThenInclude(f => f.Improvements)
    .SingleOrDefault(u => u.Id == Id);

Check out the Including multiple levels section of eager loading docs.