C#•3y ago

[Entity Framework] Issues with .Include()

Hi all! I have some issues trying to get data from my SQL Db. Having 3 models (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 List<SetContent> Sets { get; set; } = new List<SetContent>();
    }

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 List<SetContent> Sets { get; set; } = new List<SetContent>();
    }

I have 3 pages: - one to display all Sets - one to display Content of specific Set - one to display the specific Item details 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 my question is how should I form the call in order to get something like Item with SetContent with Set (name and releaseDate):

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

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

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

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

15 Replies

Angius•3y ago

Ewww, virtual Technically, fetching an item from Items and using .Include(i => i.Sets) would just work But I'd recommend opting for .Select() instead

Unknown User•3y ago

Message Not Public

AvalariOP•3y ago

anything bad with using virtual? 😄 forgot to mentioned that I've tried it since the beginning and even with:

builder.Services.AddControllersWithViews()
                .AddJsonOptions(x => x.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles);

builder.Services.AddControllersWithViews()
                .AddJsonOptions(x => x.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles);

it thrown me into a loop of Item -> Sets -> Item -> Sets=null

Angius•3y ago

It implies you're using lazy loading, which is generally not a great idea Selecting instead of including should help

Starzy•3y ago

What's wrong with lazy loading though? Eager fetching can be pretty bad for performance Unless you're meaning return anonymous dtos as a better alternative or something?

Angius•3y ago

Not anonymous, yes DTOs And in most cases you'll get better performance out of fetching, say, a blogpost and its associated tags in one go, rather then querying the database for the blogpost, and then querying it again for each tag

D.Mentia•3y ago

This would be because of lazy loading 😛 When you explicitly load, you don't have those problems In this case your SetContent should contain a Set (as well as the SetId), which doesn't do anything in the db, just makes it easier to work with on the code side Then you can do Context.Items.Include(i => i.Sets).ThenInclude(s => s.Set)

AvalariOP•3y ago

I went with @Angius's rout and just formed a nested Select I get the intended return without obsolete data

D.Mentia•3y ago

Yep that works too

AvalariOP•3y ago

also tried:

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

            _context.Entry(item).Collection(s => s.Sets)
                .Query()
                .Include(b => b.Set)
                .Load();

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

            _context.Entry(item).Collection(s => s.Sets)
                .Query()
                .Include(b => b.Set)
                .Load();

Angius•3y ago

That only queries the database again for little to no reason So, yeah, good you went with .Select()

AvalariOP•3y ago

@Angius thanks for helping out (y) tho should I get rid of Virtual tags in case of Selecting what I want?

Angius•3y ago

Unknown User•3y ago

Message Not Public

Angius•3y ago

.Include() is eager, yes, but virtual allows for lazy loading to be used If you're using it, don't If you aren't, why have virtual there One way or another, it's unnecessary

Gaming

Programming

[Entity Framework] Issues with .Include()