Click here to Skip to main content
15,884,628 members
Please Sign up or sign in to vote.
4.11/5 (2 votes)
See more:
I have one table Reference with value as follows

ReferenceId	Name	Desc
1	         Ref1	test1
2	         Ref2	test2
3	         Ref3	test3
4	         Ref4	test4
5	         Ref5	test5
6	         Ref6	test6


Other table as follows RefContext
(In this table childlist column contains Id of ReferenceId from above Reference Table

ContextId    ReferencId   ChildList
1             1            2,3
2             4            1,5,6

I want to join above to tables and I want Output as follows

ReferenceName       ReferenceList
Ref1                 Ref2, Ref3
Ref4                 Ref1, Ref5, Ref6


I want to do this using LINQ. please help me in this.
Posted
Updated 14-Apr-14 21:47pm
v2

C#
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);
}
 
Share this answer
 
v2
Comments
PrachiMhatre 16-Apr-14 5:22am    
Thanks so much Emre Ataseven for your solution. It worked for me. Thank u :)
Emre Ataseven 16-Apr-14 6:22am    
You are welcome :)
You have a wonderful opportunity to re-design your RefContext table!
However, you if cannot (or you don't want to) do it, then have a look at this page: LINQ: Query Comma Separated Value (CSV) files[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900