linq to sql - query to find items without children

i am开发者_运维百科 trying to write a linq query that doesn't fire a million child queries.

what i want to do is something like this in sql

select incoming.id, incoming.message, outgoing.message
from incoming
left join outgoing on incoming.id = outgoing.originalMessageId
where outgoing.message is not null

so basically i am doing the left join just do i can find the objects that don't have children.

is there a way to do this, AND return a typed bunch of the "incoming" items...?

sounds simple, and i've tried a few different approaches but i think i'm suffering from lack of sleep so my brain isn't making it happen :(

thanks in advance Doug

If I understand your question correctly, this should do the trick:

Incoming
.Join(
    Outgoing
        .Where(o => o.message != null),
    i => i.id,
    o => o.originalMessageId,
    (i,o) => new {Incoming=i, Outgoing=o}
)