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

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:

There's a couple of ways to do this, but I like using the Foreign Key which will tie the ids to the "virtual" tables: entityframeworktutorial.net/code-first/… (you should make the ints nullable... so "public int? ProductId { get; set; }") — browsermator
– browsermator, Commented Mar 16, 2020 at 21:12
@Dale K I do tried this [ForeignKey] on the navigation property in the dependent entity but it did not created the navigation properties. I will try the principal — Rahul Waghmare
– Rahul Waghmare, Commented Mar 16, 2020 at 21:20
@Dale K I tried [ForeignKey] on the navigation property in the dependent entity and now I have Foreign key in Sales table but not navigation properties — Rahul Waghmare
– Rahul Waghmare, Commented Mar 16, 2020 at 21:34
I think you meant to tag @pcalkins as I haven't contributed to this question. — Dale K
– Dale K, Commented Mar 16, 2020 at 21:34

Xueli Chen · Accepted Answer · 2020-03-17 06:02:42Z

1

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

edited Mar 17, 2020 at 6:02

answered Mar 17, 2020 at 2:54

Xueli Chen

12.7k3 gold badges28 silver badges39 bronze badges

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

6 Comments

Rahul Waghmare Over a year ago

I am using API controller and in get API it looks something like this

Rahul Waghmare Over a year ago

// GET: api/Sales [HttpGet] public async Task<ActionResult<IEnumerable<Sales>>> GetSales() { return await _context.Sales.ToListAsync(); }

Rahul Waghmare Over a year ago

I want to do something like this stackoverflow.com/questions/51004760/…

Xueli Chen Over a year ago

@RahulWaghmare , please check my updated. If you find my workaround works , could you mark my reply as answer?

Rahul Waghmare Over a year ago

Thank you so much. Yes I have marked your reply as Answer.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related