title: Retrieve data from multiple database tables to API in ASP.NET Core
body: <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/ObIAG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ObIAG.png" alt="enter image description here"></a> </p>
  
  <p>I created a Web API for these with Entity Framework on VS 2019. </p>
  
  <p>My model looks like:</p>
  
  <pre><code>namespace OneClickAPI.Models
  {
      public partial class ProductsTbl
      {
          public ProductsTbl()
          {
              BuyBillDetalisTbl = new HashSet&lt;BuyBillDetalisTbl&gt;();
              ItemMovmentTbl = new HashSet&lt;ItemMovmentTbl&gt;();
              ItemStoresTbl = new HashSet&lt;ItemStoresTbl&gt;();
              SaleBillDetialsTbl = new HashSet&lt;SaleBillDetialsTbl&gt;();
          }
  
          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&lt;BuyBillDetalisTbl&gt; BuyBillDetalisTbl { get; set; }
          public ICollection&lt;ItemMovmentTbl&gt; ItemMovmentTbl { get; set; }
          public ICollection&lt;ItemStoresTbl&gt; ItemStoresTbl { get; set; }
          public ICollection&lt;SaleBillDetialsTbl&gt; SaleBillDetialsTbl { get; set; }
      }
  }
  
  </code></pre>
  
  <p>but the problem is my API doesn't fetch data from connected tables </p>
  
  <pre><code>[
  {
  "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": []
  }
  ]
  </code></pre>
  
  <p>So how to join these tables using Entity Framework to be the API get all data from connected tables?</p>
  
  <p><strong>Update:</strong></p>
  
  <p><em>The exception</em></p>
  
  <pre><code>       System.NullReferenceException
    HResult=0x80004003
    Message=Object reference not set to an instance of an object.
  
  </code></pre>
  
  <p><strong>Update 2</strong>
  <strong>ProductsTbl</strong>:</p>
  
  <pre><code>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&lt;ProductsTbl&gt; GetProductsTbl()
          {
              var products = _context.ProductsTbl
                 .Include(p =&gt; p.Branch)
                 .Include(p =&gt; p.Cat)
                 .Include(p =&gt; p.BuyBillDetalisTbl)
                 .Include(p =&gt; p.ItemMovmentTbl)
                 .Include(p =&gt; p.ItemStoresTbl)
                 .Include(p =&gt; p.SaleBillDetialsTbl)
                 .ToList();
              return _context.ProductsTbl;
          }
  
          // GET: api/Products/5
          [HttpGet("{id}")]
          public async Task&lt;IActionResult&gt; 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&lt;IActionResult&gt; 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&lt;IActionResult&gt; 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&lt;IActionResult&gt; 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 =&gt; e.Id == id);
          }
      }
  }
  </code></pre>
  
  <p><strong>BranchTbl</strong>:</p>
  
  <pre><code>...
  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&lt;BranchTbl&gt; GetBranchTbl()
          {
              return _context.BranchTbl;
          }
  
          // GET: api/Branch/5
          [HttpGet("{id}")]
          public async Task&lt;IActionResult&gt; 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
          ...
  }
  </code></pre>
  
  <p><strong>CatTbl</strong></p>
  
  <pre><code>...
  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&lt;CatTbl&gt; GetCatTbl()
          {
              return _context.CatTbl;
          }
  
          // GET: api/Cat/5
          [HttpGet("{id}")]
          public async Task&lt;IActionResult&gt; 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
          ...
  }
  </code></pre>
  
  <p><strong>DBContext</strong></p>
  
  <pre><code>using System;
  using Microsoft.EntityFrameworkCore;
  using Microsoft.EntityFrameworkCore.Metadata;
  
  namespace OneClickAPI.Models
  {
      public partial class OneClickDBContext : DbContext
      {
          public OneClickDBContext()
          {
          }
  
          public OneClickDBContext(DbContextOptions&lt;OneClickDBContext&gt; options)
              : base(options)
          {
          }
  
          public virtual DbSet&lt;BillTybsTbl&gt; BillTybsTbl { get; set; }
          public virtual DbSet&lt;BranchTbl&gt; BranchTbl { get; set; }
          public virtual DbSet&lt;BuyBillDetalisTbl&gt; BuyBillDetalisTbl { get; set; }
          public virtual DbSet&lt;BuyBillHeaderTbl&gt; BuyBillHeaderTbl { get; set; }
          public virtual DbSet&lt;CatTbl&gt; CatTbl { get; set; }
          public virtual DbSet&lt;ClintsTbl&gt; ClintsTbl { get; set; }
          public virtual DbSet&lt;ExpensesTbl&gt; ExpensesTbl { get; set; }
          public virtual DbSet&lt;ItemMovmentTbl&gt; ItemMovmentTbl { get; set; }
          public virtual DbSet&lt;ItemStoresTbl&gt; ItemStoresTbl { get; set; }
          public virtual DbSet&lt;ProductsTbl&gt; ProductsTbl { get; set; }
          public virtual DbSet&lt;RulesTbl&gt; RulesTbl { get; set; }
          public virtual DbSet&lt;SafeTbl&gt; SafeTbl { get; set; }
          public virtual DbSet&lt;SaleBillDetialsTbl&gt; SaleBillDetialsTbl { get; set; }
          public virtual DbSet&lt;SaleBillHeaderTbl&gt; SaleBillHeaderTbl { get; set; }
          public virtual DbSet&lt;StoreSectionTbl&gt; StoreSectionTbl { get; set; }
          public virtual DbSet&lt;StoresTbl&gt; StoresTbl { get; set; }
          public virtual DbSet&lt;StoreTransTbl&gt; StoreTransTbl { get; set; }
          public virtual DbSet&lt;SupplersTbl&gt; SupplersTbl { get; set; }
          public virtual DbSet&lt;UsersGroupsTbl&gt; UsersGroupsTbl { get; set; }
          public virtual DbSet&lt;UsersTbl&gt; UsersTbl { get; set; }
          public virtual DbSet&lt;ViewsTbl&gt; 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&lt;BillTybsTbl&gt;(entity =&gt;
              {
                  entity.ToTable("billTybsTbl");
  
                  entity.Property(e =&gt; e.Id).HasColumnName("id");
  
                  entity.Property(e =&gt; e.BranchId).HasColumnName("branchId");
  
                  entity.Property(e =&gt; e.Name)
                      .HasColumnName("name")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Nots)
                      .HasColumnName("nots")
                      .HasMaxLength(200);
  
                  entity.HasOne(d =&gt; d.Branch)
                      .WithMany(p =&gt; p.BillTybsTbl)
                      .HasForeignKey(d =&gt; d.BranchId)
                      .HasConstraintName("FK_billTybsTbl_branchTbl");
              });
  
              modelBuilder.Entity&lt;BranchTbl&gt;(entity =&gt;
              {
                  entity.ToTable("branchTbl");
  
                  entity.Property(e =&gt; e.Id).HasColumnName("id");
  
                  entity.Property(e =&gt; e.Master)
                      .HasColumnName("master")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Name)
                      .HasColumnName("name")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Notes)
                      .HasColumnName("notes")
                      .HasMaxLength(50);
              });
  
  
  
              modelBuilder.Entity&lt;CatTbl&gt;(entity =&gt;
              {
                  entity.ToTable("catTbl");
  
                  entity.Property(e =&gt; e.Id).HasColumnName("id");
  
                  entity.Property(e =&gt; e.BranchId).HasColumnName("branchID");
  
                  entity.Property(e =&gt; e.Name)
                      .HasColumnName("name")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Notes)
                      .HasColumnName("notes")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Printer)
                      .HasColumnName("printer")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Touch)
                      .HasColumnName("touch")
                      .HasMaxLength(50);
  
                  entity.HasOne(d =&gt; d.Branch)
                      .WithMany(p =&gt; p.CatTbl)
                      .HasForeignKey(d =&gt; d.BranchId)
                      .HasConstraintName("FK_catTbl_branchTbl");
              });
  
  
              modelBuilder.Entity&lt;ProductsTbl&gt;(entity =&gt;
              {
                  entity.ToTable("productsTbl");
  
                  entity.Property(e =&gt; e.Id).HasColumnName("id");
  
                  entity.Property(e =&gt; e.AvgCost).HasColumnName("avgCost");
  
                  entity.Property(e =&gt; e.BranchId).HasColumnName("branchID");
  
                  entity.Property(e =&gt; e.BuyPrice).HasColumnName("buyPrice");
  
                  entity.Property(e =&gt; e.CatId).HasColumnName("catID");
  
                  entity.Property(e =&gt; e.Name)
                      .HasColumnName("name")
                      .HasMaxLength(50);
  
                  entity.Property(e =&gt; e.Qty).HasColumnName("qty");
  
                  entity.Property(e =&gt; e.QtyLimite).HasColumnName("qtyLimite");
  
                  entity.Property(e =&gt; e.SaleGomla).HasColumnName("saleGomla");
  
                  entity.Property(e =&gt; e.SaleLimite).HasColumnName("saleLimite");
  
                  entity.Property(e =&gt; e.SalePice).HasColumnName("salePice");
  
                  entity.HasOne(d =&gt; d.Branch)
                      .WithMany(p =&gt; p.ProductsTbl)
                      .HasForeignKey(d =&gt; d.BranchId)
                      .HasConstraintName("FK_productsTbl_branchTbl");
  
                  entity.HasOne(d =&gt; d.Cat)
                      .WithMany(p =&gt; p.ProductsTbl)
                      .HasForeignKey(d =&gt; d.CatId)
                      .HasConstraintName("FK_productsTbl_catTbl");
              });
  ...//I delete other tabels to minmize code 
      }
  }
  
  </code></pre>
  
  <p>Note 
  I deleted other tables to minimize code and to be readable</p>