Syntax for LINQ to DataSets Row Filter in C#?

Question

I have two DataTables one is Primary Table and the rest is a sub table (I AM UISNG STRONGLY TYPED DATASET).

Example

Employee Table

Id   Name   City 
1    AAA    NY 
2    BBB    BB
3    CCC    AA

CityInitials Table

CityInitial
NY    
FF  
CC
RR 
RNF
YOT
DDD  

I have to select rows from employee table only when the 'city' in Employee table matches any of the CityInitials of CityInitials Table. i tried

var _filter = EmployeeTable.AsEnumerable().
Select(x=>x.Field<string>("City")).Contains
(CityInitials.AsEnumerable().Field<string>("CityInitials").Select(row=>row);

Please suggest what is the proper query to achieve the result?

Answer 1

I assume these are in fact typed datasets? Without knowing exactly what you're working with, I'd call the following an approximation of what I'd expect to work:

var cities = CityInitials.Rows.Select(x => x.CityInitials).ToList();
var _filter = EmployeeTable.Rows.Where(x => cities.Contains(x.City));