A where clause in a linq to entities clause? (inner location)

c# entity-framework lambda linq linq-to-entities

Question

I have a table that is mapped from one to many to another table that is mapped from many to many. What I would want to do is

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 do this? The first portion will function, but when I attempt it without the "inner WHERE," I am unable to access the attributes of the many-to-many table, and the "inner WHERE" will clearly not compile. I essentially want to do something along the lines of the SQL query below:

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'

Although it seems straightforward, I can't seem to find a method to do it with a single line of linq; instead, I have to loop over the results of using numerous lines to obtain the desired effect. Among other things, I tried:

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

However, I can't choose a property of the many-to-many database at this time without adding a FirstOrDefault(), which negates the impact since it won't look over all the entries.

The SQL query created by the linq-to-entities framework did function, however it required many lines of code and returned too many results in the background:

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

While I plainly just need one row where the many to many table, this piece of code will return all rows within some table that meet the first Where condition and then apply the second Where condition. MyValue is equivalent to RandomProperty.

1
12
6/8/2012 2:59:13 PM

Accepted Answer

If you alter the inner, it ought to function.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"));
20
11/24/2011 2:55:01 PM

Popular Answer

Why don't you simply perform a join if you want to?

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 ought to do everything your SQL does...



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow