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

Time:01-17

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:

picture

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 :)

CodePudding user response:

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;}
  • Related