How to write linq query to get the result table?

Question

Table:

ID      State   Rate
2       NY      8
1       CA      10
1       NY      9
2       IL      7
2       WA      8
1       WA      5

Linq Result Table:

ID      State   ApprovedR   Not-ApprovedR
2       NY          8           9
1       CA          0           10
2       IL          7           0
2       WA          8           5

Rate with record of ID=1 is considered as Not-ApprovedR, If a record doesn't have and ID of 1 then its Not-ApprovedR should be shown as 0

thanks stephen I am new to stack overflow. I didnt know how to put a table in the question — blackbookstar
– blackbookstar, Commented Feb 1, 2017 at 7:13
Cant make sense of this. Your 2nd record has ID = 1 which you say should have its Rate in the Not-ApprovedR column, yet that is not what the 2nd table shows (its in the ApprovedR column — user3559349
– user3559349, Commented Feb 1, 2017 at 7:15
All rows should either have ApprovedR or Not-ApprovedR set to 0 right? *Edit: You kinda need to define your requirements clearly in your post and try showing what have you tried so far. — uTeisT
– uTeisT, Commented Feb 1, 2017 at 7:21
No if a state has two ID's 1 and 2 then those corresponding rate should be shown, if it is 2 it should be shown under approvedR if 1 on not-approvedR. suppose the state has only ID=2, where 2 indicates the rate is an approved rate, so in the resultant table under Not-Approved it should be 0 — blackbookstar
– blackbookstar, Commented Feb 1, 2017 at 7:25
Now you have corrected the last 2 columns, can you explain the first - you have 2 NY records, and are grouping them but the first column shows 2 even though one of those records has ID = 1 - why is the value not 1 for example (surely that column should be omitted) — user3559349
– user3559349, Commented Feb 1, 2017 at 7:26

user3559349 · Accepted Answer · 2017-02-01 07:53:10Z

1

Start by creating a view model to represent you 2nd table structure

public class MyViewModel
{
    public int ID { get; set; }
    public string State { get; set; }
    public int ApprovedR { get; set; }
    public int Not-ApprovedR { get; set; }
}

Then use a .GroupBy() to group by State and project the result into your view model

var data = db.yourTable.GroupBy(x => x.State).Select(x => new MyViewModel()
{
    ID = x.Max(y => y.ID),
    State = x.Key,
    ApprovedR = x.Where(y => y.ID != 1).Sum(y => y.Rate),
    Not-ApprovedR = x.Where(y => y.ID == 1).Sum(y => y.Rate),
});

Side note. Naming a field ID when its not a unique identifier is confusing, and I recommend you change the name of that property.

answered Feb 1, 2017 at 7:53

user3559349

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write linq query to get the result table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related