none
combining two queries from db repository RRS feed

  • Question

  • I have two queries that I'm trying to combine into one and return a list of objects, but I'm finding it a little bit difficult.

     have two tables: listing and amenities

    listing has columns: listingid, title, description, address city, state, zip

    amenities has columns: amenityid, listingid, emenity

    the column 'listingid' in the amenity table is a forein key to listingid in the 'listing' table so I can have many amenities to one listing

    I want to return a collection of objects, where each object in the collection has its single listing and its associated amenities. 

    Each row in listings represents a single listing, so the object from the linq query should only have one listing row in its object but can have many amenities in its object. 

    Right now I have the two queries split up so I can get a list of the listings but then I have to run a second query to get the list of amenities from the listing by passing in a 'listingid' from the 'listing' table

     

    public IQueryable<Listing> GetListings()
        {
          return from listing in entities.Listings
              select listing;
        }
    
        public IQueryable<Amenity> GetAmenities(int listingId)
        {
          return from amenity in entities.Amenities
              where amenity.ListingID == listingId
              select amenity;
        }
    

    I have been trying this linq query but for each amenity, say 3, it will return three objects into the collection where the listing is repeated three times, one for each amenity. I tried using distinct at the end of the query, but I knew that wasn't correct.

    public IQueryable<ListingObject> AllListings()
        {
           var list = from listing in entities.Listings
                join amenities in entities.Amenities
                on listing.ListingID equals amenities.ListingID
                into JoinedListingAmenities
                from amenities in JoinedListingAmenities.DefaultIfEmpty()
                select new ListingObject
                {
                  Listing = listing ,
                  Amenities = amenities
                };
           return list;
        }
    


    chuckdawit
    Sunday, April 24, 2011 3:31 PM

Answers

  • I figured it out.

     

    var li = from listing in entities.Listings
                select new
                {
                  Listing = listing,
                  Amenity = (from am in entities.Amenities
                       where am.ListingID == listing.ListingID
                       select am)
                };
    


    chuckdawit
    • Marked as answer by witdaj Sunday, April 24, 2011 5:24 PM
    Sunday, April 24, 2011 5:24 PM