Sample Input:
TransactionID | CustomerName | AccountNumber | TransactionType | Amount | TransactionDate |
---|---|---|---|---|---|
T1001 | Raj Kumar | 1234567890 | Credit | 15000 | 45787 |
T1002 | Sai R | 1234567890 | Debit | 5000 | 45788 |
T1003 | Nikhil S | 9876543210 | Credit | 20000 | 45789 |
T1004 | Raj Kumar | 1234567890 | Debit | 3000 | 45790 |
T1005 | Sai R | 1234567890 | Credit | 10000 | 45791 |
T1006 | Nikhil S | 9876543210 | Debit | 7000 | 45792 |
T1007 | Raj Kumar | 1234567890 | Credit | 5000 | 45793 |
70 Useful LINQ Examples for RPA Developers:
Use Case | LINQ Expression | Description |
---|---|---|
Filter Credit Transactions | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Credit”).CopyToDataTable() | Only Credit transactions |
Filter Debit Transactions | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Debit”).CopyToDataTable() | Only Debit transactions |
Filter Amount > 10000 | dt.AsEnumerable().Where(Function(row) Convert.ToDouble(row(“Amount”)) > 10000).CopyToDataTable() | Amount greater than 10000 |
Get All Unique Customers | dt.AsEnumerable().Select(Function(row) row(“CustomerName”)).Distinct().ToList() | Distinct Customer Names |
Sort by Date Ascending | dt.AsEnumerable().OrderBy(Function(row) row(“TransactionDate”)).CopyToDataTable() | Sort by TransactionDate ASC |
Sort by Amount Descending | dt.AsEnumerable().OrderByDescending(Function(row) Convert.ToDouble(row(“Amount”))).CopyToDataTable() | Sort by Amount DESC |
Get Top 2 Transactions by Amount | dt.AsEnumerable().OrderByDescending(Function(row) Convert.ToDouble(row(“Amount”))).Take(2).CopyToDataTable() | Top 2 Amounts |
Sum of All Amounts | dt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row(“Amount”))) | Sum of Amounts |
Max Transaction Amount | dt.AsEnumerable().Max(Function(row) Convert.ToDouble(row(“Amount”))) | Maximum Transaction Amount |
Min Transaction Amount | dt.AsEnumerable().Min(Function(row) Convert.ToDouble(row(“Amount”))) | Minimum Transaction Amount |
Average Transaction Amount | dt.AsEnumerable().Average(Function(row) Convert.ToDouble(row(“Amount”))) | Average Amount |
Count of Transactions | dt.AsEnumerable().Count() | Total number of transactions |
Group by Customer and Sum | dt.AsEnumerable().GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Customer=g.Key, .Total=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Group by Customer with total amount |
Check if Any > 20000 | dt.AsEnumerable().Any(Function(row) Convert.ToDouble(row(“Amount”)) > 20000) | Check if any transaction exceeds 20000 |
First Credit Transaction | dt.AsEnumerable().First(Function(row) row(“TransactionType”).ToString() = “Credit”) | First Credit Transaction |
Last Debit Transaction | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Debit”).Last() | Last Debit Transaction |
Customer Transaction Count | dt.AsEnumerable().GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Customer=g.Key, .Count=g.Count()}).ToList() | Group by Customer with count |
Get Transactions of Raj Kumar | dt.AsEnumerable().Where(Function(row) row(“CustomerName”).ToString() = “Raj Kumar”).CopyToDataTable() | Only Raj Kumar transactions |
Filter by Date Range | dt.AsEnumerable().Where(Function(row) CDate(row(“TransactionDate”)) >= #2025-05-12# And CDate(row(“TransactionDate”)) <= #2025-05-14#).CopyToDataTable() | Transactions from 12-14 May |
Get Account Numbers | dt.AsEnumerable().Select(Function(row) row(“AccountNumber”)).Distinct().ToList() | Unique Account Numbers |
Use Case | LINQ Expression | Description |
---|---|---|
Select Anonymous Type | dt.AsEnumerable().Select(Function(row) New With {.Name=row(“CustomerName”), .Amt=row(“Amount”)}).ToList() | Custom projection with name and amount |
Skip First 2 Rows | dt.AsEnumerable().Skip(2).CopyToDataTable() | Skip top 2 transactions |
Take First 3 Rows | dt.AsEnumerable().Take(3).CopyToDataTable() | Take first 3 transactions |
List of Dates | dt.AsEnumerable().Select(Function(row) CDate(row(“TransactionDate”))).ToList() | All transaction dates |
Credit Total per Account | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Credit”).GroupBy(Function(row) row(“AccountNumber”)).Select(Function(g) New With {.Acc=g.Key, .Total=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Total Credit per account |
Max Amount by Customer | dt.AsEnumerable().GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Cust=g.Key, .MaxAmt=g.Max(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Max Amount by Customer |
Min Amount by Customer | dt.AsEnumerable().GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Cust=g.Key, .MinAmt=g.Min(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Min Amount by Customer |
Total Debit by Customer | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Debit”).GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Cust=g.Key, .Sum=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Debit sum by Customer |
All Dates in Descending Order | dt.AsEnumerable().OrderByDescending(Function(row) row(“TransactionDate”)).Select(Function(row) row(“TransactionDate”)).ToList() | All dates DESC |
Filter Amount Between 4000 and 15000 | dt.AsEnumerable().Where(Function(row) Convert.ToDouble(row(“Amount”)) >= 4000 And Convert.ToDouble(row(“Amount”)) <= 15000).CopyToDataTable() | Filter range 4k-15k |
Check All Are Credit | dt.AsEnumerable().All(Function(row) row(“TransactionType”).ToString() = “Credit”) | Check if all transactions are Credit |
Check All Amounts > 1000 | dt.AsEnumerable().All(Function(row) Convert.ToDouble(row(“Amount”)) > 1000) | Check if all transactions > 1000 |
First Transaction | dt.AsEnumerable().First() | Get first transaction row |
Last Transaction | dt.AsEnumerable().Last() | Get last transaction row |
FirstOrDefault of Sai R | dt.AsEnumerable().FirstOrDefault(Function(row) row(“CustomerName”).ToString() = “Sai R”) | FirstOrDefault match of Sai R |
Transaction Exists for ID T1003 | dt.AsEnumerable().Any(Function(row) row(“TransactionID”).ToString() = “T1003”) | Check if T1003 exists |
Filter by Customer and Type | dt.AsEnumerable().Where(Function(row) row(“CustomerName”).ToString() = “Sai R” And row(“TransactionType”).ToString() = “Credit”).CopyToDataTable() | Sai R credit transactions |
Sum Amount by Account | dt.AsEnumerable().GroupBy(Function(row) row(“AccountNumber”)).Select(Function(g) New With {.Account=g.Key, .Total=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Sum by Account |
Count by Transaction Type | dt.AsEnumerable().GroupBy(Function(row) row(“TransactionType”)).Select(Function(g) New With {.Type=g.Key, .Count=g.Count()}).ToList() | Group by Credit/Debit |
Sum by Transaction Type | dt.AsEnumerable().GroupBy(Function(row) row(“TransactionType”)).Select(Function(g) New With {.Type=g.Key, .Sum=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Total Amount per TransactionType |
Take Middle 3 Transactions | dt.AsEnumerable().Skip(2).Take(3).CopyToDataTable() | Middle 3 rows |
Transactions for May 13 | dt.AsEnumerable().Where(Function(row) CDate(row(“TransactionDate”)) = #2025-05-13#).CopyToDataTable() | Transactions on 13 May |
Convert to List of Anonymous | dt.AsEnumerable().Select(Function(row) New With {.ID=row(“TransactionID”), .Date=row(“TransactionDate”)}).ToList() | ID and Date projection |
Count of Unique AccountNumbers | dt.AsEnumerable().Select(Function(row) row(“AccountNumber”)).Distinct().Count() | Count of unique account numbers |
Sum of Transactions by Day | dt.AsEnumerable().GroupBy(Function(row) row(“TransactionDate”)).Select(Function(g) New With {.Date=g.Key, .Sum=g.Sum(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Sum by Day |
Use Case | LINQ Expression | Description |
---|---|---|
Transactions Except Top 1 | dt.AsEnumerable().Skip(1).CopyToDataTable() | Exclude first row |
List All Transaction IDs | dt.AsEnumerable().Select(Function(row) row(“TransactionID”).ToString()).ToList() | All TransactionIDs |
Get All Credit Amounts | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Credit”).Select(Function(row) Convert.ToDouble(row(“Amount”))).ToList() | List of Credit Amounts |
Get All Debit Amounts | dt.AsEnumerable().Where(Function(row) row(“TransactionType”).ToString() = “Debit”).Select(Function(row) Convert.ToDouble(row(“Amount”))).ToList() | List of Debit Amounts |
Minimum Date | dt.AsEnumerable().Min(Function(row) CDate(row(“TransactionDate”))) | Earliest transaction date |
Maximum Date | dt.AsEnumerable().Max(Function(row) CDate(row(“TransactionDate”))) | Latest transaction date |
Get Transactions Not by Raj Kumar | dt.AsEnumerable().Where(Function(row) row(“CustomerName”).ToString() <> “Raj Kumar”).CopyToDataTable() | Exclude Raj Kumar transactions |
Find Transaction by Amount = 10000 | dt.AsEnumerable().Where(Function(row) Convert.ToDouble(row(“Amount”)) = 10000).CopyToDataTable() | Transactions with exact amount 10K |
Take Even Indexed Rows | dt.AsEnumerable().Where(Function(row, idx) idx Mod 2 = 0).CopyToDataTable() | Only even index rows |
Take Odd Indexed Rows | dt.AsEnumerable().Where(Function(row, idx) idx Mod 2 <> 0).CopyToDataTable() | Only odd index rows |
Get Unique Transaction Types | dt.AsEnumerable().Select(Function(row) row(“TransactionType”).ToString()).Distinct().ToList() | Unique types |
Filter Amount < 6000 | dt.AsEnumerable().Where(Function(row) Convert.ToDouble(row(“Amount”)) < 6000).CopyToDataTable() | Amount below 6000 |
Transactions Sorted by CustomerName | dt.AsEnumerable().OrderBy(Function(row) row(“CustomerName”).ToString()).CopyToDataTable() | Sort by name |
Check for Empty DataTable | Not dt.AsEnumerable().Any() | Returns True if DataTable is empty |
Convert Amount to String List | dt.AsEnumerable().Select(Function(row) row(“Amount”).ToString()).ToList() | All Amounts as strings |
Group by Account and Count | dt.AsEnumerable().GroupBy(Function(row) row(“AccountNumber”)).Select(Function(g) New With {.Account=g.Key, .Count=g.Count()}).ToList() | Account with number of transactions |
Row Index > 3 | dt.AsEnumerable().Where(Function(row, idx) idx > 3).CopyToDataTable() | Skip first 4 rows |
Row Index < 3 | dt.AsEnumerable().Where(Function(row, idx) idx < 3).CopyToDataTable() | Only first 3 rows |
Select Customer and Date | dt.AsEnumerable().Select(Function(row) New With {.Customer=row(“CustomerName”), .Date=row(“TransactionDate”)}).ToList() | Get customer and date info |
Amount as Int List | dt.AsEnumerable().Select(Function(row) Convert.ToInt32(row(“Amount”))).ToList() | Amount column as Integers |
Top 1 by Date | dt.AsEnumerable().OrderByDescending(Function(row) row(“TransactionDate”)).Take(1).CopyToDataTable() | Most recent transaction |
Bottom 1 by Date | dt.AsEnumerable().OrderBy(Function(row) row(“TransactionDate”)).Take(1).CopyToDataTable() | Oldest transaction |
Transactions with Amount Not Equal to 5000 | dt.AsEnumerable().Where(Function(row) Convert.ToDouble(row(“Amount”)) <> 5000).CopyToDataTable() | Amount != 5000 |
Group and Average by Customer | dt.AsEnumerable().GroupBy(Function(row) row(“CustomerName”)).Select(Function(g) New With {.Customer=g.Key, .Avg=g.Average(Function(r) Convert.ToDouble(r(“Amount”)))}).ToList() | Average Amount per Customer |