You must get a list of items that contain sub-items that are mapped to another list.

Question

Situation:

I have some people with certain skills, and they can / can belong to more than one area. Skills are related to each other, as are areas.

I get a list of people from selecting all faces with a match for each skill and adding them to a list where I can use Distinct () to make sure they are not displayed twice.

The resulting list of faces:

List<Person> peopleWithRightSkills

On each [Person] object, I have at least one address associated, but they can have more like it in relation to [Person]

I have another list:

List<PostalCode> acceptedPostalcodes

Now I need to compare and filter those peopleWithRightSkills that have an address where the postal code of the address is in the acceptedPostacodes

-, SelectMany , , , , " " , / . :

List<Person> matchedPeople

( )

[Table:Person]
int:ID (primary)
string:FirstName
string:LastName

[Table:Address]
int:Person_ID (foreign key to Person)
int:PostalCode_ID (foreing key to PostalCode)
string:StreetName

[Table:PostalCode]
int:ID
string:CityName

, " " ( 1, , 10 ), " " .

, , , .

Answer 1

List<int> peopleIDs = peopleWithRightSkills.Select(p => p.ID).ToList();
List<int> postalIDs = acceptedPostalCodes.Select(c => c.ID).ToList();

var query = db.Persons
  .Where(p => peopleIDs.Contains(p.ID)
  .Where(p => p.Addresses.Any(a => postalIDs.Contains(a.PostalCode_ID))
  );

LinqToSql List<int> . Contains TSql IN.

, LinqToSql , ( 50k), SqlServer ~ 2000. , . 1500 1000 PostalCodes, 2500 SQL Server, 400 , SqlException.

Answer 2

, , acceptedPostalCodes ; / , -, . , . , , , acceptedPostalCodes.

, (.. , - .)

, , , , , , .

EDIT: , , , - LINQ, peopleWithRightSkills, PostalCodes:

var matchedPeople =
peopleWithRightSkills.Where(p => acceptablePostalCodes.Contains(p.Address));