How to fetch record of more than one table in ASP.NET Core API Controller

Question

I want to fetch 3 table from database just all values from 3 table.

This api is in Sales Controller where I want to fetch 3 tables Customer,Product and Store so That I can use that in form.

Here is Database Context

namespace CRUDReact.Model
{
public class DataBaseContext: DbContext
{
    public DataBaseContext(DbContextOptions<DataBaseContext> options) : base(options) { }

    public DbSet<Customer> Customer { get; set; }
    public DbSet<Sales> Sales { get; set; }
    public DbSet<Product> Product { get; set; }
    public DbSet<Store> Store { get; set; }

}
}

Here is the api that I want to use to fetch 3 tables. This api is in Sales controller.

   // GET: api/AllSales
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Object>>> GetAllSales()
    {
        var salesdata = await _context.Sales
                 .Include(s => s.Customer)
                 .Include(s => s.Product)
                 .Include(s => s.Store)
                 .Select(s => new
                 {
                     salesId = s.SalesId,
                     dateSold = s.DateSold,
                     customer = new
                     {
                         customerId = s.CustomerRefId,
                         name = s.Customer.Name,
                         address = s.Customer.Address
                     },
                     product = new
                     {
                         productId = s.ProductRefId,
                         name = s.Product.Name,
                         price = s.Product.Price
                     },
                     store = new
                     {
                         storeId = s.StoreRefId,
                         name = s.Store.Name,
                         address = s.Store.Address
                     }
                 })
                 .ToListAsync();
        return salesdata;
    }

Here is the Sales class

namespace CRUDReact.Models
{
public class Sales
{
    [Key]
    public int SalesId { get; set; }

    public int ProductRefId { get; set; }
    [ForeignKey("ProductRefId")]
    public Product Product { get; set; }

    public int CustomerRefId { get; set; }
    [ForeignKey("CustomerRefId")]
    public Customer Customer { get; set; }

    public int StoreRefId { get; set; }
    [ForeignKey("StoreRefId")]
    public Store Store { get; set; }

    [DataType(DataType.Date)]
    public string DateSold { get; set; }
}
}

Answer 1

Fetching Sales with Include will result in Getting Data from Child Tables. Why are you projecting it.

var salesdata = await _context.Sales
             .Include(s => s.Customer)
             .Include(s => s.Product)
             .Include(s => s.Store).ToListAsync();

Also normally Sale will have Store, Customer and List of Products, are you sure your model is correct.