How to query multiple tables in EF ASP.NET MVC?

Question

i have a big beginner Problem i think! I try to make a ASP.NET MVC 5 Applikation, the application should store Suppliers in a database table, in seperate table it should store SalesOrders. In the View, when i click on details of the Supplier, all details should be load, but also all SalesOrders where for this Supplier should be load too. i've tried this way without result.

ModelBase Contains an prop ID only.

The Classes

public class SalesOrder : ModelBase
{
    public string ordertitle { get; set; }
    public Supplier Supplier { get; set; }
}


public class Supplier : ModelBase
{
    public string CompanyName { get; set; }
    public string ContactPerson { get; set; }
    public string Phone { get; set; }
    public string Street { get; set; }
    public string Zip { get; set; }
    public string City { get; set; }
    public int Discount { get; set; }
}

The ViewModel

public class SalesOrderViewModel
{
    public Supplier Supplier { get; set; }
    public List<SalesOrder> SalesOrder { get; set; }
}

The Controller

public ActionResult SupplierSalesOrders(int id)
{
    var supplier = _context.Suppliers.Single(s => s.Id == id);
    var salesorder = _context.SalesOrders.ToList().Where(s => s.Supplier == supplier);

    SalesOrderViewModel viewModel = new SalesOrderViewModel
    {
        Supplier = supplier,
        SalesOrder = salesorder.ToList()
    };

    return View(viewModel);
}

The View

@model NETwork.Models.SalesOrderViewModel


@foreach(var sales in Model)
{
    @Html.LabelFor(model => model.SalesOrder)
}

sorry for the loooong question.

What ive done wrong?

best regards

Lars

TheGeneral · Accepted Answer · 2020-02-03 23:54:44Z

Your first problem is you would usually query by Id

.Where(s => s.Supplier == supplier);

The above is not an id, is an object of supplier

One solution would be to include the collection navigation property for SalesOrders in Supplier

public class Supplier : ModelBase
{
    public string CompanyName { get; set; }
    public string ContactPerson { get; set; }
    public string Phone { get; set; }
    public string Street { get; set; }
    public string Zip { get; set; }
    public string City { get; set; }
    public int Discount { get; set; }
    public ICollection<SalesOrder> SalesOrders { get; set; }
}

Which then allows

 var supplier = _context.Suppliers
                        .Include(x => SalesOrders)
                        .Single(s => s.Id == id);


 SalesOrderViewModel viewModel = new SalesOrderViewModel
 {
      Supplier = supplier,
      SalesOrder = supplier.SalesOrder
 };

Note that, the above viewModel is probably not ideal. Meaning, supplier will naturally contain all the salesorders in this incarnation and SalesOrder = supplier.SalesOrder will be redundant.

Jossean Yamil · Accepted Answer · 2020-02-03 23:58:56Z

You can add a Foreign key to the SalesOrder class for the supplier.

public class SalesOrder : ModelBase
{
    public string ordertitle { get; set; }

    [ForeignKey(nameof(Supplier))]
    public int SupplierId { get; set; }

    public Supplier Supplier { get; set; }
}

And you can add a collection of Sales orders to the suppliers.

public class Supplier : ModelBase
{
    public string CompanyName { get; set; }
    public string ContactPerson { get; set; }
    public string Phone { get; set; }
    public string Street { get; set; }
    public string Zip { get; set; }
    public string City { get; set; }
    public int Discount { get; set; }
    public ICollection<SalesOrder> SalesOrders { get; set; }
}

Then, if you would like to get the Sales orders with the supplier, you can use the Include() action when you query the supplier.

var supplier = _context.Suppliers.Include(s => s.SalesOrders).Single(s => s.Id == id);

Collectives™ on Stack Overflow

How to query multiple tables in EF ASP.NET MVC?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related