CODEWITHSUNDEEP
c#asp.netlinqlambda
SeventhWarhawk
SeventhWarhawk

Reputation: 1323

How to sum the values of a specific column attribute of individual rows returned using a LINQ expression? (C#)

I want to single out the rows of a table using a LINQ expression based off a certain passed value and then sum a specific column attribute of each of these rows while keeping the individual sums of the rows separate to one another.

The user enters a specific quantity value using a quantity input - this passed quantity value needs to be used in a LINQ expression to filter out the rows which meet the desired condition.

Below is a variable being assigned to the passed quantity value:

int quantitySelected;
JObject passedQuantity = JObject.Parse(data);
quantitySelected = (int)passedQuantity["qtySelect"]

"quantitySelected" is now equal to the value passed through by the user - and now, using this value in a LINQ expression, i want to be able to filter a specific table (InvoiceLine) based off the condition being met and then include all the filtered rows according to a foreign key identifier (ProductID):

var dynamicReader = DBAccessor.InvoiceLines.Where(zz => zz.Quantity >= quantitySelected).Include(yy => yy.ProductID);

The relevant extract from the database structure is as follows: enter image description here

I want to have all the rows which have a quantity greater or equal to what the user inputted, stored inside the dynamic reader with reference to the ProductID to which they are associated. So for example:

enter image description here

I want to be able to single out the row which has a ProductID of 8 (which can be seen twice here) and then sum their quantities, (so ProductID 8 will ultimately have a sum of 6) and so forth (for each repeating ProductID).

Is there a way to accomplish this using a LINQ query expression? or will i need to use additional C#?

Upvotes: 1

Views: 632

Answers (2)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

I want to be able to single out the row which has a ProductID of 8 (which can be seen twice here) and then sum their quantities

That is nice. Apparently you want the complete row, in the row you want the ProductId and the sum of the quantities. But what do you want to do with the other properties: do you want InvoiceLineId 7 or 10? InvoiceId 3 or 4? Or are you only interested in the sum?

My advice would be to make groups of InvoiceLines that have the same ProductId. For this you can use GroupBy. Use parameter resultSelector to select the properties that you want in your end result:

var result = DBAccessor.InvoiceLines
    .Where(invoiceLine => invoiceLine.Quantity >= quantitySelected)

    // group the remaining invoicelines into groups with the same productId
    .GroupBy(invoiceLine => invoiceLine.ProductId,

    // parameter result selector: use the common productId and all invoiceLines
    // that have this productId to make one new:
    (productId, invoiceLinesWithThisProductId) => new
    {
        ProductId = productId,
        Quantity = invoiceLinesWithThisProductId
                   .Select(invoiceLine => invoiceLine.Quantity)
                   .Sum(),

        ... // other properties, you didn't specify what you want
    });

Upvotes: 1

Jamshaid K.
Jamshaid K.

Reputation: 4547

After reading the description, you seem to be willing to merge duplicates rows into one from the list and sum up the quantity of the duplicate rows. You can use GroupBy statement to get the desired results.

Here is an example, you can further fix it accordingly to fit your needs.

var dynamiceReader = DBAccessor.InvoiceLines.Where(x=> x.Quantity >= quantitySelected).GroupBy(e => e.ProductID).Select(product =>
{
    var p = product.First();
    return new
    {
        InvoiceLineID = p.InvoiceLineID,
        InvoiceID = p.InvoiceID,
        ProductID = p.ProductID,
        Quantity = product.Sum(sum => sum.Quantity)
    };
});

Similarly, if you want to include the merged Ids as well, then you can modify it like below:

InvoiceLineID = string.Join(", ", product.Select(x => x.InvoiceLineID)),

Upvotes: 0

Related Questions