我将如何改进用作规范的7行Linq查询? [英] How would I improve this 7 line Linq Query that acts as a Specification?

问题描述

BigQuery获得了一组Products和嵌套的相关表.然后，我尝试对规范模式进行过滤，但尝试不力.这是过滤器代码.查询中有三个表，我想通过嵌套在底部查询中的值来过滤顶部查询.就像我说的那样，目前可以产生我们想要的结果.

BigQuery at the top gets a set of Products and nested related tables. Then, I apply filtering in a poor attempt at a specification pattern. This is the filter code. There are three tables in the query, and I want to filter the top query by the value nested in the bottom query. Like I said, this currently produces the results we want.

但是，.Contains()为每个对象生成一个SQL WHERE EXISTS()子句.我们确实只需要一个，但是我不知道如何获取内部ID与外部ID进行比较.

However, the .Contains() produces a SQL WHERE EXISTS() clause for each. We really only need one, but I don't know how to get the inner ID to compare with the outer ID.

from p in bigQuery                            // Root table
where ( from pp in p.LPP                      // Level 1 nested table
        where (from pv in pp.LPV              // Level 2 nested table 
               where pv.colorid == intValue   // Our filter value
               select p.id).Contains(p.id)    // Where exists
        select p.id).Contains(p.id)           // Where exists
select p;

有什么想法吗?这将按原样产生一条900行的SQL语句，到目前为止，我们只有一个过滤器.

Any thoughts? This produces a 900 line SQL statement as-is, and we only have one filter so far.

推荐答案

from p in bigQuery
where p.LPP.SelectMany(pv => pv.LVP).Any(x => x.colorid == intValue)
select p;

据我所见，以上内容应等效.请尝试一下并检查生成的SQL(如果可行).在任何情况下，都应该相距不远.

From what I can see, the above should be equivalent. Please try it and inspect the SQL generated, if working. Any case, it should not be far off.

这篇关于我将如何改进用作规范的7行Linq查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！