CODEWITHSUNDEEP
c#linqentity-frameworklambdalinq-to-entities
L2Eer
L2Eer

Reputation: 477

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

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.

Upvotes: 16

Views: 20968

Answers (3)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

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"));

Upvotes: 25

abatishchev
abatishchev

Reputation: 100258

from link in db.main_link_table
join s in db.some_table on link.association1 = s.association
join m in db.many_to_many_table on link.association2 = m.association
where s.X = 'MyValue' AND m.Y = 'MyValue'
select m; // or s or link or both 3 as you want

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1500485

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...

Upvotes: 6

Related Questions