How to split a string in a datatable and add into new rows using Linq C#?

Question

I am new to linq. I am using linq to get vlaues from datalist control and creating a datatable with that values. The datatable contains three columns. In that, sometimes the 'Answer' column is having a data which is comma separated. You can see the image of datatable with comma separated column

            DataTable SaveAnswer = clsSource.SetData();
            var df = from DataListItem dli in PollDataList.Items
                     let z = ((HiddenField)dli.FindControl("IDReqHiddenField")) 
                     let y = ((RadioButtonList)dli.FindControl("rdblstPollOptions"))
                     let v = ((CheckBoxList)dli.FindControl("CheckBoxListMultiplePollOptions"))
                     select new
                     {
                         PollId=z.Value,
                         Answered1 = y.SelectedValue,
                         Answered2=v.Items.Cast<ListItem>().Where(r=>r.Selected)
                     };
           var result = from p in df
                         select SavePollAnswer.LoadDataRow(
                            new object[] { 
                                p.PollId,p.Answered1+string.Join( ", ", p.Answered2 ),""
                            },
                            false);
            SavePollAnswer = result.CopyToDataTable();

And here is my design

<asp:DataList ID="PollDataList" runat="server"    CssClass="poll-preview">
        <ItemTemplate>
            <asp:HiddenField ID="PollIDReqHiddenField" Value='<%# Eval("PollID") %>' runat="server" Visible="false" />
            <asp:Label ID="lblReqQuestionNumber" runat="server" Text='<%# Eval("NoofPollQuestion") %>' Font-Bold="true"></asp:Label><br />
            <asp:Label ID="lblRequiredPollQusetion" runat="server" Text='<%# Eval("PollQuestions") %>' Font-Bold="false"></asp:Label>
            <asp:Label ID="lblReqNotification" runat="server" ForeColor="Red" CssClass='<%# Eval("PollReq") %>' Text="*" Font-Bold="true"></asp:Label>
            <asp:RadioButtonList ID="rdblstPollOptions" DataSource='<%# Eval("PollOptionsReq") %>' runat="server"></asp:RadioButtonList>
            <asp:CheckBoxList ID="CheckBoxListMultiplePollOptions" DataSource='<%# Eval("PollOptionsMul") %>'  runat="server" RepeatDirection="Vertical" RepeatColumns="1"></asp:CheckBoxList>
        </ItemTemplate>
    </asp:DataList>

But we want the datatable output like this Expected Output

Thanks in advance!

Would I be stating the obvious with this? Split the ones with 2 values and add new 2 new rows.
I think I have to explain it more clearly...In the first image you can see the "PollAnswer" column having selected options(2,3) for the "PollID" (36). But the output I need should not be like this (36->2,3).It should be 36->2,36->3(you can see in second image).
Yes, I see that. Where you build your new object[] you need to see if p.Answered2 is not empty/null. If not, meaning there is a comma separated value to view in separate rows, perform the first insert with a new object with p.Answered1, then again with another row and p.Answered2.

Simon Buchan · Answer 1 · 2013-06-07 11:59:06Z

LINQ can get you partway by allowing you to nest from clauses in queries (simplifying the output a bit):

from DataListItem dli in PollDataList.Items
let z = ... // as in existing code
let y = ...
let v = ...
from checkbox in v.Items
where checkbox.Selected
select new [] { z.Value, checkbox.Value, "" }

Here you will get an output item for each selected checkbox in each poll item (your Answered2). But you also want an output row for the Answered1 value for each poll item, and here LINQ is less helpful. There are two ways you can do this, the quick and ugly way using Enumerable.Concat:

...
let v = ...
from answer in new [] { y.SelectedValue }.Concat(v.Items.Where(r => r.Selected)) // http://msdn.microsoft.com/en-us/library/bb302894.aspx
select new [] { z.Value, answer, "" }

Or the clearer and more general, but longer way, using yield to create an iterator method that gives you an item for all the answers:

from DataListItem item in PollDataList.Items
let z = ...
from answer in PollItemAnswers(item)
...
// outside the method containing the above
IEnumerable<object> PollItemAnswers(PollListItem item)
{
    var y = ...; // As in let statements
    var v = ...;
    yield return y.Value; // return the first answer
    foreach (var checkBox in v.Items)
        if (checkBox.Selected)
            yield return checkBox.Value; // return the other answers
}

And finally, if your final code don't have the items already separated (say, it was posted from a web form), you can use string.Split():

 yield return y.Value;
 foreach (var answer in value.Split(',')) // "a, b, c" -> "a", " b", " c"
     yield return answer.Trim(); // " b" -> "b"

As a final note, watch out for if your answers could contain ,! In general, using something like JSON to safely move value collections around (say from a file or another machine) will save you a lot of grief.

Thank you. I unable to apply your piece of code..Can you please guide me to figure this out.
@Sudha: I can't answer questions about how DataTables work, but you seem to have gotten that far already. If it's not clear, ... is where your existing code would be, I've removed what isn't required for the answer. Other than that, the only difference is that I combined the df and result queries.
@Sudha: I've made a few changes to the example code, where it wasn't clear what was being skipped as being unimportant to the answer. Perhaps I'm not understanding what PollDataList.Items is containing? Are you having trouble getting the results of the query into SavePollAnswer?
Thank you for your reply. I have added design also. PollDataList is a datalist which contains hidden field, radiobuttonlist and checkboxlist controls. While reading values from datalist, I got selected values of CheckBoxList as ListItem(Answered2). I want these values in DataTable. So when I copy query to datatable, I made list items as comma separated string. I need this not as comma separated but individual row with respective pollId.

asked	today
viewed	80 times
active	today

How to split a string in a datatable and add into new rows using Linq C#?

1 Answer

Your Answer

Not the answer you're looking for? Browse other questions tagged c# asp.net linq or ask your own question.

Community Bulletin

How to split a string in a datatable and add into new rows using Linq C#?

1 Answer

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged c# asp.net linq or ask your own question.

Community Bulletin

Related