3

I have 2 database tables.

In Table1 Calculate I have 1 row which is mapped via an Id to multiple rows in table2 CalculdateData.

Now I need to load the data from table1 Calculate with all relevant Details from Table2 CalculdateData.

How would i have the Details into a Tuple-List.?

So basically for CalculateData I have 4 columns per row which I Need to put into a Tuple. Meaning if I would have for example 4 rows i need to create 4 Tuples in a List.

IEnumerable<Storage> context = new MyEntities();

var Result = (from a in context.calculate
             join b in context.CalculateData on a.Id equals b.CalcId into c
             where a.SpecialID == 2023 && a.VersionId == 1
             orderby a.InternalOrderNr ascending
             select new Storage
             {
                myField1 = a.Field1;
                myField2 = a.Field2;
                myField3 = a.Field3;
                < MISSING PART AND QUESTION >
             }).ToList();

            return Result;


public class Storage
{
            public int myField1;
            public int myField2;
            public int myField3;
            public List<Tuple<int, int, string, decimal>> myField4;
}
Eldho
  • 7,795
  • 5
  • 40
  • 77
Maik
  • 1,582
  • 3
  • 12
  • 13

2 Answers2

1

This should work:

        var Result = (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 Storage
                      {
                          myField1 = a.Field1,
                          myField2 = a.Field2,
                          myField3 = a.Field3,
                          myField4 = c.Select(d => new Tuple<int, int, string, decimal>
                                                   (d.Field1, d.Field2, d.Field3, d.Field4))
                                                   .ToList()
                      }).ToList();

        return Result;

It also would be good thing to check that this query transforms in single sql request and you not making new sql request on each tuple list creation.

Edit: In case you will have problems with custom types in query (as @Toxantron pointed) this selection should work:

    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();
    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;
Andrey Tretyak
  • 3,043
  • 2
  • 20
  • 33
  • 2
    I am not sure about latest Entity Framework, but till EF 6.1 it was not possible to use non-default constructors in Linq2Entities. – Toxantron Apr 26 '16 at 10:38
  • 1
    @Toxantron that also my concern, I think it still not possible, and we will have to select anonymous type and transform it after selection. – Andrey Tretyak Apr 26 '16 at 10:49
  • I think only in memory `Select` only be possible – Eldho Apr 26 '16 at 11:00
  • 1
    @Eldho selection with anonymous type should work on database side. There might be a problems with tuple list selection (could create sub request), I guess it could be solved, but can't suggest 100% solution without testing on real database. – Andrey Tretyak Apr 26 '16 at 11:22
  • Hi Guys, great post. It is working with you snippet above. But I have an another tricky task to solve on top of this. Example: This is the Tuple: public List> myField4; The string describes the decimal value. 1,2,Values from Database column 1, ValueColumn1 1,2,Values from Database column 2, ValueColumn2 1,2,Values from Database column 3, ValueColumn3 Is it clear what I'm trying to say? – Maik Apr 26 '16 at 12:06
  • @Maik sorry but it's not clear for me, may be you can explain it in different way of add it to your question – Andrey Tretyak Apr 26 '16 at 13:28
  • As the original answer has been given in this context I have created an extended query under: http://stackoverflow.com/questions/36875107/c-sharp-linq-query-filling-a-tuple-list – Maik Apr 26 '16 at 20:29
0

You could try something like this.This is not tested yet.

select new Storage
 {
         myField1 = a.Field1,
         myField2 = a.Field2,
         myField3 = a.Field3,
         myField4 = c.Select(d => new Tuple<int, int, string, decimal>(d.Field1, d.Field2, d.Field3, d.Field4)).ToList()
 }).ToList();

This is totally based on this This tutorial

Eldho
  • 7,795
  • 5
  • 40
  • 77
  • No, this is not what I want. The Fields myField1, myField2 and myField3 containing 1 value and the myField4 can contain several values. myField4 is a List (List> myField4) Check out my Class Storage which I defined. – Maik Apr 26 '16 at 10:25