我有一个一对多映射到表具有多对多映射到另一个表的表。我想做到以下几点:
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"));
我怎样才能做到这一点?第一部分工作,但没有内部WHERE尝试它的时候,我无法访问many_to_many_table的属性,但内,其中显然不会编译。我基本上要实现类似下面的SQL查询:
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'这看似简单,但我不能找到一种方法来实现它在LINQ的一个单行 - 使用多行来实现预期的效果返回太多的结果和我最终通过他们不得不循环。我也试过一样的东西:
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")不过,在这一点上,除非我添加了一个FirstOrDefault(),它抵消因为它不会在所有的搜索记录的影响,我不能选择many_to_many_table属性。
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.
什么做的工作,但需要多行代码,并在后台返回的LINQ到实体框架构建SQL查询的结果太多:
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"); }这一段代码将返回内部some_table所有行匹配第一个WHERE条件然后应用下一个Where条件,而我明明只需要一个单行,其中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.
推荐答案据如果你改变了内应工作其中,到任何:
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"));更多推荐
LINQ到实体,where子句在哪里? (内哪里)
发布评论