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()
精彩评论