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.
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:
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow