LINQ: Is there a way to combine these queries into one?

Question

I have a database that contains 3 tables:

• Phones
• PhoneListings
• PhoneConditions

PhoneListings has a FK from the Phones table(PhoneID), and a FK from the Phone Conditions table(conditionID)

I am working on a function that adds a Phone Listing to the user's cart, and returns all of the necessary information for the user. The phone make and model are contained in the PHONES table, and the details about the Condition are contained in the PhoneConditions table.

Currently I am using 3 queries to obtain all the neccesary information. Is there a way to combine all of this into one query?

public ActionResult phoneAdd(int listingID, int qty)
{

    ShoppingBasket myBasket = new ShoppingBasket();
    string BasketID = myBasket.GetBasketID(this.HttpContext);



     var PhoneListingQuery = (from x in myDB.phoneListings
                             where x.phonelistingID == listingID
                             select x).Single();

     var PhoneCondition = myDB.phoneConditions
                          .Where(x => x.conditionID == PhoneListingQuery.phonelistingID).Single();

    var PhoneDataQuery = (from ph in myDB.Phones
                          where ph.PhoneID == PhoneListingQuery.phonePageID
                          select ph).SingleOrDefault();

}

Answer 1

You could project the result into an anonymous class, or a Tuple, or even a custom shaped entity in a single line, however the overall database performance might not be any better:

var phoneObjects = myDB.phoneListings
       .Where(pl => pl.phonelistingID == listingID)
       .Select(pl => new 
       {
          PhoneListingQuery = pl,
          PhoneCondition = myDB.phoneConditions
             .Single(pc => pc.conditionID == pl.phonelistingID),
          PhoneDataQuery = myDB.Phones
             .SingleOrDefault(ph => ph.PhoneID == pl.phonePageID)
       })
       .Single();

  // Access phoneObjects.PhoneListingQuery / PhoneCondition / PhoneDataQuery as needed

There are also slightly more compact overloads of the LINQ Single and SingleOrDefault extensions which take a predicate as a parameter, which will help reduce the code slightly.

Edit

As an alternative to multiple retrievals from the ORM DbContext, or doing explicit manual Joins, if you set up navigation relationships between entities in your model via the navigable join keys (usually the Foreign Keys in the underlying tables), you can specify the depth of fetch with an eager load, using Include:

var phoneListingWithAssociations = myDB.phoneListings
       .Include(pl => pl.PhoneConditions)
       .Include(pl => pl.Phones)
       .Single(pl => pl.phonelistingID == listingID);

Which will return the entity graph in phoneListingWithAssociations

(Assuming foreign keys PhoneListing.phonePageID => Phones.phoneId and PhoneCondition.conditionID => PhoneListing.phonelistingID)

Answer 2

You should be able to pull it all in one query with join, I think.

But as pointed out you might not achieve alot of speed from this, as you are just picking the first match and then moving on, not really doing any inner comparisons.

If you know there exist atleast one data point in each table then you might aswell pull all at the same time. if not then waiting with the "sub queries" is nice as done by StuartLC.

 var Phone = (from a in myDB.phoneListings
         join b in myDB.phoneConditions on a.phonelistingID equals b.conditionID 
         join c in ph in myDB.Phones on a.phonePageID equals c.PhoneID
         where 
         a.phonelistingID == listingID
         select new {
         Listing = a,
         Condition = b,
         Data = c
         }).FirstOrDefault();

FirstOrDefault because single throws error if there exists more than one element.