CODEWITHSUNDEEP
c#sqldatabaseentity-framework
Avalari
Avalari

Reputation: 37

How to .Include() multiple tables properly using Entity Framework

I have some issues trying to get data from my SQL database.

Having 3 model classes, Set, SetContent and Item:

public class Set
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }
    public DateTime? ReleaseDate { get; set; }
    public string Type { get; set; }
    public string? Series { get; set; }
    public string? Description { get; set; }

    public ICollection<SetContent> Content { get; set; }
}

public class SetContent
{
    public int Id { get; set; }
    public int SetId { get; set; }
    public string? SetCode { get; set; }
    public string? SetRarity { get; set; }
    public string? SetRarityCode { get; set; }
    public int ItemId { get; set; }
    public virtual Item Item { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
    public string Desc { get; set; }
    public string ImageBig { get; set; }
    public string ImageSmall { get; set; }

    public ICollection<SetContent> Sets { get; set; } = new List<SetContent>();
}

I have 3 pages:

The issue that I have is with the third one.

For the Item view page I would like to have a segment showing in what Set you can find it - so a combination of Set and SetContent.

I've tried using:

var card = _mapper.Map<ItemReadOnlyDto>(await _context.Items
            .Include(_c => _c.Sets)
            .FirstOrDefaultAsync(o => o.Id == id));

but that didn't work out.

I'm able to get the data from SetContent using:

var item = await _context.Items.Where(s => s.Id == id).FirstOrDefaultAsync<Item>();
_context.Entry(item).Collection(s => s.Sets).Load();

but it has some obsolete data and reference loop nulls.

So the question here is how to, by querying the Item, get the data from SetContent and afterwards using setId from SetContent get the data from Set.

Something like this:

{
   "id": 0,
   "name": "string",
   "type": "string",
   "desc": "string",
   "imageBig": "string",
   "imageSmall": "string",
   "sets": [
   {
     "id": 0,
     "setId": 0,
     "setCode": "string",
     "setRarity": "string",
     "setRarityCode": "string",

     "SetName": "string",
     "ReleaseDate": "DateTime"
   }
   ]
}

Upvotes: 0

Views: 715

Answers (1)

Xerillio
Xerillio

Reputation: 5261

I think you want to add a navigation property from SetContent to Set:

// Removed other properties for brevity
public class SetContent
{
    public int Id { get; set; }

    public int SetId { get; set; }
    public virtual Set Set { get; set; }

    public int ItemId { get; set; }
    public virtual Item Item { get; set; }
}

Then you can use the ThenInclude method:

var card = _mapper.Map<ItemReadOnlyDto>(await _context.Items
            .Include(_c => _c.Sets)
                .ThenInclude(sc => sc.Set)
            .FirstOrDefaultAsync(o => o.Id == id));

And of course add some mapping for item.Sets[n].Set.

Upvotes: 1

Related Questions