Use linq to get parent objects based on one of the property in a self referencing table.

Question

Hello, I have a table called Quiz that have these fields

id As Int
created As DateTime
header As Sring
body As String
fk_parent As int
url As String

All the items without parent key would be the question, and ones that have the parent key would be the answer. I am having the problem to get all the latest active questions (based both on questions created time and and answer created time).

I am struggling to write a Linq query that can do the above task.

Answer 1

Here's a start:

IQueryable<int> keys =
    from x in dc.Quiz
    let masterID = ParentId.HasValue ? ParentId.Value : Id
    group x by masterID into g
    order g by g.Max(x => x.created) descending
    select g.Key

List<Quiz> = dc.Quiz.Where(x => keys.Take(20).Contains(x.Id)).ToList();

This assumes answers aren't parents of answers... If they can - you have an arbitrary depth tree walk on your hands, which is a wrong shaped nail for Linq and for SQL.

Answer 2

You could try joining the table on itself in LINQ and creating a new object that holds the Questions and answers:

var QandA = from q in tbl
     join a in tbl on q.id equals a.fk_parent
     select new {
     QHeader = q.header,
     QBody = q.body,
     QUrl = q.url,
     AHeader = a.header,
     ABody = a.body,
     AUrl = a.url
     };

I think this is how your table is setup, but I might have the join wrong.