LINQ update value in parent rows from ther child rows

Question

I use linq2sql and m_DateContext.Dates class for table

ID | ParentID | Datestamp
---|----------|----------
1  | NULL     | 1.8.2010
---|----------|----------
2  | 1        | 2.8.2010
---|----------|----------
3  | 1        | 4.8.2010

etc...

I need to write the linq query to select only rows where ParentID is NULL (ex: ID=1), BUT Value of DATESTAMP must be equals to max date from it's child rows. (Row ID=3).

Linq query must return an instance of m_DateContext.Dates with non-broken links to entities in database schema.

Result must be smthng like:

ID = 1 ParentID=NULL Datestamp = 4.8.2010

Please help

Answer 1

var rows =
(
from parent in db.Dates
where parentId == null
let MaxDate = parent.Dates.Max(child => child.DateStamp)
select new{DateObject = parent, MaxDate = MaxDate}
)

List<Date> result = new List<Date>();
foreach(var row in rows)
{
  Date d = row.DateObject;
  d.DateStamp = row.MaxDate;
  result.Add(d)
}

Answer 2

var query = 
    from p in TDates 
    where p.ParentID==null 
    select new TDate 
    { 
        ID = p.ID, 
        ParentID = null, 
        Datestamp = TDates.Where(c=> c.ParentID == p.ID).Max(c=>c.Datestamp)
    };

Answer 3

All kinds of assumptions (linq to entities or sql) and I think you want a let...

var query = (
     from i in TDates
        where i.ParentID==null
     let maxDate= TDates.Max(d=>d.Datestamp) 
     select new {
       NonBrokenRecord = i,
       Datestamp = maxDate
     }

).ToList();

Once you get the results you could do...

query.ForEach(x=>x.NonBrokenRecord.DateStamp = x.Datestamp);
query = query.Select(x=>x.NonBrokenRecord).ToList();