Linq: datatable queries

Posted on 2011-10-06
Last Modified: 2012-05-12
Given two datatables

Table1 (Id, etc)
Table2(id, prod, etc)

I want to merge the two table in such a way that will add a column to table1 "prod" that contains a comma delimited list of values from prod 2

While the code attach gets me the desired result.

Does anybody have a better suggestion on how to do this?

DataTable dtDealers = ds.Tables[DTDEALERLIST].Copy();
dtDealers.Columns.Add("Products", typeof (string));

var res = from d in ds.Tables[DTDEALERLIST].AsEnumerable()
                                  join p in ds.Tables[DTPRODUCTS].AsEnumerable() on d.Field<int>("dealerList_Id") equals p.Field<int>("dealerList_Id") into r
                                  from p in r.DefaultIfEmpty()
                                  select new
                                                 ID = p.Field<int>("dealerList_Id"),
                                                 List = r.ToList().Aggregate(new StringBuilder(), (sb, l) => sb.Append(l.Field<string>("products_Text")).Append(","), sb =>
                                                                                                                                                                              if (sb.Length > 0) sb.Length--;
                                                                                                                                                                              return sb.ToString();

                        var dis = res.Distinct();

                        foreach(var d in dis)
                            dtDealers.Rows[d.ID]["Products"] = d.List;

Open in new window

Question by:acadenilla
    LVL 96

    Expert Comment

    by:Bob Learned
    It seems like you have everything that you need.  What kind of help do you need?

    Author Comment


    Peer code review i guess.

    Curious to know if anybody had better suggestion.

    How would you have done it?


    LVL 96

    Accepted Solution

    How would I have done it?  First, no DataTable, second, NHibernate, third, I wouldn't have done this kind of manipulation at this point...
    LVL 16

    Assisted Solution

    There is a merge method you can use take a look at this:

    Author Closing Comment

    thanks guys for the comments.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In one of my recent projects, I was working with IP cameras, I need to take some pictures from the camera and do some processing on it. The first step, was to retrieve the image from camera into Image object. So that it can be displayed or …
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now