CODEWITHSUNDEEP
c#linq
novafluff
novafluff

Reputation: 911

LINQ groupby when two attributes are the same

So i have two objects that i join together but on some occasions lines from object x will have two properties that will be the duplicates on another object so for simplicity we can call them PropertyOne and PropertyTwo and when those two are the same i want to group them together into one line and take the sum of the amount field the two objects have

i have this linq where without the groupby part it works to get all lines but it then have the objects where the properties are the same as their own "lines" and not the same. How would i change this to make it group them properly

 transactions = (from x in data.Transactions
                            join y in data.OtherTransactions
                            on x.TransId equals y.TransId
                            group x by x.PropertyOne, x.PropertyTwo
                       select new OtherFinancialTransactionsItems
                       {
                           Description = x.Description,
                           LineAmount = y.Amount
                       }).ToList();

Sample data

data.Transactions = [
{Transaction: TransId: 1, Description: "Foo", PropertyOne: "123", PropertyTwo: "100"},
{Transaction: TransId: 2, Description: "Blah", PropertyOne: "456", PropertyTwo: "200"},
{Transaction: TransId: 3, Description: "Foo", PropertyOne: "123", PropertyTwo: "100"}
]

data.OtherTransactions = [
{OtherTransactions: TransId: 1, Amount: 5000},
{OtherTransactions: TransId: 2, Amount: 7500},
{OtherTransactions: TransId: 3, Amount: 5000}
]

And the expected outcome would be two lines

OtherFinancialTransactionsItems [
{OtherFinancialTransactionsItem: Description: "Foo", Amount: 10000},
{OtherFinancialTransactionsItem: Description: "Blah", Amount: 7500},
]

Upvotes: 0

Views: 157

Answers (2)

mm8
mm8

Reputation: 169200

This should work if I understand your requirements correctly:

transactions = (from x in data.Transactions
                join y in data.OtherTransactions
                on x.TransId equals y.TransId
                let temp = new
                {
                    x.Description,
                    y.Amount,
                    x.PropertyOne,
                    x.PropertyTwo
                }
                group temp by new { x.PropertyOne, x.PropertyTwo } into g
                select new OtherFinancialTransactionsItems
                {
                    Description = g.First().Description,
                    LineAmount = g.Sum(x => x.Amount)
                }).ToList();

Upvotes: 1

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

You have to group only records which have these properties equal. And concatenate with records with not equal.

var joined =
    from x in data.Transactions
    join y in data.OtherTransactions on x.TransId equals y.TransId
    select new { x, y };

var withEqual = 
    from q in joined
    where q.x.PropertyOne == q.x.PropertyTwo 
    group q by new { q.x.PropertyOne, x.Description } into g
    select new 
    {
        g.Key.Description,
        LineAmount = g.Sum(m => m.y.Amount)
    }

var notEqual = 
    from q in joined
    where q.x.PropertyOne != q.x.PropertyTwo 
    select new 
    {
        q.x.Description,
        LineAmount = q.y.Amount
    }   

var transactions = withEqual
    .Concat(notEqual)
    .Select(x => new OtherFinancialTransactionsItems
    {
        Description = x.Description,
        LineAmount = y.Amount
    })
    .ToList();

This query is close to your requirement, but I think you have missed something.

Upvotes: 0

Related Questions