linq to entities, a where in where clause? (inner where)

Question

I have a table with a one to many mapping to a table that has a many to many mapping to another table. I'd like to do the following:

var results = context.main_link_table
                     .Where(l => l.some_table.RandomProperty == "myValue" &&
                            l.some_table.many_to_many_table
                             .Where(m => m.RandomProperty == "myValue"));

How can I achieve this? The first part will work but when trying it without the 'inner WHERE', I can't access the many_to_many_table's properties, but the "inner where" obviously won't compile. I basically want to achieve something like the following SQL query:

SELECT * from main_link_table
INNER JOIN some_table AS t1 ON t1.association = main_link_table.association
INNER JOIN many_to_many_table AS t2 ON t2.association = some_table.association
WHERE t1.RandomProperty = 'MyValue' AND t2.RandomProperty = 'MyValue'

It's seemingly simple but I can't find a way to achieve it in one single line of linq - using multiple lines to achieve the desired effect returns too much results and I end up having to loop through them. I also tried stuff like:

var results = main_link_tbl.Include("some_table.many_to_many_table")
                           .Where(l => l.some_table.many_to_many_table.<property>
                                       == "MyValue")

But at this point I can't select a property of many_to_many_table unless I add a FirstOrDefault(), which nullifies the effect since it won't search through all the records.

What did work, but requires multiple lines of code and in the background returns too many results in the SQL query built by the linq-to-entities framework:

var results = db.main_link_table.Include("some_table")
                                .Include("some_table.many_to_many_table")
                                .Where(s => s.some_table.RandomProperty 
                                            == "myValue")
                                .Select(s => s.some_table);

foreach(var result in results) {
    var match_data = result.Where(s => s.many_to_many_table.RandomProperty
                                       == "myValue");
}

This piece of code will return all rows inside some_table that match the first Where condition and then applies the next Where condition, while I obviously only need a single row where the many_to_many_table.RandomProperty equals myValue.

Answer 1

It should work if you change the inner Where to Any:

var results = context.main_link_table
                     .Where(l => l.some_table.RandomProperty == "myValue" &&
                                 l.some_table.many_to_many_table
                                  .Any(m => m.RandomProperty == "myValue"));

Answer 2

If you want to do a join, why don't you just do a join?

var query = from main in context.MainLinks
            join t1 in context.Some on main.Association equals t1.Association
            where t1.RandomProperty == "MyValue"
            join t2 in context.ManyToMany on t1.Association equals t2.Association
            where t2.RandomProperty == "MyValue"
            select new { main, t1, t2 };

That should achieve exactly what your SQL does...