How do I create a dynamic where clause using a List in LINQ to SQL?

Question

 var query_loc = (from at in db.amenities_types
                     join a in db.amenities on at.id equals a.amenities_type
                     join u in db.unitInfos on a.unit_id equals u.id
                     join l in db.locations on u.locations_id equals l.id
                     join o in db.organizations on l.organization_id equals o.id
                     join ot in db.organization_types on o.id equals ot.organization_id
                     where (((u.price >= low_rent) && (u.price <= high_rent)) 
                              || (u.price == null))
                     && (u.bedrooms <= beds) && (u.bathrooms <= baths)
                     && amenities_list.Contains(at.id)
                     && (((ot.active == true) && (DateTime.Now <= ot.deactivateDate))
                          || ((ot.active == true) && (ot.deactivateDate == null)))
                         && (((l.active == true) && (DateTime.Now <= l.deactivateDate))
                          || ((l.active == true) && (l.deactivateDate == null)) )
                     && (ot.type == 8)
                     orderby o.name ascending, l.name ascending
                     select new { l, o, u, ot, at });

The specific line I need to replace is

where amenities_list.Contains(at.id)

Instead it needs to produce SQL like this ([at.id] = 29 AND [at.id] = 30 AND [at.id] = 40)

So how do I get my List to produce the above SQL code in LINQ to SQL.

Answer 1

Please create methods for your clauses, you are scaring me.

var query_loc = (from at in db.amenities_types 
                 join a in db.amenities on at.id equals a.amenities_type 
                 join u in db.unitInfos on a.unit_id equals u.id 
                 join l in db.locations on u.locations_id equals l.id 
                 join o in db.organizations on l.organization_id equals o.id 
                 join ot in db.organization_types on o.id equals ot.organization_id 
                 where
                    PriceIsValid(u)
                 && BedsAndBathsArevalid(u) 
                 && AtIdIsValid(at.id)
                 && SomeCrazyDateConditionIsValid(ot, l)
                 && TheOtTypeIsValid(ot)
                 orderby o.name ascending, l.name ascending 
                 select new { l, o, u, ot, at }); 

And if you mean at.is = 29 OR at.id = 30 OR at.id = 40, then use an AtIdIsValid(at.id) predicate like:

bool AtIdIsValid(int atId){ return (atId == 29 || atId == 30 || atId == 40); }