This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi I have a query:
var orphanedOwners = _domainRepository.Owners.Where(o => !o.Controls.Any(c => c.OwnerId == o.Id)).AsEnumerable(); which produces the following sql:
SELECT Owner.Id AS [Owner.Id], Owner.Name AS [Owner.Name], Owner.OwnerRoleId AS [Owner.OwnerRoleId], Owner.StaffId AS [Owner.StaffId], Owner.Status AS [Owner.Status] FROM Owner WHERE NOT (EXISTS ( SELECT Control.* FROM Control WHERE Control.OwnerId = Owner.Id AND Control.OwnerId = Owner.Id )) I notice the where clause repeats some logic (in bold). Is this a minor bug or is there a better approach to my query? Cheers |
|
|
|
|
Any thoughts? |
|
|
|
|
I think all we're doing is translating your query. Your query, if I've understood it right, contains some redundancy, and the generated SQL reflects that redundancy -- we're not smart enough to optimise it away. The redundancy comes from this: Where(o => !o.Controls.Any(c => c.OwnerId == o.Id)) Assuming OwnerId is the foreign key from Control to Owner, any entity in o.Controls will have OwnerId equal to o.Id -- otherwise it wouldn't be in o's Controls collection! Put another way, the first OwnerId check in the SQL is reflecting membership in o.Controls, and the second is reflecting your manual OwnerId check. So you should be able to rewrite this as Where(o => !o.Controls.Any()) and eliminate the redundancy without changing the result. |
|
|
|
|
Thanks Ivan, makes perfect sense. |
|
|
|
