How to do subquery in linq?

[Andy]

Hi,

I have a query I'm building dynamically.. I start off with this:
var query =
from documents in db.Document
select documents;

I then use the Method syntax to add filters
query = query.Where( d => d.CustPONumber.StartsWith( "ABC" ) );

This works, except that there is some data that should be filtered in
another related table (Contacts).

I'd like to have the equivalent of this query in linq.

SELECT *
FROM [Documents]
WHERE DocumentId IN ( SELECT DocumentId from Contacts WHERE FirstName
LIKE 'Tri%' )

I've tried this:
query = query.Where(
d => contacts.Any(
c => c.DocumentId == d.DocumentId
)
);

Where query is as specified above and contacts is:
var contacts = from dContacts in db.Contacts where
dContacts.FirstName.StartsWith( "Tri" );

I'm not getting the expected results; for some reason, there is a
clause being added in the final where clause that says the two primary
key's for [Documents] and [Contacts] must be equal (d.DocumentId =
c.ContactId). Clearly, that's not what I want.

Any ideas?

Thanks
Andy

 

[Jon Skeet [C# MVP]]

I have a query I'm building dynamically.. I start off with this:
var query =
from documents in db.Document
select documents;

I then use the Method syntax to add filters
query = query.Where( d => d.CustPONumber.StartsWith( "ABC" ) );

This works, except that there is some data that should be filtered in
another related table (Contacts).

I'd like to have the equivalent of this query in linq.

SELECT *
FROM [Documents]
WHERE DocumentId IN ( SELECT DocumentId from Contacts WHERE FirstName
LIKE 'Tri%' )

I've tried this:
query = query.Where(
d => contacts.Any(
c => c.DocumentId == d.DocumentId
)
);

Where query is as specified above and contacts is:
var contacts = from dContacts in db.Contacts where
dContacts.FirstName.StartsWith( "Tri" );

I'm not getting the expected results; for some reason, there is a
clause being added in the final where clause that says the two primary
key's for [Documents] and [Contacts] must be equal (d.DocumentId =
c.ContactId). Clearly, that's not what I want.

Try:

var query = db.Document
.Where(doc => db.Contact.Where(contact =>
contact.FirstName.StartsWith("Tri"))
.Select(contact => contact.DocumentId)
.Contains(doc => doc.DocumentId));

I think that's the right logical query (without checking, admittedly) -
but give it a try.