how to search LINQ SQL where value in LIST<object>

I have a SQL table called Quotes.

And in C# the user I have:

Public class Locations
{ 
 public string cityname { get; set; }
}

 List<Locations> = new puLocations List<Locations>  // Pickup Locations
 List<Locatio开发者_如何学Gons> = new delLocations List<Locations> // Delivery Locations

Now I want to search my quote table with these lists.. something like this (obviously this doesnt work)

var quotes = from q in db.Quotes
where q.PULocations in puLocations  //puLocatiosn is the list<Locations>
and q.DELLocations in delLocations
select q;

so I want it to return ANY match.. ie If in pu locations i have gold coast, sydney, Brisbane. And Delivery location i have Perth, Hobart it should return gold coast -> perth gold coast -> hobart sydney -> perth sydney -> gold coast .... etc (if those quotes exist)

---

First you need this

List<Locations> puLocations= new  List<Locations>()  // Pickup Locations  
List<Locations> delLocations = new  List<Locations>() // Delivery Locations 

And then you want to use the .Contains() extension method like :

var quotes = from q in db.Quotes where q.PULocations.Contains(puLocations) and 
q.DELLocations.Contains(delLocations) select q;

---

In order not to make your life harder you should make this operation in db, and i bet you have pickup and delivery locations in you database together with quotes so supposing that you have them like db.Locations.Where(l=>l.type==DEL) and db.Locations.Where(l=>l.type==PU) you would write the following query

var quotes = from q in db.Quotes
    join del in db.Locations on del equals q.DELLocations
    join pu in db.Locations on pu equals q.PULocations
    where pu.type == PU && del.type == DEL && otherConditions
    select q;

also in case you really have constrains in you database they would propagate as navigation properties so you linq query would look like below

var quotes = from q in db.Quotes where q.DELLocation.field == something && q.PULocation.field == something

but you might have no constrain, so first query will be the way to go.

the last one way you can go is utilize Entity SQL IN statement, and write long statement like the following

   // you build string specifying as many parameters as you have items in your lists
   var entitySQL = "select quote FROM [Quotes] WHERE quote.DELLocation.LocationID IN (@delparam1, @delparam2,.... ) AND quote.PULocation.LocationID in (@puparam1, @puparam2,.... )"
   // then you create ObjectParameterCollection to substitute your IDs to ObjectQuery
   var opc = new ObjectParameterCollection();
   opc.Add("@delparam1", DELLocation[0].LocationID) // obviously you'll do it using linq
   opc.Add("@puparam1", PULocation[0].LocationID)

   // create object query

   var quotes = db.CreateQuery<Quote>(entitySQL, opc);