C#•3mo ago

✅ Help understanding how to get foreign relation using Entity Framework core.

Hi, I want to be able to join two tables to get all words in a category using entity framework core (whatever the latest version is). I have a sqlite3 database with two tables, words and categories. This is their schema: categories: name, id. words: id, text, categoryid (foreign key), length. In plain sql joining them would be easy, just something like (or with join/innerjoin)

select * from words, categories where words.categoryid = categories.id;

select * from words, categories where words.categoryid = categories.id;

when I try

 [HttpGet]
    public async Task<ActionResult<IEnumerable<Category>>> Get()
    {
        return await _db.Categories.Include(w => w.Words).ToListAsync();
    }

 [HttpGet]
    public async Task<ActionResult<IEnumerable<Category>>> Get()
    {
        return await _db.Categories.Include(w => w.Words).ToListAsync();
    }

I get this error.

System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles. Path: $.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Name.

System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles. Path: $.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Words.Category.Name.

This is the on model creating

   modelBuilder.Entity<Word>(entity =>
        {
            entity.HasOne(d => d.Category).WithMany(p => p.Words).OnDelete(DeleteBehavior.ClientSetNull);
        });

   modelBuilder.Entity<Word>(entity =>
        {
            entity.HasOne(d => d.Category).WithMany(p => p.Words).OnDelete(DeleteBehavior.ClientSetNull);
        });

these are the class models

[Table("categories")]
public partial class Category
{
    [Column("name")]
    public string Name { get; set; } = null!;

    [Key]
    [Column("id")]
    public int Id { get; set; }

    [InverseProperty("Category")]
    public virtual ICollection<Word> Words { get; set; } = new List<Word>();
}

[Table("categories")]
public partial class Category
{
    [Column("name")]
    public string Name { get; set; } = null!;

    [Key]
    [Column("id")]
    public int Id { get; set; }

    [InverseProperty("Category")]
    public virtual ICollection<Word> Words { get; set; } = new List<Word>();
}

[Table("words")]
public partial class Word
{
    //normal word columns

    [ForeignKey("Categoryid")]
    [InverseProperty("Words")]
    public virtual Category Category { get; set; } = null!;
}

[Table("words")]
public partial class Word
{
    //normal word columns

    [ForeignKey("Categoryid")]
    [InverseProperty("Words")]
    public virtual Category Category { get; set; } = null!;
}