CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinqasp.net-mvc-4linq-to-sql
blackbookstar
blackbookstar

Reputation: 25

How to write linq query to get the result table?

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

Upvotes: 2

Views: 96

Answers (1)

user3559349
user3559349

Reputation:

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.

Upvotes: 1

Related Questions