CODEWITHSUNDEEP
linqentity-framework
ledragon
ledragon

Reputation: 301

Linq Method syntax for retrieving single nested child entity

I wonder if I could get some help with the following. I'm retrieving set of data as follows using EF. 

var booking = this.GetDbSet<Booking>().Include(c => c.BookingProducts.Select(d => d.Product.PrinterProducts.Select(e => e.ProductPrices))).Single(c => c.BookingId == bookingId)

Within a PrinterProduct there is a foreign key PrinterId for an additional entity Printer. With the Booking Entity I also have PrinterId also linked by foreign key to the additonal entity Printer.

What I'm hoping to do is retrieve only the PrinterProduct relating to the PrinterId held in the booking entity rather that all the PrinterProducts as in my code. I've tried to use Join but have tied myself in knots!

Grateful for any help!

Edit:

Object structure:

public class Booking
{
    public Guid BookingId { get; set; }
    public string BookingName { get; set; }
    public Printer Printer { get; set; }
    public IEnumerable<BookingProduct> BookingProducts { get; set; }
}

public class BookingProduct
{
    public int BookingProductId { get; set; }
    public Booking Booking { get; set; }
    public Product Product { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public IEnumerable<PrinterProduct> PrinterProducts { get; set; }
}

public class PrinterProduct
{
    public int PrinterProductId { get; set; }
    public Product Product { get; set; }
    public Printer Printer { get; set; }
    public IEnumerable<ProductPrice> ProductPrices { get; set; }
}

public class ProductPrice
{
    public int ProductPriceId { get; set; }
    public PrinterProduct PrinterProduct { get; set; }
    public decimal Price { get; set; }
}

public class Printer
{
    public int PrinterId { get; set; }
    public string PrinterName { get; set; }   
    public IEnumerable<Booking> Bookings { get; set; }
    public IEnumerable<PrinterProduct> PrinterProducts { get; set; } 
}

Upvotes: 2

Views: 166

Answers (2)

Flater
Flater

Reputation: 13803

Given the newly added class structures in your question, I hope I can clear it up now.

From what I see, Bookings and Products have a many-to-many relation (where BookingProduct is used as the connection). The same is true for Product and Printer (where PrinterProduct is used as the connection). From what I understand, you are trying to get from a singular Booking item to a singular PrinterProduct. I don't see any efficient way to do this without introducing the possibility of inconsistency with your data. You're expecting some Lists to return you one result. If it's only one result, why is it a List in the first place?

  • You have a single Booking. You take its BookingProducts. Now you have many items.
  • You take the Product from each individual BookingProduct. If all BookingProducts have the same product, you're in luck and will only have a List<Product> with a single Product in it. However, there is nothing stopping the system from return many different products, so we are to assume that you now hold a List of several Products
  • From each Product in the list, you now take all of its PrinterProducts. You now hold many PrinterProducts of many Products.

As you see, you end up with a whole list of items, not just the singular entity you're expecting.

Bookings, Products and Printers are all connected to eachother individually, like a triangle. I have seen scenarios where that is correct, but nine times out of ten, this is not what you want; and only leads to possible data inconsistency. Look at it this way: Is it ever possible for the Product to have a Printer other than the Printer that is already related to the Booking? If not, then why would you have two relations? This only introduces the possibility that Booking.Printer is not the same as PrinterProduct.Printer.

Your relational model is set up to yield many results, but I think you expect a single result in some places. I would suggest taking another look at your data model because it does not reflect the types of operation you wish to perform on it. Change the many-to-many relations to one-to-many where applicable, and you should be able to traverse your data model in a more logical fashion, akin to the answer I provided in my previous answer.

Upvotes: 1

Flater
Flater

Reputation: 13803

If you've set up navigational properties, you can just browse to it:

var myBooking = ... //A single Booking, don't know how you retrieve it in your case.

var myPrinter = myBooking.Printer; //the Printer that is related to the Booking.

var myPrintproducts = myPrinter.PrintProducts; //The products that are related to the printer.

You don't need to keep nesting select statements, that only creates unnecessary confusion and overhead cost. Keep in mind that you need to do this while in scope of the db context. Every time you try to access a property, EF will fill in the needed variables from the database. As long as there is an open db connection, it works.

Edit

If you really need to optimize this, you can use a Select statement. But you only need a single one. For example: 

var myPrintproducts = db.Bookings.Single( x => x.ID == some_id_variable ).Select( x => x.Printer.PrintProducts);

But unless you have a very strict performance requirement, it seems better for code readability to just browse to it.

Upvotes: 1

Related Questions