A little help for nested query

[Kaeswurm_Thomas (Thomas Kaeswurm)]

I have a folder structure made with closure table.
The SQL query is easy.

query for root folders:

select * from Ordner
where OrdnerId in
  (select Child from ClosureTable
   group by Child
   having count(Child)=1)

query for child folders:

select * from Ordner
where OrdnerId in
  (select Child from ClosureTable
   where Parent = '00000000-0000-0000-0000-000000000000'
   and Deep = 1)

2 problems:

1. the nested query
2. the "having" argument

to 1) I can make the inner query und a loop with all found entries and assemble my list manually
to 2) no idea

[wlandgraf (Wagner Landgraf)]

For such complex queries, have you considered simply using a raw SQL expression like this:

Filtering data with two many valued associations

In this case you should use SQL expressions, using, for example, EXISTS clause. Pseudo-code: Find<TAnagrFiscale> .Where(Linq.Sql('Exists SELECT * FROM AnagrIndirizzo AI WHERE field like '%' and AI.Id = {ID}'))

[Oleg_Kyrylenko]

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

[Kaeswurm_Thomas (Thomas Kaeswurm)]

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;

Thank you