I use linq2sql and m_DateContext.Dates class for table
ID | ParentID | Datestamp
---|----------|----------
1 | NULL | 1.8.2010
---|----------|---开发者_Python百科-------
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
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)
}
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();
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)
};
精彩评论