ASP.NET Core & Entity Framework Core : one-to-many relation and navigation properties

asp.net-core entity-framework-core sql-server

Question

I am using ASP.NET Core and Entity Framework Core, and controller API for my react app database connect.

I have 4 classes Customer, Product, Store and Sales. The Customer, Product and Store table have a one-to-many relation with sales.

Here is the image of database relation which I am trying to implement

Sales class

public class Sales
{
    [Key]
    public int SalesId { get; set; }
    public int ProductId { get; set; }
    public int CustomerId { get; set; }
    public int StoreId { get; set; }
    [DataType(DataType.Date)]
    public string DateSold { get; set; }

    public Product Product { get; set; }
    public Customer Customer { get; set; }
    public Store Store { get; set; }
}

Customer class

public class Customer
{
    [Key]
    public int CustomerId { get; set; }
    [Column(TypeName = "nvarchar(100)")]
    public string Name { get; set; }
    [Column(TypeName = "nvarchar(100)")]
    public string Address { get; set; }

    public IList<Sales> Sales { get; set; }
}

The other Product and store are same as customer class.

I run the migration command but the database was not created and command run successful so I created database and then I run the update-database which created all the tables in database.

If I add navigation properties to table will it fetch Sales record with Customer, Product and Store record as per ID in sales record.

I want to fetch sales record and in the sales table there is customer, product and store ID. How can I fetch their record?

My tables look like this:

My database diagram

1
0
3/16/2020 9:23:08 PM

Accepted Answer

I want to fetch sales record and in the sales table there is customer, product and store ID. How can I fetch their record?

Based on your model design , you could use Include method for loading related data like below:

Controller

    [HttpGet]
    public async Task<ActionResult<object>> GetSales() 
    {
        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
                        {
                            name = s.Customer.Name,
                            address = s.Customer.Address
                        },
                        product = new
                        {
                            name = s.Product.Name,
                            price = s.Product.Price
                        },
                        store = new
                        {
                            name = s.Store.Name,
                            address = s.Store.Address
                        }
                    })
                    .ToListAsync();
        return salesdata; 
    }

For query syntax , you could use the Join in EF Core for Complex Query Operators

var data = from s in _context.Sales
                   join cu in _context.Customer on s.CustomerId equals cu.CustomerId 
                   join p in _context.Product on s.ProductId equals p.ProductId 
                   join st in _context.Store on s.StoreId equals st.StoreId 
                   select new
                   {
                       salesId = s.SalesId,
                       dateSold = s.DateSold,
                       customer = new
                       {
                           name = s.Customer.Name,
                           address = s.Customer.Address
                       },
                       product = new
                       {
                           name = s.Product.Name,
                           price = s.Product.Price
                       },
                       store = new
                       {
                           name = s.Store.Name,
                           address = s.Store.Address
                       }
                   };
        return await data.ToListAsync();     

Result enter image description here

1
3/17/2020 6:02:42 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow