Combine not constant tables row values as colum with LINQ

Question

On the basis of the following link Combine tables using row values as column LINQ C# SQL I would combine two IEnumerables using row values as column. Unlike what is written, I am not using tables with constant fields obtained by a web service.

var element = XDocument.Parse(getstring1);

            var dataCode = from item in element.Descendants("Field")
                           where item.Element("Code").Value == "CODE"
                           select item.Element("Value");

            var dataDescription = from item in element.Descendants("Field")
                                  where item.Element("Code").Value == "DESCRIPTION"
                                  select item.Element("Value");

            var resultsSet = dataCode.Zip(dataDescription, Tuple.Create);

            element = XDocument.Parse(getstring2);

            var dataSizeSet = from item in element.Descendants("Field")
                              where item.Element("Code").Value == "SIZESET"
                              select item.Element("Value");

            var dataSizeId = from item in element.Descendants("Field")
                             where item.Element("Code").Value == "SIZEID"
                             select item.Element("Value");

            var dataSizeLabel = from item in element.Descendants("Field")
                                where item.Element("Code").Value == "SIZELABEL"
                                select item.Element("Value");

            var resultsLabel = dataSizeSet.ZipThree(dataSizeId, dataSizeLabel, Tuple.Create);

            var results = resultsSet.GroupJoin(resultsLabel,
                            set => set.Item1,
                            label => label.Item1,
                            (set, label) => new
                            {
                                set.Item1,
                                set.Item2,
                                Label01 = label.SingleOrDefault(x => x.Item2.Value == "1").Item3,
                                Label02 = label.SingleOrDefault(x => x.Item2.Value == "2").Item3,
                                Label03 = label.SingleOrDefault(x => x.Item2.Value == "3").Item3,
                                Label04 = label.SingleOrDefault(x => x.Item2.Value == "4").Item3,
                                Label05 = label.SingleOrDefault(x => x.Item2.Value == "5").Item3,
                                Label06 = label.SingleOrDefault(x => x.Item2.Value == "6").Item3,
                                Label07 = label.SingleOrDefault(x => x.Item2.Value == "7").Item3,
                                Label08 = label.SingleOrDefault(x => x.Item2.Value == "8").Item3,
                                Label09 = label.SingleOrDefault(x => x.Item2.Value == "9").Item3,
                                Label10 = label.SingleOrDefault(x => x.Item2.Value == "10").Item3,
                                Label11 = label.SingleOrDefault(x => x.Item2.Value == "11").Item3,
                                Label12 = label.SingleOrDefault(x => x.Item2.Value == "12").Item3,
                                Label13 = label.SingleOrDefault(x => x.Item2.Value == "13").Item3,
                                Label14 = label.SingleOrDefault(x => x.Item2.Value == "14").Item3,
                                Label15 = label.SingleOrDefault(x => x.Item2.Value == "15").Item3,
                                Label16 = label.SingleOrDefault(x => x.Item2.Value == "16").Item3,
                                Label17 = label.SingleOrDefault(x => x.Item2.Value == "17").Item3,
                                Label18 = label.SingleOrDefault(x => x.Item2.Value == "18").Item3,
                                Label19 = label.SingleOrDefault(x => x.Item2.Value == "19").Item3,
                                Label20 = label.SingleOrDefault(x => x.Item2.Value == "20").Item3
                            });

The var "results", if I expand the IEnumerable, contains nothing, and get the following error message: "object reference not set to an instance of an object".

Any idea to suggest me?

I believe your question will be closed soon as duplicate. But for future - don't just dump all your code. Add some description *what* your code does and provide some xml for reproducing your issue — Sergey Berezovskiy, Feb 22 '17 at 10:35
In my opinion it is not a duplicate, but it's an integration — Mattia Trombon, Feb 22 '17 at 10:39
I don't know what do you call as *integration* but it's duplicate of http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it?rq=1 — Sergey Berezovskiy, Feb 22 '17 at 10:43
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — M. Adeel Khalid, Feb 22 '17 at 10:47

score 0 · Answer 1 · answered Feb 22 '17 at 10:41

0

You're using label.SingleOrDefault() which may return null, and then calling .ItemN on the result, which would fail with 'object reference not set to an instance of an object' in that case.

answered Feb 22 '17 at 10:41

Jon

309
3
10

So, what method do i have to use? Thanks for your answer! – Mattia Trombon Feb 22 '17 at 10:44
It really depends on the detail of what you're trying to do. E.g. if Label01 is nullable, and you're using C#6 you could write "Label01 = label.SingleOrDefault(x => x.Item2.Value == "1")?.Item3" which would set Label01 to null if x.Item2.Value != "1". – Jon Feb 22 '17 at 10:48

score 0 · Answer 2 · answered Feb 22 '17 at 11:08

You want a pivot table. Code below updates your 1st table with the info from 2nd table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable personTbl = new DataTable();
            personTbl.Columns.Add("Id", typeof(int));
            personTbl.Columns.Add("Name", typeof(string));
            personTbl.Columns.Add("Age", typeof(int));

            personTbl.Rows.Add(new object[] { 1, "Steve", 21});
            personTbl.Rows.Add(new object[] { 2, "Jack", 17});
            personTbl.Rows.Add(new object[] { 3, "Alcice", 25});
            personTbl.Rows.Add(new object[] { 4, "Harry", 14});

            DataTable personInfo = new DataTable();
            personInfo.Columns.Add("UId", typeof(int));
            personInfo.Columns.Add("Key", typeof(string));
            personInfo.Columns.Add("Value", typeof(string));

            personInfo.Rows.Add(new object[] { 1, "Height", "70"});
            personInfo.Rows.Add(new object[] { 2, "Height", "65"});
            personInfo.Rows.Add(new object[] { 2, "Eyes", "Blue"});
            personInfo.Rows.Add(new object[] { 4, "Height", "51"});
            personInfo.Rows.Add(new object[] { 3, "Hair", "Brown"});
            personInfo.Rows.Add(new object[] { 1, "Eyes", "Green"});

            List<string> infoType = personInfo.AsEnumerable().Select(x => x.Field<string>("Key")).Distinct().ToList();

            foreach (string type in infoType)
            {
                personTbl.Columns.Add(type, typeof(string));
            }

            Dictionary<int, List<DataRow>> dict = personInfo.AsEnumerable().GroupBy(x => x.Field<int>("UId"), y => y)
                .ToDictionary(x => x.Key, y => y.ToList());

            foreach (DataRow row in personTbl.AsEnumerable())
            {
                int id = row.Field<int>("Id");
                if(dict.ContainsKey(id))
                {
                   List<DataRow> rowInfo = dict[id];
                   foreach (DataRow col in rowInfo)
                   {
                       row[(string)col["key"]] = col["Value"];
                   }
                }
            }
        }
    }
}

Combine not constant tables row values as colum with LINQ

2 Answers2