.Where(t => !t.IsSet)
    .SelectMany(t => table.References)
        .Where(r => r.PrimaryTable == "myTable"));



Actually, I'm a bit confused by this code. Are you sure it's doing what it's meant to do? In particular, what's throwing me off is the fact that you're enumerating over table.References, but then, when a certain condition holds for a particular Reference (i.e., refer.PrimaryTable == "myTable"), you're adding the Table (table) instead of the Reference (refer).

What this means is that if a Table has multiple Reference objects with PrimaryTable == "myTable", your tables collection might contain multiple copies of this Table. Is this correct?

I'm going to go out on a limb and guess that what you really want to check is simply that a Table has, in its References collection, any Reference object with PrimaryTable == "myTable". If that's the case, in your original code after tables.Add(table) I would have simply added break to avoid duplicates. (It may be that only one Reference in each collection would ever have the same PrimaryTable, which case you'd be fine; but you could still stop enumerating at this point. Unless of course you want the duplicates.)

In any case, Lee's code (and what I have below) is not duplicating this behavior. Rather, it's adding the Reference objects to a list (because the final ToList call is on an IEnumerable<Reference>).

It seems like, if what I've described above is the behavior you're after, you might want to do this:

var tables = dbServer.Tables
    .Where(table => !table.IsSet)
        table => table.References.Any(refer => refer.PrimaryTable == "myTable")


I'm going to expand on Lee's answer. Let's analyze it line by line.

// 1. enumerating over a collection
foreach (Table table in dbServer.Tables)
    // 2. checking a condition
    if (!table.IsSet)
        // 3. enumerating over another collection
        foreach (Reference refer in table.References)
            // 4. checking a condition
            if (refer.PrimaryTable == "myTable")
                // 5. adding to a collection

OK. So we've got:

  1. Enumeration - simple -- that's where we start
  2. Condition checking - we'll need a Where
  3. Enumeration over another collection - SelectMany
  4. Condition checking - Where again
  5. Adding - most likely ToList (depends on what type of collection you want)

Here's what it comes out to:

var tables = dbServer.Tables                         // step 1
    .Where(table => !table.IsSet)                    // step 2
    .SelectMany(table => table.References)           // step 3
    .Where(refer => refer.PrimaryTable == "myTable") // step 4
    .ToList();                                       // step 5

Make sense?


Just need to use two from's

        var q = from table in dbServer.Tables
                where !table.IsSet
                from refer in table.References
                where refer.PrimaryTable == "myTable"
                select table;


var tables = dbServer.Tables
    .Where(t => !t.IsSet)
    .SelectMany(t => t.References)
    .Where(r => r.PrimaryTable == "myTable")

Assuming tables is a List<T>

EDIT: As the comment points out, this isn't the same as the original - it looks like what you actually want is this:

var tables = dbServer.Tables
    .Where(t => !t.IsSet && t.References.Any(r => r.PrimaryTable == "myTable"))

This will give you all the tables which have a reference whose PrimaryTable is 'myTable' which assumes that there will only be one matching reference table. Otherwise you could have the same table added multiple times.

Related Query

More Query from same tag