CODEWITHSUNDEEP
linqentity-frameworkc#-4.0ef-code-firstmany-to-many
deshergan
deshergan

Reputation: 73

Linq Many to Many query

I need help to query this three tables. RentCommunityFeature and RentPropertyFeature has a many to many relationship with RentUnitListing. My problem is i can't get these three tables to query. What i want is all those rentlistings that has certain features. for example if RentCommunityFeature has a "pool" and RentPropertyFeature has a "parking", i want all the records in RentUnitListing that has "Pool" and "Parking". If no parking than result should show only with "Pool".

I tried the below query but it gives incorrect results. It shows duplicate results when myCommunityFeatureId or myPropertyFeatureId is -1. I have initializes them to -1 if they are empty in DB.

Any help would be greatly appreciated.

var AllAds = from r in _db.RentUnitListings
             from cf in r.RentCommunityFeatures                    
             from pf in r.RentPropertyFeatures
             where (myCommunityFeatureId > 0) ? (cf.RentCommunityFeatureID == myCommunityFeatureId && cf.RentUnitListings.) : (myCommunityFeatureId == -1)
             where (myPropertyFeatureId > 0) ? (pf.RentPropertyFeatureID == myPropertyFeatureId) : (myPropertyFeatureId == -1)
             select r;

public partial class RentCommunityFeature
{

    public int RentCommunityFeatureID { get; set; }
    public string RentCommunityFeatureDescription { get; set; }

    public virtual ICollection<RentUnitListing> RentUnitListings { get; set; }
}

public partial class RentPropertyFeature
{


    public int RentPropertyFeatureID { get; set; }
    public string RentPropertyFeatureDescription { get; set; }

    public virtual ICollection<RentUnitListing> RentUnitListings { get; set; }
}

public partial class RentUnitListing
{
    public Guid RentUnitListingID { get; set; }
    public string RentUnitListingShortDescription { get; set; }   
    public virtual ICollection<RentCommunityFeature> RentCommunityFeatures { get; set; }
    public virtual ICollection<RentPropertyFeature> RentPropertyFeatures { get; set; }        
}

Upvotes: 1

Views: 89

Answers (1)

Slauma
Slauma

Reputation: 177163

var listings = _db.RentUnitListings
    .Where(rul => rul.RentCommunityFeatures
                 .Any(rcf => rcf.RentCommunityFeatureID == myCommunityFeatureId)
               || rul.RentPropertyFeatures
                 .Any(rpf => rpf.RentPropertyFeatureID == myPropertyFeatureId))
    .ToList();

It means: Return all listings that have at least one (Any) RentCommunityFeature with the myCommunityFeatureId OR at least one (Any) RentPropertyFeature with the myPropertyFeatureId. The "OR" is not exclusive, so a returned listing may have a "Pool" without a "Parking" feature or a "Parking" without a "Pool" feature or it might have both. In any case a returned listing might have a lot of other features in addition to "Pool" or "Parking".

Upvotes: 1

Related Questions