How do I do queries against hierarchical data using LINQ to SQL?

Question

I have 2 tables that are related. each app can relate to many apps. ie, office can be related to word, excel.......

app
id PK int
appname varchar(50)
.....

appsrelated
relatedid int fk to app.id
appid int

sample data app id, appname
1, office
2, word
3, excel
4, quake

appsrelated relatedid, appid
1, 2
1, 3
basically new to linq to sql an I have Brain lock.

I would like to do the following query. I use vb.net but c# is ok. query is give me all the apps that are not related to (1)
result should be (4, quake)

thanks in advance.

Answer 1

C# -- find the ids of the related apps, select ids, select only those apps that aren't the app in question or whose ids don't appear in the ids of the apps that are related.

var query = apps.Where( a => a.appid != 1
                             &&  !appsrelated.Where( r => r.relatedid == 1 )
                                             .Select( r => r.appid )
                                             .Contains( a.appid ) );

Answer 2

The following code should accomplish what you are asking if I understood correctly.

var relatedToApp1 = Context.appsrelated.Where(related => related.relatedid == 1);
var items = Context.app.Where(app => app.id != 1 && !relatedToApp1.Any(related => related.appid == app.id));

Answer 3

for those in vb land. here is the resulting query.

Dim dc As New dashboardDataContext  
Dim q = dc.appsrelateds _  
.Where(Function(r) r.relatedid = 17)

Dim items = dc.ApplicationInfos _  
    Where(Function(app) app.Id <> 17 And Not q.Any(Function(related) related.appid = app.Id))