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 |