How can I use linqTOsql to efficiently get nested lists?

Question

I have three tables that I would like to query: Streams, Entries, and FieldInstances.

I'm wanting to get a list of entries inside a stream. A Stream could be a blog or a page etc. and entry is the actual instance of the stream ie: "stream:Page entry:Welcome" or "stream:blog entry:News about somthing".

The thing is, each entry has custom data field associated with it through FieldInstance. IE:

stream: Page entry: Welcome fieldInstance: Welcome Image Path

I'm trying to figure out the best way to get a list of all entries inside of one stream and also have the custom field instances that are associated with each entry.

I've been playing around with code like this:

var stream = genesisRepository.Streams.First(x => x.StreamUrl == streamUrl);
IQueryable<StreamEntry> entry = genesisRepository.StreamEntries.Where(x => x.StreamID == stream.StreamID);
IQueryable<FieldInstance> fieldInstances = genesisRepository.FieldInstances.Where(
            // doesn't work because entry is basically returning a collection of some kind.
            // and i can't figure out how to compare a single ID with a list/collection of IDs
            x => x.fiStreamEntryID == entry.Where(e => e.StreamID == stream.StreamID)
        );

This of course doesn't work. Inititially I was thinking to get all entries in the stream and then all fieldInstances in the stream, then I'll display the data using lambdas after I have everything... hopefully keeping sql queries dows to two or three. But I can't figure out how to write the linqTOsql to execute in just two or three queries. I keep thinking that I need to execute quiries in a loop to get the fieldInstances for each entry.

Is there a LinqTOsql query that will select all fieldInstances where the StreamEntryID(fk) is in the list of entries whose (fk)StreamID matches the Stream?

Answer 1

Does this help? I'm still new to EF but that's how I'd have a crack at it...

var results = genesisRepository.FieldInstances
    .Include("StreamEntry.Stream")
    .Where(fi => fi.StreamEntry.Stream.StreamUrl == streamUrl);

Answer 2

Sorry, I don't quite get what columns you are joining in the third statement, but:

var stream = genesisRepository.Streams.First(x => x.StreamUrl == streamUrl);
IQueryable<StreamEntry> entries = genesisRepository.StreamEntries.Where(x => x.StreamID == stream.StreamID);
IQueryable<FieldInstance> fieldInstances = from entry in entries
    from instance in genesisRepository.FieldInstances
    where entry.entryId == instance.fiStreamEntryID
    select instance;