var dtReference = new DataTable("Reference");
var dtRefContext = new DataTable("RefContext");
var dtOutput = new DataTable("Output");
dtReference.Columns.AddRange
(
new DataColumn[]
{
new DataColumn("ReferenceId", typeof (int)),
new DataColumn("Name", typeof (string)),
new DataColumn("Desc", typeof (string))
}
);
dtRefContext.Columns.AddRange
(
new DataColumn[]
{
new DataColumn("ContextId", typeof (int)),
new DataColumn("ReferenceId", typeof (int)),
new DataColumn("ChildList", typeof (string))
}
);
dtOutput.Columns.AddRange
(
new DataColumn[]
{
new DataColumn("ReferenceName", typeof (string)),
new DataColumn("ReferenceList", typeof (string))
}
);
for (int i = 1; i <= 6; i++)
{
var rowRef = dtReference.NewRow();
rowRef["ReferenceId"] = i;
rowRef["Name"] = "Ref" + i;
rowRef["Desc"] = "test" + i;
dtReference.Rows.Add(rowRef);
}
var rowCon = dtRefContext.NewRow();
rowCon["ContextId"] = 1;
rowCon["ReferenceId"] = 1;
rowCon["ChildList"] = "2,3";
dtRefContext.Rows.Add(rowCon);
rowCon = dtRefContext.NewRow();
rowCon["ContextId"] = 2;
rowCon["ReferenceId"] = 4;
rowCon["ChildList"] = "1,5,6";
dtRefContext.Rows.Add(rowCon);
var _aa =
from a in dtReference.AsEnumerable()
from b in dtRefContext.AsEnumerable()
where b.Field<int>("ReferenceId") == a.Field<int>("ReferenceId")
select new
{
tempName = a.Field<string>("Name"),
tempNameList = b.Field<string>("ChildList").Split(',').Select(int.Parse)
};
var _bb =
from a in dtReference.AsEnumerable()
from b in _aa
where b.tempNameList.Contains(a.Field<int>("ReferenceId"))
select new
{
tempName = b.tempName,
tempNameList = a.Field<string>("Name"),
};
var result =
from r in _bb
group r by r.tempName into g
select new
{
ReferenceName = g.Key,
ReferenceList = string.Join(",", g.OrderBy(s => s.tempName)
.Select(s => s.tempNameList))
};
foreach (var item in result)
{
var rowOut = dtOutput.NewRow();
rowOut["ReferenceName"] = item.ReferenceName;
rowOut["ReferenceList"] = item.ReferenceList;
dtOutput.Rows.Add(rowOut);
}