How to query hierarchical data using LINQ to SQL?

Question

I have two tables that are related to each other. Each application can apply to many applications. those. office can be associated with the word excel .......

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

appsrelated
relatedid int fk - app.id
appid int

sample data app id, appname
1, office
2, word
3, excellent 4, earthquake

appsrelated Related, appid
1, 2
1, 3

Basically, I'm new to linq-to-sql and I have a brain lock.

. vb.net, # . , (1), (4, ).

.

Answer 1

, , .

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 2

# - , , , , .

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

Answer 3

for those who are in vb-country. here is the result of the 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))