How to convert this foreach loop into Linq code?

Question

I am new one with Linq and I would like to modify my old c# code to use Linq. The idea of this code to select all tables where it's not set and reference’s field PrimaryTable equal "myTable"

foreach (Table table in dbServer.Tables)
            {
                if (!table.IsSet)
                {
                    foreach (Reference refer in table.References)
                    {
                        if (refer.PrimaryTable == "myTable")
                        {
                            tables.Add(table);
                        }
                    }
                }
            }

After digging in internet I have got this code

var q = from table in dbServer.Tables
                    let refers = from refer in table.References
                                 where refer.PrimaryTable == "myTable"
                                 select refer.ForeignTable
                    where refers.Contains(table.Name)
                    select table;

But it does not work at all and I need your help to make it works.

Thanks in advance.

Answer 1

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

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"))
    .ToList();

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.

Answer 2

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

Answer 3

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;

Answer 4

EDIT

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)
    .Where(
        table => table.References.Any(refer => refer.PrimaryTable == "myTable")
    ).ToList();

---

ORIGINAL ANSWER

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
                tables.Add(table);
            }
        }
    }
}

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?