Add an additional value from LINQ

how to add new column in linq query
dynamic linq select
add new column in existing list c#

I have the following bit of code:

var checkRegisters = await Database.Client.Context.CheckRegister
            .Where(cr => cr.CheckingAccount == account 
                   && cr.CheckDate >= fromDate 
                   && cr.CheckDate <= toDate 
                   && cr.Void == "N" )
            .GroupBy(cr => cr.CheckNumber, 
                     cr => cr.Amount, (key, g) => 
                                          new { 
                                                CheckNumber = key, 
                                                Amount = g.Sum()
                                              })
            .OrderBy(c => c.CheckNumber)
            .ToListAsync();

I don't fully understand what it is doing besides returning me a list of CheckNumber and Amount. The CheckRegister table contains the column Payee and so does the corresponding modelBuilder from the EF:

entity.Property(e => e.Payee).HasColumnType("varchar(255)");

How can I add Payee to the list I'm returned?

What the code does:

Your database has a table that contains CheckRegisters. Every CheckRegister has at least properties CheckingAccount, CheckDate, Void, CheckNumber and Amount, and according to you it also has a property Payee:

So your code described step by step.

// Take the complete table of CheckRegisters:
Database.Client.Context.CheckRegister

// from every checkRegister in this table (every row),
// keep only those checkRegisters that have a CheckingAccount value equal to account
// and a CheckDate value betwen fromData and toDate
// and a value for property Void equalt to "N"

.Where(checkRegister => checkRegister.CheckingAccount == account 
                     && checkRegister.CheckDate >= fromDate 
                     && checkRegister.CheckDate <= toDate 
                     && checkRegister.Void == "N" )

// Group all remaining CheckRegisters into groups with same checkNumber
// Each group will have a property Key which has the value of the common CheckNumber
        .GroupBy(cr => cr.CheckNumber,

           // ElementSelector: from every CheckRegister put only the amount in each grouop 
           checkRegister => checkRegister.Amount,

           // ResultSelector: take every key (which we call checkNumber)
           // and all Amounts of all CheckRegisters with this CheckNumber
           // to make one new object:
           (checkNumber, amountsFromCheckRegistersWithThisCheckNumber) => new
           { 
                CheckNumber = key, 

                // To calculate property Amount: sum all amountsFromCheckRegistersWithThisCheckNumber
                Amount = amountsFromCheckRegistersWithThisCheckNumber.Sum(),
           })

// By now you have a sequence of objects, each with two properties:
// CheckNumber: a checkNumber used in the CheckRegisters that were left after the Where
// Amount: the sum of all Amounts of all CheckRegisters that have this CheckNumber

// Finally you do some Ordering, and convert the resulting elements into a list
.OrderBy(groupedItem => groupedItem.CheckNumber)
.ToListAsync();

Now your desired change: Add the Payees.

The problem is, that the returned data are CheckRegisters grouped by CheckNumber. Every Payee is per CheckRegister. If you make groups of CheckRegisters with the same CheckNumber you get several Payees with this CheckNumber:

CheckNumber | Payee | ...
    1       |   A
    2       |   B
    1       |   C
    2       |   C    // note: C  is used again
    1       |   D

If I make groups with same CheckNumber, you'll get:

group CheckNumber 1 [1, A], [1,C], [1,D]
group CheckNumber 2 [2, B], [2,C]

So every group may have more than one Employee. If that's ok with you, just omit the ElementSelector and Select the Employees in the ResultSelector

var result = Database.Client.Context.CheckRegister
.Where(...)
.GroupBy(cr => cr.CheckNumber,

// ResultSelector: take every key (which we call checkNumber)
// and all CheckRegisters with this CheckNumber
// to make one new object:
(checkNumber, checkRegistersWithThisCheckNumber) => new
{ 
    CheckNumber = checkNumber, 

    // for Employees: Select property Employee, and convert them to a List
    Employees = checkRegistersWithThisCheckNumber
                .Select(checkRegister => checkRegister.Employee)
                .ToList(),

    // Amounts: select the Amounts and Sum
    Amount = checkRegistersWithThisCheckNumber
                 .Select(checkRegister => checkRegister.Amount)
                 .Sum(),
    });

Add an additional value from LINQ, What the code does: Your database has a table that contains CheckRegisters . Every CheckRegister has at least properties CheckingAccount� I am adding to array using the foreach loop. I want to do the same via linq. My code is as below: foreach (string SourceExcelFilePath in SourceExcelTemplateCollection) { // Fetch the Template Id from Destination Excel Template(In DATA Folder)if any to validate if Template Id already exists string SourceExcelTemplateName = Path.GetFileNameWithoutExtension(SourceExcelFilePath); string

I would add it to the object you're creating in GroupBy. So change line 3 like below

.GroupBy(cr => cr.CheckNumber, cr => cr.Amount, cr => cr.Payee, (key, g) => 
                              new { CheckNumber = key, Amount = g.Sum(), Payee = g.Payee})

Note that group by was only separating by Number and Amount, so if you also group by Payee you should expect the resulting list to be longer. This is also assuming that Payee is the name of the column in your table.

How to add values to array using Linq?, I am adding to array using the foreach loop. I want to do the same via linq. My code is as below: Hide Copy Code. foreach� How can add an additional row to a LINQ query? Ask Question (All)" item would have a special value for its ID so you know when it has been selected. Let's assume

Add the new column to the group by:

var checkRegisters = await Database.Client.Context.CheckRegister
            .Where(cr => cr.CheckingAccount == account && cr.CheckDate >= fromDate && cr.CheckDate <= toDate && cr.Void == "N" )
            .GroupBy(cr => cr.CheckNumber, cr => cr.Amount, e => e.Payee , (key, g, p) => new { CheckNumber = key, Amount = g.Sum(), Payee = p})
            .OrderBy(c => c.CheckNumber).ToListAsync();

How to add extra results on LINQ result, Hi, Try this,. Hide Expand Copy Code. List<employeeinfo> EList = new List< employeeinfo>(); EList.Add(new EmployeeInfo(1, "a1", "b1")); EList. The LINQ family of technologies provides a consistent query experience for objects (LINQ to Objects), relational databases (LINQ to SQL), and XML (LINQ to XML). For a developer who writes queries, the most visible "language-integrated" part of LINQ is the query expression. Query expressions are written in a declarative query syntax. By using

Expression.Add Method (System.Linq.Expressions), Creates a that represents an arithmetic addition operation that does not have Linq.Expressions; // This expression adds the values of its two arguments. I have a LINQ query (using with EF) Basically I want to add a column in Select results based on value of another column. I have PaymentDate column in DB table but not Paid column. If there is null in PaymentDate column it also shows payment is false and if it has some date in it means paid is true. Here is my query, please guide me how to do that.

How to add custom methods for LINQ queries (C#), For more information, see Extension Methods. Adding an Aggregate Method. An aggregate method computes a single value from a set of values. In this quick post, we needed to reuse a calculated value inside of a LINQ query. We didn’t want to call the calculation multiple times. We also didn’t want to iterate the collection twice. We found LINQ sub-expressions as a solution that allows us to stash calculated values to use in other places in our LINQ query. This allows us to call

Calculating Values using Calculated Values in LINQ � Nick , Need to perform a LINQ query with a calculated value that relies on the result of another calculated value? We'll add 2 more methods to calculate margin: To add additional filters expand the Where clause. At this point it is standard LINQ filtering. The point is that you don't need pivot here because you are using the ability of the data table to query columns by a name provided programmatically.

Comments
  • Much closer and I think I am beginning to see the light. Only error is "key" after CheckNumber = key The name key does not exist in the current context. Thanks for the well commented example. Any idea about key error?
  • oops, type after I changed the identifiers to more describing names. Corrected it
  • This and @Oscar M's solution gives same error: The type arguments for method 'Queryable.GroupBy.....cannot be inferred from the usage. Try specifying the type arguments explicitly.
  • Is cr an object, possibly of type CheckRegister? Can you add Payee to that object's fields as a string then try the code again?
  • I thought I already had that in the last bit of code in my original post with: entity.Property(e => e.Payee).HasColumnType("varchar(255)"); that is what Entity Framework has built out from my DB tables....at least that is how I understand it to work
  • I should add that this is Entity Framework Core. Still not working after trying many variation on the Group By
  • This and @cyclopeanCity's solution gives same error: The type arguments for method 'Queryable.GroupBy.....cannot be inferred from the usage. Try specifying the type arguments explicitly.