Custom Sort Based On Referenced Records

Question

Please consider these data:

Id           F1          F2          Ref_ID
------------------------------------------- 
1            Nima        35           Null
2            Eli         33           Null
3            Arian       5            1
4            Ava         1            1
5            Arsha       3            2
6            Rozhan      30           1
7            Zhina       20           2

I want to sort this table like this result:

Id           F1          F2          Ref_ID
------------------------------------------- 
1            Nima        35           Null
3            Arian       5            1
4            Ava         1            1    
6            Rozhan      30           1
2            Eli         33           Null
5            Arsha       3            2
7            Zhina       20           2

the refrenced records should place under the reference record based on Id ascending.

How I can do this using LINQ or SQL. Thanks

Answer 1

In SQL you could sort it by using a COALESCE or ISNULL for the Ref_id and the Id.

And an IIF or a CASE WHEN to make sure the parent id comes first in the same group of Ref_id.

SELECT Id, F1, F2, Ref_ID
FROM YourTable
ORDER BY COALESCE(Ref_ID, Id), IIF(Ref_ID IS NULL, 0, 1), Id;

A test on db<>fiddle here