Retrieve data from multiple database tables to API in ASP.NET Core

Question

I have a database in SQL Server stored locally with a number of different tables, all connected together by different types of relations (one-to-many and many-to-many). You can see some of theme like this:

I created a Web API for these with Entity Framework on VS 2019.

My model looks like:

namespace OneClickAPI.Models
{
    public partial class ProductsTbl
    {
        public ProductsTbl()
        {
            BuyBillDetalisTbl = new HashSet<BuyBillDetalisTbl>();
            ItemMovmentTbl = new HashSet<ItemMovmentTbl>();
            ItemStoresTbl = new HashSet<ItemStoresTbl>();
            SaleBillDetialsTbl = new HashSet<SaleBillDetialsTbl>();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public int? Qty { get; set; }
        public decimal? BuyPrice { get; set; }
        public decimal? SalePice { get; set; }
        public decimal? SaleGomla { get; set; }
        public decimal? AvgCost { get; set; }
        public int? QtyLimite { get; set; }
        public decimal? SaleLimite { get; set; }
        public int? CatId { get; set; }
        public int? BranchId { get; set; }

        public BranchTbl Branch { get; set; }
        public CatTbl Cat { get; set; }
        public ICollection<BuyBillDetalisTbl> BuyBillDetalisTbl { get; set; }
        public ICollection<ItemMovmentTbl> ItemMovmentTbl { get; set; }
        public ICollection<ItemStoresTbl> ItemStoresTbl { get; set; }
        public ICollection<SaleBillDetialsTbl> SaleBillDetialsTbl { get; set; }
    }
}

but the problem is my API doesn't fetch data from connected tables

[
{
"id": 1002,
"name": "item 1",
"qty": 30,
"buyPrice": 22,
"salePice": 27,
"saleGomla": 25,
"avgCost": 22,
"qtyLimite": 5,
"saleLimite": 22,
"catId": 1,
"branchId": null,
"branch": null,
"cat": null,
"buyBillDetalisTbl": [],
"itemMovmentTbl": [],
"itemStoresTbl": [],
"saleBillDetialsTbl": []
}
]

So how to join these tables using Entity Framework to be the API get all data from connected tables?

Update:

The exception

       System.NullReferenceException
  HResult=0x80004003
  Message=Object reference not set to an instance of an object.

Update 2 ProductsTbl:

namespace OneClickAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [EnableCors("enableCors")]
    public class ProductsController : ControllerBase
    {
        private readonly OneClickDBContext _context;

        public ProductsController(OneClickDBContext context)
        {
            _context = context;
        }

        // GET: api/Products
        [HttpGet]
        public IEnumerable<ProductsTbl> GetProductsTbl()
        {
            var products = _context.ProductsTbl
               .Include(p => p.Branch)
               .Include(p => p.Cat)
               .Include(p => p.BuyBillDetalisTbl)
               .Include(p => p.ItemMovmentTbl)
               .Include(p => p.ItemStoresTbl)
               .Include(p => p.SaleBillDetialsTbl)
               .ToList();
            return _context.ProductsTbl;
        }

        // GET: api/Products/5
        [HttpGet("{id}")]
        public async Task<IActionResult> GetProductsTbl([FromRoute] int id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var productsTbl = await _context.ProductsTbl.FindAsync(id);

            if (productsTbl == null)
            {
                return NotFound();
            }

            return Ok(productsTbl);
        }

        // PUT: api/Products/5
        [HttpPut("{id}")]
        public async Task<IActionResult> PutProductsTbl([FromRoute] int id, [FromBody] ProductsTbl productsTbl)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (id != productsTbl.Id)
            {
                return BadRequest();
            }

            _context.Entry(productsTbl).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!ProductsTblExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: api/Products
        [HttpPost]
        public async Task<IActionResult> PostProductsTbl([FromBody] ProductsTbl productsTbl)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            _context.ProductsTbl.Add(productsTbl);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetProductsTbl", new { id = productsTbl.Id }, productsTbl);
        }

        // DELETE: api/Products/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProductsTbl([FromRoute] int id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var productsTbl = await _context.ProductsTbl.FindAsync(id);
            if (productsTbl == null)
            {
                return NotFound();
            }

            _context.ProductsTbl.Remove(productsTbl);
            await _context.SaveChangesAsync();

            return Ok(productsTbl);
        }

        private bool ProductsTblExists(int id)
        {
            return _context.ProductsTbl.Any(e => e.Id == id);
        }
    }
}

BranchTbl:

...
namespace OneClickAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [EnableCors("enableCors")]
    public class BranchController : ControllerBase
    {
        private readonly OneClickDBContext _context;

        public BranchController(OneClickDBContext context)
        {
            _context = context;
        }

        // GET: api/Branch
        [HttpGet]
        public IEnumerable<BranchTbl> GetBranchTbl()
        {
            return _context.BranchTbl;
        }

        // GET: api/Branch/5
        [HttpGet("{id}")]
        public async Task<IActionResult> GetBranchTbl([FromRoute] int id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var branchTbl = await _context.BranchTbl.FindAsync(id);

            if (branchTbl == null)
            {
                return NotFound();
            }

            return Ok(branchTbl);
        }

        // PUT: api/Branch/5
        ...
}

CatTbl

...
namespace OneClickAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [EnableCors("enableCors")]
    public class CatController : ControllerBase
    {
        private readonly OneClickDBContext _context;

        public CatController(OneClickDBContext context)
        {
            _context = context;
        }

        // GET: api/Cat
        [HttpGet]
        public IEnumerable<CatTbl> GetCatTbl()
        {
            return _context.CatTbl;
        }

        // GET: api/Cat/5
        [HttpGet("{id}")]
        public async Task<IActionResult> GetCatTbl([FromRoute] int id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var catTbl = await _context.CatTbl.FindAsync(id);

            if (catTbl == null)
            {
                return NotFound();
            }

            return Ok(catTbl);
        }

        // PUT: api/Cat/5
        ...
}

DBContext

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace OneClickAPI.Models
{
    public partial class OneClickDBContext : DbContext
    {
        public OneClickDBContext()
        {
        }

        public OneClickDBContext(DbContextOptions<OneClickDBContext> options)
            : base(options)
        {
        }

        public virtual DbSet<BillTybsTbl> BillTybsTbl { get; set; }
        public virtual DbSet<BranchTbl> BranchTbl { get; set; }
        public virtual DbSet<BuyBillDetalisTbl> BuyBillDetalisTbl { get; set; }
        public virtual DbSet<BuyBillHeaderTbl> BuyBillHeaderTbl { get; set; }
        public virtual DbSet<CatTbl> CatTbl { get; set; }
        public virtual DbSet<ClintsTbl> ClintsTbl { get; set; }
        public virtual DbSet<ExpensesTbl> ExpensesTbl { get; set; }
        public virtual DbSet<ItemMovmentTbl> ItemMovmentTbl { get; set; }
        public virtual DbSet<ItemStoresTbl> ItemStoresTbl { get; set; }
        public virtual DbSet<ProductsTbl> ProductsTbl { get; set; }
        public virtual DbSet<RulesTbl> RulesTbl { get; set; }
        public virtual DbSet<SafeTbl> SafeTbl { get; set; }
        public virtual DbSet<SaleBillDetialsTbl> SaleBillDetialsTbl { get; set; }
        public virtual DbSet<SaleBillHeaderTbl> SaleBillHeaderTbl { get; set; }
        public virtual DbSet<StoreSectionTbl> StoreSectionTbl { get; set; }
        public virtual DbSet<StoresTbl> StoresTbl { get; set; }
        public virtual DbSet<StoreTransTbl> StoreTransTbl { get; set; }
        public virtual DbSet<SupplersTbl> SupplersTbl { get; set; }
        public virtual DbSet<UsersGroupsTbl> UsersGroupsTbl { get; set; }
        public virtual DbSet<UsersTbl> UsersTbl { get; set; }
        public virtual DbSet<ViewsTbl> ViewsTbl { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer("Server=.\\SQLEXPRESS;Database=OneClickDB;Integrated Security=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<BillTybsTbl>(entity =>
            {
                entity.ToTable("billTybsTbl");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.BranchId).HasColumnName("branchId");

                entity.Property(e => e.Name)
                    .HasColumnName("name")
                    .HasMaxLength(50);

                entity.Property(e => e.Nots)
                    .HasColumnName("nots")
                    .HasMaxLength(200);

                entity.HasOne(d => d.Branch)
                    .WithMany(p => p.BillTybsTbl)
                    .HasForeignKey(d => d.BranchId)
                    .HasConstraintName("FK_billTybsTbl_branchTbl");
            });

            modelBuilder.Entity<BranchTbl>(entity =>
            {
                entity.ToTable("branchTbl");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.Master)
                    .HasColumnName("master")
                    .HasMaxLength(50);

                entity.Property(e => e.Name)
                    .HasColumnName("name")
                    .HasMaxLength(50);

                entity.Property(e => e.Notes)
                    .HasColumnName("notes")
                    .HasMaxLength(50);
            });



            modelBuilder.Entity<CatTbl>(entity =>
            {
                entity.ToTable("catTbl");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.BranchId).HasColumnName("branchID");

                entity.Property(e => e.Name)
                    .HasColumnName("name")
                    .HasMaxLength(50);

                entity.Property(e => e.Notes)
                    .HasColumnName("notes")
                    .HasMaxLength(50);

                entity.Property(e => e.Printer)
                    .HasColumnName("printer")
                    .HasMaxLength(50);

                entity.Property(e => e.Touch)
                    .HasColumnName("touch")
                    .HasMaxLength(50);

                entity.HasOne(d => d.Branch)
                    .WithMany(p => p.CatTbl)
                    .HasForeignKey(d => d.BranchId)
                    .HasConstraintName("FK_catTbl_branchTbl");
            });


            modelBuilder.Entity<ProductsTbl>(entity =>
            {
                entity.ToTable("productsTbl");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.AvgCost).HasColumnName("avgCost");

                entity.Property(e => e.BranchId).HasColumnName("branchID");

                entity.Property(e => e.BuyPrice).HasColumnName("buyPrice");

                entity.Property(e => e.CatId).HasColumnName("catID");

                entity.Property(e => e.Name)
                    .HasColumnName("name")
                    .HasMaxLength(50);

                entity.Property(e => e.Qty).HasColumnName("qty");

                entity.Property(e => e.QtyLimite).HasColumnName("qtyLimite");

                entity.Property(e => e.SaleGomla).HasColumnName("saleGomla");

                entity.Property(e => e.SaleLimite).HasColumnName("saleLimite");

                entity.Property(e => e.SalePice).HasColumnName("salePice");

                entity.HasOne(d => d.Branch)
                    .WithMany(p => p.ProductsTbl)
                    .HasForeignKey(d => d.BranchId)
                    .HasConstraintName("FK_productsTbl_branchTbl");

                entity.HasOne(d => d.Cat)
                    .WithMany(p => p.ProductsTbl)
                    .HasForeignKey(d => d.CatId)
                    .HasConstraintName("FK_productsTbl_catTbl");
            });
...//I delete other tabels to minmize code 
    }
}

Note I deleted other tables to minimize code and to be readable

Thanks a lot, I already did it but face this error:An unhandled exception occurred while processing the request. — Mohamed Elkast
– Mohamed Elkast, Commented Dec 30, 2019 at 8:39
I'm sure the foreign key is set correctly and you can check the update for more details — Mohamed Elkast
– Mohamed Elkast, Commented Dec 30, 2019 at 9:59

Mukesh Modhvadiya · Accepted Answer · 2019-12-30 05:59:48Z

1

You can use Include to load data from other related tables. E.g.

var products = _context.ProductsTbl
              .Include(p => p.branchTbl)
              .Include(p => p.catTbl)
              .ToList();

Please check Loading Related Data, which explains different methods to load related table data.

answered Dec 30, 2019 at 5:59

Mukesh Modhvadiya

2,1982 gold badges28 silver badges32 bronze badges

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

11 Comments

Mohamed Elkast Over a year ago

Thanks a lot for your support and excuse me I'm still beginner could you tell me where I should write this code? sorry for the stupid query

Mukesh Modhvadiya Over a year ago

No problem, wherever you need to load related data, i.e. in your GetProductsTbl() method.

Mohamed Elkast Over a year ago

Sorry for this now I have this An unhandled exception occurred while processing the request.

Mukesh Modhvadiya Over a year ago

put debug point in your controller and see where error occures and do you get value in products variable after the call?

Mohamed Elkast Over a year ago

the error is here:` var products = _context.ProductsTblّ

|

Collectives™ on Stack Overflow

Retrieve data from multiple database tables to API in ASP.NET Core

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related