lambda expression for a query on two tables that returns records from one table

Question

I have two tables

TableA (articles)
int id
int Type
string name

and

TableB (compatibles)
int linked_ID
int tableA_ID

TableA records:
id=1, Type=0, name="ArticleA"
id=2, Type=1, name="ArticleB"
id=3, Type=2, name="ArticleC"
id=4, Type=1, name="ArticleD"

TableB records:
linked_ID= 1, tableA_ID=2
linked_ID= 1, tableA_ID=3
linked_ID= 1, tableA_ID=4

TableB has a list of arcicels that are compatible to a certain article. I am quite new to queries (didn't need them in my projects yet). But as C# and WPF allow some pretty cool automation with Binding I would like to add a binding that returns the following:

Give me all articles that are of Type 1 and compatible to my selected article (id=1).

The simple part of it works well (articles has a list of all articles):

private ObservableCollection<Article> _articles = 
    new ObservableCollection<Article>();

[fill it with the available articles] and then:

comboBoxArticles.ItemsSource = 
    _articles.AsBindable().Where( c => c.Typ == 0 );

How can I extend the Where clause to query another table?

Thanks a lot in advance.

Answer 1

It appears as if you want to "join" two "tables" using Linq. Here is something to get you started:

comboBoxArticles.ItemsSource =
    from article in _articles
    from compatible in _compatibles
    where article.Type == 0 && && article.id == compatible.tableA_ID && compatible.linked_ID == 1
    select article;

... or in method chain...

comboBoxArticles.ItemsSource = _articles
    .SelectMany(article => _compatibles, (article, compatible) => new {article, compatible})
    .Where(@t => @t.article.Type == 0 && @t.article.id == @t.compatible.tableA_ID && @t.compatible.linked_ID == 1)
    .Select(@t => @t.article);