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;
}
Improve this question
0

2 Answers 2

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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 that also my concern, I think it still not possible, and we will have to select anonymous type and transform it after selection.
I think only in memory Select only be possible
@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.
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<Tuple<int, int, string, decimal>> 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?
|
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

Improve this answer

1 Comment

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<Tuple<int, int, string, decimal>> myField4) Check out my Class Storage which I defined.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.