sometimes, to make query more simple and readable, I just create a small function (if applicable of course). example: select * from History with (nolock) where [dbo].fn_GetNumStopsForRide(RideId)=3
in you case it would be:
select * from Ordner where [dbo].fn_GetNumChild(OrdnerId)=1
CREATE FUNCTION [dbo].[fn_GetNumChild]( @ChildID INTEGER
)
RETURNS integer
AS
BEGIN
DECLARE @TotalChild integer
Select @TotalChild= Count(1) FROM ClosureTable with(nolock) WHERE (Child = @ChildID) and
Parent = '00000000-0000-0000-0000-000000000000' and Deep = 1
Return @TotalChild
END
Well, first I'll take this code. But later I want to get rid of the SQL code from the module.
if LOrdnerId.IsEmpty then
Result := ObjManager.Find<TOrdner>
.Where( Linq.Sql('doko_id in (select ct_child from t_dok_closuretable '+
'group by ct_child having count(ct_child)=1)'))
.List
else
Result := ObjManager.Find<TOrdner>
.Where( Linq.Sql('doko_id in (select ct_child from t_dok_closuretable '+
'where ct_parent='''+GetGuidString( LOrdnerId, guid36)+''' and ct_deep=1)'))
.List;