Select Last Childs in a self-joined table in EF

Question

I have a table like this :

Id              int      Not Null  PK Unique
RequestNO       int      Not Null   
RefrenceId      int      Null                  //Self Join to Id

If I have these records in this table :

Id   RequestNO    RefrenceId      
1       H100         NULL
2       H101         NULL
3       H101          2
4       H101          3
5       H100          1
6       H105         NULL

Depend on these records I want to return a list where Id is in { 4 ,5 , 6 }.I want to select last childrens (If any parents have not childs, itself is a child). Is there any one to help me about this ?!!

I am using EF 4 (Database First) , and the table name is Requests

UPDATE1 : I try this , but it failed.

var list = DataContext.Requests.GroupBy(rec => new { rec.ConfirmNo }).Select(rec => rec.FirstOrDefault());

If you need more details comment me. Thanks Ali Foroughi

score 1 · Accepted Answer · edited Dec 16 '12 at 09:20

1

You can try this:

var list = DataContext.Requests.Where(op => !DataContext.Requests.Any(ip => ip.RefrenceId == ip.Id));

edited Dec 16 '12 at 09:20

Ali Foroughi

4,540
7
42
66

answered Dec 16 '12 at 09:07

NaveenBhat

3,248
4
35
48

As I know there is nothing like Exists in EF4 . maybe i shoult to use Any . Am I right ?! – Ali Foroughi Dec 16 '12 at 09:11
You helped me , Thank you dude – Ali Foroughi Dec 16 '12 at 09:15
Ya..there is no extension method called `Exists` in EF4. I tried it with LINQ to Objects and pasted the code. I'm glad it helped you and thanks for the edit. – NaveenBhat Dec 16 '12 at 09:22

Select Last Childs in a self-joined table in EF

1 Answers1