how to fetch the data in the foreign key using Entity framework core

Question

I am trying to get the categoryName on HttpGet through my foreignKey which is categoryid, here is a picture of my swagger to see what I am trying to achieve:

It should display the categoryName of the Id.

here is my model:

public class ItemTables
{
    [Key]
    public int Id { get; set; }

    public string company { get; set; }

    public string availability { get; set; }

    public decimal price { get; set; }

    public decimal discount { get; set; }

    public decimal tax { get; set; }

    public string description { get; set; }

    public int categoryid { get; set; }

    public categories categories { get; set; }

}

public class categories
{
    [Key]
    public int categoryID { get; set; }

    public string categoryName { get; set; }

    public ICollection<ItemTables> items { get; set; }
}

my context:

public class itemTableDbContext : DbContext
{
    public itemTableDbContext(DbContextOptions options) : base(options)
    {
    }


    public DbSet<ItemTables> ItemTables { get; set; }

    public DbSet<categories> categories { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        modelBuilder.Entity<ItemTables>()
            .HasOne<categories>(c => c.categories)
            .WithMany(i => i.items)
            .HasForeignKey(f => f.categoryid);

        //modelBuilder.Entity<itemTable>().ToTable("itemtable");

        //modelBuilder.Entity<categories>().ToTable("categories");
    }
}

and here is my itemtableController which it has the [HttpGet]:

public class ItemTableController : ControllerBase
{
    private readonly itemTableDbContext _itemTableDbContext;

    public ItemTableController(itemTableDbContext itemTableDbContext)
    {
        _itemTableDbContext = itemTableDbContext;
    }

    [HttpGet]
    public async Task<IActionResult> GetItemsTable()
    {
        var ItemTable = await _itemTableDbContext.ItemTables.ToListAsync();

        return Ok(ItemTable);
    }
    
    [HttpPost]
    public async Task<IActionResult> AddItemTable([FromBody] ItemTables itemTableRequest)
    {
        await _itemTableDbContext.ItemTables.AddAsync(itemTableRequest);
        await _itemTableDbContext.SaveChangesAsync();

        return Ok(itemTableRequest);
    }
}

I hope you got what I am trying to achieve, thank you in advance, if you need more information tell me so :)

Ghassen · Accepted Answer · 2023-01-16 13:23:12Z

3

You have to include categories so it will be retrieved with your items

var ItemTable = await _itemTableDbContext.ItemTables.Include(i=>i.categories).ToListAsync();

you can check this documentation to learn more about the include method https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager

Tips:

Rename the categories class to => Category
public categories categories { get; set; } => public Category Category {get;set;}

answered Jan 16, 2023 at 13:23

Ghassen

1,2363 gold badges17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Firefly0027 Over a year ago

thank you so much! cant believe its that simple

Ghassen Over a year ago

yes because EntityFramework is amazing !!

Collectives™ on Stack Overflow

how to fetch the data in the foreign key using Entity framework core

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related