Skip to main content

LINQ Query

Executes a LINQ (Language Integrated Query) expression on a DataTable for advanced data manipulation, transformation, and analysis. This node provides powerful querying capabilities beyond simple filtering.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Input​

  • Table - DataTable to query
  • LINQ Query - C# LINQ query expression to execute (edited in the code editor)

Output​

  • Result - Result of the LINQ query execution (type varies based on query)

LINQ Query Context​

The table is available as dt in your LINQ query. You can access it using:

  • dt.AsEnumerable() - Convert to enumerable for LINQ operations
  • dt.Rows - Access rows directly
  • dt.Columns - Access column information

Example Usage​

Basic Selection and Projection​

// Select specific columns
from user in dt.AsEnumerable()
select new {
    Name = user.Field<string>("name"),
    Email = user.Field<string>("email")
}

Filtering with Where Clause​

// Filter rows where age is greater than 30
from user in dt.AsEnumerable()
where user.Field<int>("age") > 30
select new {
    Name = user.Field<string>("name"),
    Age = user.Field<int>("age")
}

Complex Filtering​

// Multiple conditions
from user in dt.AsEnumerable()
where user.Field<string>("status") == "Active"
   && user.Field<int>("age") >= 18
   && user.Field<int>("age") <= 65
select user

Ordering Results​

// Order by age descending
from user in dt.AsEnumerable()
orderby user.Field<int>("age") descending
select new {
    Name = user.Field<string>("name"),
    Age = user.Field<int>("age")
}

Grouping Data​

// Group by city and count
from user in dt.AsEnumerable()
group user by user.Field<string>("city") into cityGroup
select new {
    City = cityGroup.Key,
    Count = cityGroup.Count()
}

Aggregations​

// Calculate sum of salaries by department
from emp in dt.AsEnumerable()
group emp by emp.Field<string>("department") into deptGroup
select new {
    Department = deptGroup.Key,
    TotalSalary = deptGroup.Sum(e => e.Field<decimal>("salary")),
    AvgSalary = deptGroup.Average(e => e.Field<decimal>("salary")),
    Count = deptGroup.Count()
}

String Operations​

// Filter by string pattern
from user in dt.AsEnumerable()
where user.Field<string>("email").Contains("@gmail.com")
select user
// String transformations
from user in dt.AsEnumerable()
select new {
    Name = user.Field<string>("name").ToUpper(),
    Email = user.Field<string>("email").ToLower(),
    Domain = user.Field<string>("email").Split('@')[1]
}

Calculations and Transformations​

// Calculate derived values
from product in dt.AsEnumerable()
select new {
    ProductName = product.Field<string>("name"),
    Price = product.Field<decimal>("price"),
    Quantity = product.Field<int>("quantity"),
    Total = product.Field<decimal>("price") * product.Field<int>("quantity"),
    Discounted = product.Field<decimal>("price") * 0.9m
}

Top N Records​

// Get top 10 highest salaries
(from emp in dt.AsEnumerable()
 orderby emp.Field<decimal>("salary") descending
 select new {
     Name = emp.Field<string>("name"),
     Salary = emp.Field<decimal>("salary")
 }).Take(10)

Distinct Values​

// Get distinct cities
(from user in dt.AsEnumerable()
 select user.Field<string>("city")).Distinct()

Joining Data (Self-Join)​

// Find users in the same city
from user1 in dt.AsEnumerable()
join user2 in dt.AsEnumerable()
on user1.Field<string>("city") equals user2.Field<string>("city")
where user1.Field<string>("name") != user2.Field<string>("name")
select new {
    User1 = user1.Field<string>("name"),
    User2 = user2.Field<string>("name"),
    City = user1.Field<string>("city")
}

Conditional Logic​

// Categorize by age
from user in dt.AsEnumerable()
select new {
    Name = user.Field<string>("name"),
    Age = user.Field<int>("age"),
    Category = user.Field<int>("age") < 18 ? "Minor" :
               user.Field<int>("age") < 65 ? "Adult" : "Senior"
}

Statistical Analysis​

// Calculate statistics
new {
    TotalRows = dt.AsEnumerable().Count(),
    AvgAge = dt.AsEnumerable().Average(r => r.Field<int>("age")),
    MinAge = dt.AsEnumerable().Min(r => r.Field<int>("age")),
    MaxAge = dt.AsEnumerable().Max(r => r.Field<int>("age")),
    TotalSalary = dt.AsEnumerable().Sum(r => r.Field<decimal>("salary"))
}

Method Syntax​

// Using method syntax instead of query syntax
dt.AsEnumerable()
  .Where(r => r.Field<int>("age") > 30)
  .Select(r => new {
      Name = r.Field<string>("name"),
      Age = r.Field<int>("age")
  })
  .OrderBy(r => r.Age)
  .ToList()

Field Types​

When accessing fields, use the appropriate generic type:

  • Field<string>("columnName") - For text
  • Field<int>("columnName") - For integers
  • Field<decimal>("columnName") - For decimal numbers
  • Field<double>("columnName") - For floating-point numbers
  • Field<bool>("columnName") - For boolean values
  • Field<DateTime>("columnName") - For dates

Tips​

  • The LINQ query is written in C#, not JavaScript
  • Use dt.AsEnumerable() to convert the DataTable to a queryable collection
  • The query editor provides syntax highlighting for C#
  • Results can be various types: collections, single values, anonymous objects
  • Use .ToList() at the end if you want to return a list
  • LINQ queries are evaluated lazily - add .ToList() or .ToArray() to force evaluation
  • Complex queries may impact performance on large datasets
  • Test your queries with sample data first
  • The query must be a valid C# expression

Common Errors​

Empty Table Error​

Error: Table cannot be empty. Please provide a valid DataTable.

Solution: Ensure you're passing a valid DataTable object.

Query Syntax Error​

Error: ErrQuery: [C# compilation error message]

Common causes and solutions:

// Wrong - missing Field type parameter
from user in dt.AsEnumerable()
where user.Field("age") > 30  // Error: missing type parameter
select user

// Correct
from user in dt.AsEnumerable()
where user.Field<int>("age") > 30
select user
// Wrong - incorrect column name (case-sensitive)
user.Field<string>("Name")  // Error if column is "name"

// Correct - match exact column name
user.Field<string>("name")
// Wrong - missing semicolon or incorrect syntax
from user in dt.AsEnumerable()
select user.Field<string>("name")  // Incomplete

// Correct - complete expression
from user in dt.AsEnumerable()
select new { Name = user.Field<string>("name") }

Type Mismatch Error​

// Wrong - trying to get integer as string
var age = user.Field<string>("age");  // Error if age is int

// Correct - match the actual data type
var age = user.Field<int>("age");

Null Value Handling​

// May throw error if column contains nulls
user.Field<int>("age")

// Safe null handling
user.Field<int?>("age") ?? 0  // Returns 0 if null

See Also​