Posts tagged: linq

Using LINQ to bind flat data to Infragistics UltraWebTree

By , January 10, 2010 11:37 pm

Often you have to operate with flattened data that in reality contains multiple levels of hierarchy. For example it can come as a result of several SQL JOIN statement and look like this:

In this example data consist of static root column, region, site, type and state. And the data has clearly defined hierarchy (e.g. Region “India” has site “Bangalore”, site “Bangalore” has types “Application” and “Area”, type “Application” has states “N/A” and “Testing”).

To load this data into Infragistics UltraWebTree I put together a small procedure: Continue reading 'Using LINQ to bind flat data to Infragistics UltraWebTree'»

LINQ: Truly language integrated. Using VB functions inside of LINQ Query

By , December 17, 2009 5:15 pm

LINQ is truly integrated into VB.NET. This allows not only to use LINQ-specific query language in a standard VB.NET code, but use VB.NET code in a LINQ query. Consider function from the previous post. To make it universal we can pass one more parameter
“Aggregate Type” and based on that parameter return Min, Max, Avg, Sum or Count

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable, i_iAggregateType as Integer) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Choose(i_iAggregateType, Group.Min(Function(row) row(i_sAggregateColumn)), Group.Max(Function(row) row(i_sAggregateColumn)), Group.Sum(Function(row) row(i_sAggregateColumn)), Group.Avg(Function(row) row(i_sAggregateColumn)), Group.Count(Function(row) row(i_sAggregateColumn)))

    return aQuery.toDataTable

End Function

In this example VB.NET function Choose is used inside of a LINQ query’s Select Statement. If i_iAggregateType parameter is equal 1 – Minimum value, will be calculated, 2 – Maximum etc.

Grouping ADO.NET DataTable using LINQ

By , December 17, 2009 10:22 am

I’ve described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above – that entire block of code can be replaced with a single LINQ query:

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Group.Count(Function(row) row(i_sAggregateColumn))

    return aQuery.toDataTable

End Function

The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.

There is one caveat though. Continue reading 'Grouping ADO.NET DataTable using LINQ'»

Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL. Solution to error

By , October 1, 2009 2:15 pm

I was running a basic LINQ 2 SQL statement:

From role In db.user_role _
Where role.USER_ID = Session("user_id") Select role

when I encountered following error message:

Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL.

After a little research I found the solution.
Continue reading 'Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL. Solution to error'»

Panorama Theme by Themocracy

Bitnami