开发者

Efficient way to select records with children?

I have a LINQ to SQL query that is something like the following...

return from parent in Context.Parents
       where 
       (
          (parent.SomeProperty != null && parent.SomeProperty != "")
          ||
          (Context.Childs.Count(c => c.ParentID == parent.ID && c.Type == "SomeType") > 0)
       )
       select parent

The idea is that I want to find all the parent records that have either got a value for "SomeProperty" or that have some child records of type "SomeType".

The problem is tha开发者_如何学Got the query is timing out. Is there a quicker (but still easy to read) way of doing the same thing?

Thanks for reading.


Use Any() instead of Count():

return from parent in Context.Parents
       where 
       (
          (parent.SomeProperty != null && parent.SomeProperty != "")
          ||
          Context.Childs.Any(c => c.ParentID == parent.ID 
                                  && c.Type == "SomeType")
       )
       select parent;

In Linq to SQL Count(<some condition>) is translated into:

SELECT COUNT(*) WHERE <some condition>

query which requires iterating over all rows in the database to find the count.

In Linq to SQL Any(<some condition>) is translated to a

EXISTS (.. <some condition>)

subquery which allows short circuiting the result once the first match is found.

The exact SQL mapping can be found in this SO answer: query result what should i use Count() or Any()

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜