# Vertical Sum For Top 3

Hi Experts!

This is a challenging one for me; if I could give 1000+ I would.
Supposed you've a table with this content:
``````Qid        Value
---------- -----
1            0
2            2
3            1
4            4
5            0
6            0
7            1
8            2
9            0
10           4
``````
Here's how to use the content; you want to get A, B, C, D, and E:
``````A = (Qid of 1) + (Qid of 2) = 0 + 2 = 2
B = (Qid of 3) + (Qid of 4) = 1 + 4 = 5
C = (Qid of 5) + (Qid of 6) = 0 + 0 = 0
D = (Qid of 7) + (Qid of 8) = 1 + 2 = 3
E = (Qid of 9) + (Qid of 10) = 0 + 4 = 4
``````
Ultimately you would like to get the top 3 values.
So, they are: B (5), E (4), and D (3)

The actual logic is like this:

We'll need to do this in LINQ; here's the pseudo code:
``````Q q1  = new Q { Qid=1 , Value = "0" };
Q q2  = new Q { Qid=2 , Value = "2" };
Q q3  = new Q { Qid=3 , Value = "1" };
Q q4  = new Q { Qid=4 , Value = "4" };
Q q5  = new Q { Qid=5 , Value = "0" };
Q q6  = new Q { Qid=6 , Value = "0" };
Q q7  = new Q { Qid=7 , Value = "1" };
Q q8  = new Q { Qid=8 , Value = "2" };
Q q9  = new Q { Qid=9 , Value = "0" };
Q q10 = new Q { Qid=10, Value = "4" };

List<Q> Qs = new List<Q> { q1, q2, q3, q4, q5, q6, q7, q8, q9, q10};

var results = from q in Qs ................
select new
{
A =
B =
C =
D =
E =
};

var Top3 = from r in results ..............
``````

Any ideas?
LVL 5
###### Who is Participating?

Systems and Integration DeveloperCommented:
You need to detach your result set from the Entity view of the model. Assuming you still have your Q class kicking around, you can change the line:
``````var answered = from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new
{
Qid = a.QuestionID,
Value = v.ScaleValue
};
``````
To:
``````List<Q> answered = (from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new Q
{
Qid = a.QuestionID,
Value = v.ScaleValue
}).ToList<Q>();
``````
And the rest should then work as before.
0

Systems and Integration DeveloperCommented:
Try this:
``````            List<Q> list = new List<Q>
{
new Q { Qid = 1, Value = 0},
new Q { Qid = 2, Value = 2},
new Q { Qid = 3, Value = 1},
new Q { Qid = 4, Value = 4},
new Q { Qid = 5, Value = 0},
new Q { Qid = 6, Value = 0},
new Q { Qid = 7, Value = 1},
new Q { Qid = 8, Value = 2},
new Q { Qid = 9, Value = 0},
new Q { Qid = 10, Value = 4}
};

var query = from x in list
join y in list on (x.Qid + 1) equals y.Qid
where x.Qid % 2 == 1
select (x.Value + y.Value);

var results = (from x in query
orderby x descending
select x).Take(3);
``````
0

Author Commented:
Thanks pretty clever carl_tawn. The top 3 is correct; but we're unable to determine from which of 'A-E' is it associated with. In our example, the top 3 are: B = 5, E = 4, and D = 3

Can you please take a look at my 'query'? Trying to add three Qids to make this:
A= Qid1 + Qid17 + Qid33, B=Qid2 + Qid18 + Qid34, all the way to P.
``````    List<Q> list = new List<Q>
{
new Q { Qid = 1, Value = 0},
new Q { Qid = 2, Value = 2},
new Q { Qid = 3, Value = 1},
new Q { Qid = 4, Value = 4},
new Q { Qid = 5, Value = 0},
new Q { Qid = 6, Value = 0},
new Q { Qid = 7, Value = 1},
new Q { Qid = 8, Value = 2},
new Q { Qid = 9, Value = 0},
new Q { Qid = 10, Value = 4},
new Q { Qid = 11, Value = 0},
new Q { Qid = 12, Value = 2},
new Q { Qid = 13, Value = 1},
new Q { Qid = 14, Value = 4},
new Q { Qid = 15, Value = 0},
new Q { Qid = 16, Value = 0},
new Q { Qid = 17, Value = 1},
new Q { Qid = 18, Value = 2},
new Q { Qid = 19, Value = 0},
new Q { Qid = 20, Value = 4},
new Q { Qid = 21, Value = 0},
new Q { Qid = 22, Value = 2},
new Q { Qid = 23, Value = 1},
new Q { Qid = 24, Value = 4},
new Q { Qid = 25, Value = 0},
new Q { Qid = 26, Value = 0},
new Q { Qid = 27, Value = 1},
new Q { Qid = 28, Value = 2},
new Q { Qid = 29, Value = 0},
new Q { Qid = 30, Value = 4},
new Q { Qid = 31, Value = 0},
new Q { Qid = 32, Value = 2},
new Q { Qid = 33, Value = 1},
new Q { Qid = 34, Value = 4},
new Q { Qid = 35, Value = 0},
new Q { Qid = 36, Value = 0},
new Q { Qid = 37, Value = 1},
new Q { Qid = 38, Value = 2},
new Q { Qid = 39, Value = 0},
new Q { Qid = 40, Value = 4},
new Q { Qid = 41, Value = 0},
new Q { Qid = 42, Value = 2},
new Q { Qid = 43, Value = 1},
new Q { Qid = 44, Value = 4},
new Q { Qid = 45, Value = 0},
new Q { Qid = 46, Value = 0},
new Q { Qid = 47, Value = 1},
new Q { Qid = 48, Value = 2}
};

var query = from x in list
join y in list on (x.Qid + 16) equals y.Qid
join z in list on (x.Qid + 32) equals z.Qid
select (x.Value + y.Value + z.Value);

var results = (from x in query
orderby x descending
select x).Take(3);
``````

Is there a way to tell from which logic (A-P) the top 3 came from?
0

Systems and Integration DeveloperCommented:
This should do what you want:
``````var query = (from x in list
join y in list on (x.Qid + 16) equals y.Qid
join z in list on (x.Qid + 32) equals z.Qid
where x.Qid <= 16
select x.Value + y.Value + z.Value)
.Select((item, index) => new { Letter = (char)(65 + index), Value = (int)item });

var results = (from x in query
orderby x.Value descending
select x).Take(3);

foreach(var item in results)
{
Console.WriteLine(item.Letter + "=" + item.Value);
}
``````
0

Author Commented:
Thanks carl_tawn; came across this in results:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType5`2[System.Char,System.Int32]] Select[Int32,<>f__AnonymousType5`2](System.Linq.IQueryable`1[System.Int32], System.Linq.Expressions.Expression`1[System.Func`3[System.Int32,System.Int32,<>f__AnonymousType5`2[System.Char,System.Int32]]])' method, and this method cannot be translated into a store expression.

The example above is using local values, but what I'm doing is querying two tables in a database:
``````var answered = from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new
{
Qid = a.QuestionID,
Value = v.ScaleValue
};

var query = (from x in answered
join y in answered on (x.Qid + 16) equals y.Qid
join z in answered on (x.Qid + 32) equals z.Qid
where x.Qid <= 16
select x.Value + y.Value + z.Value)
.Select((item, index) => new { Letter = (char)(65 + index), Value = item });

var results = (from x in query
orderby x.Value descending
select x).Take(3);
``````

Qid and Value are Int data type.

I tried and cannot get it to work. Thanks again for your help!
0

Author Commented:
Thank you carl_tawn; I saved time and learned something new from you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.