Linq to Sql select from multiple table

Question

Hello; I have a question about selecting from multiple tables in C# using Linq.

Table structure is like this:

TABLE A
TableAID Column1 Column2

TABLE B
TableBID TableAID Column3 Column4

So in code i have:

List<string> myList = new List{"Test1","Test2"};
var myView = MYDC.TableA.AsQueryAble();

If I want to select records from table A using where on Column1, I would simply use:

myView = myView.Where(k=>myList.Contains(k.Column1));

But if I want to preserve the myView as queryable of TableA and if I want to use where on TableB on Column3, which is linked to TableA with a foreign key, how would I do it?

I tried the following with no success:

myView = myView.Where(k=>myList.Contains(k.TableB.Select(kk=>kk.Column3)));

Any suggestions?

Thanks in advance

Answer 1

There are several ways to to this. Here are two of them:

var q = (
    from b in MYDB.TableB
    where myList.Contains(b.Column3)
    select b.TableA).Distinct();

var q =
    from a in myView
    let bs = a.TableB.Where(b => myList.Contains(b.Column3))
    where bs.Any()
    select a;

I hope this helps.