c# LINQ query filling a Tuple List

Question

I need to fill a Tuple-List with values from a MSSQL-Database with help of Linq to Entities. The code snippet below will help to get a Tuple-List where 1 Database row reflects 1 Tuple Entry. In case there are 3 rows for calculateData we would get 3 Tuple Entries in that list created with Field1 to Field4.

The code below makes it possible:

var queryResult = (from a in calculate
                   join b in calculateData on a.Id equals b.CalcId into c
                   where a.SpecialID == 2023 && a.VersionId == 1
                   orderby a.InternalOrderNr ascending
                   select new
                   {
                        a.Field1,
                        a.Field2,
                        a.Field3,
                        myField4 = c.Select(d => new {
                                    d.Field1, d.Field2, d.Field3, d.Field4})
                    }).ToList();

var result = queryResult.Select(r => new Storage
{
    myField1 = r.Field1,
    myField2 = r.Field2,
    myField3 = r.Field3,
    myField4 = r.myField4.Select(t => new Tuple<int,int,decimal,string>
        (
            t.Field1,
            t.Field2,
            t.Field3,
            t.Field4)
        ).ToList()
});

return result;

But what I need is a bit different. I need for those 3 database rows in total 9 Tuple Entries in the list. Example:

Database contains 4 Columns (Column1, Column2, Column3, Column4) per row.
And we have 3 Rows.

Tuple 1: DB-Row1 and Column1, Column2, 0, 0
Tuple 2: DB-Row1 and Columm2, Column3 ,0 ,1
Tuple 3: DB-Row1 and Columm3, Column4 ,0 ,2
Tuple 4: DB-Row2 and Column1, Column2, 1, 0
Tuple 5: DB-Row2 and Columm2, Column3 ,1 ,1
Tuple 6: DB-Row2 and Columm3, Column4 ,1 ,2
Tuple 7: DB-Row3 and Column1, Column2, 2, 0
Tuple 8: DB-Row3 and Columm2, Column3 ,2 ,1
Tuple 9: DB-Row3 and Columm3, Column4 ,2 ,2

So the difference here is that there is not a 1:1 Mapping between database row and Tuple. I need to make several Tuple out of 1 Database row. In the example above we have 3 Tuple entries, but it can differ and can be more or less.

What do you mean by 0 in Tuple 1: DB-Row1 and Column1, Column2, 0, 0? And did you mean by col1, col2, col0(see previous question), col0 of the first row? — Xiaoy312
0 is for example a static value. So we would need 3 Tuple creations for 1 database row. So simplified it means:<br /> Tuple.Creation(Column1, Column2, 0, 0) — Maik
So you want every possible combination of the values from 2 columns from each row in your result set, each of them stored in a 2-tuple, does that sound right? The solution would need to handle any number of rows or columns, but it would always use a 2-tuple, right? — Zack
Also, I'm not following your example of expected output where sometimes Row1 Column2 is 0, and sometimes it is 1. Maybe I am missing something there? — Zack
I need to create several tuples in a list per selected database row. Meaning with easy words without linq I would do Tuple.Create(Col0, Col1) and then Tuple.Create(Col2,Col3) and then Tuple.Create(Col4,Col5). All 3 Tuples are then part of my list. But all from 1 row out of the database. — Maik

Andrey Tretyak Andrey Tretyak · Accepted Answer · 2016-04-27T14:09:11

I guess something like this should work for you:

var queryResult = (from a in calculate
                   join b in calculateData on a.Id equals b.CalcId into c
                   where a.SpecialID == 2023 && a.VersionId == 1
                   orderby a.InternalOrderNr ascending
                   select new
                   {
                        a.Field1,
                        a.Field2,
                        a.Field3,
                        myField4 = c.Select(d => new {
                                    d.Field1, d.Field2, 
                                    d.Field3, d.Field4
                                    d.Field5, d.Field6})
                    }).ToList();

var result = queryResult.Select(r => new Storage
{
    myField1 = r.Field1,
    myField2 = r.Field2,
    myField3 = r.Field3,
    myField4 = r.myField4.SelectMany(t => new [] 
    {
        Tuple<int,int>(t.Field1, t.Field2),
        Tuple<int,int>(t.Field3, t.Field4),
        Tuple<int,int>(t.Field5, t.Field6)
    }).ToList()
}).ToList();

return result;

Alternative version with double 'foreach' loop should work in the same way but it will not create redundant arrays of tuples:

var queryResult = (from a in calculate
                   join b in calculateData on a.Id equals b.CalcId into c
                   where a.SpecialID == 2023 && a.VersionId == 1
                   orderby a.InternalOrderNr ascending
                   select new
                   {
                        a.Field1,
                        a.Field2,
                        a.Field3,
                        myField4 = c.Select(d => new {
                                    d.Field1, d.Field2, 
                                    d.Field3, d.Field4
                                    d.Field5, d.Field6})
                    }).ToList();

var result = new List<Storage>();
foreach(var row in queryResult){
  var storage = new Strorage
  {
    myField1 = r.Field1,
    myField2 = r.Field2,
    myField3 = r.Field3,
    myField4 = new List<Tuple<int,int>>()
  };
  foreach(var subRow in row.myField4)
  {
    storage.myField4.Add(Tuple<int,int>(t.Field1, t.Field2));
    storage.myField4.Add(Tuple<int,int>(t.Field3, t.Field4));
    storage.myField4.Add(Tuple<int,int>(t.Field5, t.Field6));
  }
}

return result;

c# LINQ query filling a Tuple List

1 Answers