LINQ allows to perform various queries against different data structures. Wouldn’t it be great if you could easily export result of a LINQ query to CSV? Fortunately you can! This article by Scott Hanselman explain how and culminates in cool in its simplicity code:
namespace FooFoo
{
public static class LinqToCSV
{
public static string ToCsv<T>(this IEnumerable<T> items)
where T : class
{
var csvBuilder = new StringBuilder();
var properties = typeof(T).GetProperties();
foreach (T item in items)
{
string line = string.Join(",",properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
csvBuilder.AppendLine(line);
}
return csvBuilder.ToString();
}
private static string ToCsvValue<T>(this T item)
{
if(item == null) return "\"\"";
if (item is string)
{
return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
}
double dummy;
if (double.TryParse(item.ToString(), out dummy))
{
return string.Format("{0}", item);
}
return string.Format("\"{0}\"", item);
}
}
}
This code adds .ToCsv() extension method to any IEnumerable so you can run something like
Language Integrated Query (LINQ) is a cool feature of .NET languages like C# that allows you to perform SQL-like query right within the language against language’s data structures (lists, arrays etc.) But one drawback of LINQ – you have to know in advance, at compile time which fields to select, what filter conditions would be. Sometimes there’s a need to supply these at runtime – e.g. user selects which fields they want to see
Thankfully there exists Dynamic LINQ Library that allows you to supply LINQ parameters as a string akin Dynamic SQL. Here’s an example of such query from the library’s homepage:
var query = db.Customers
.Where("City == @0 and Orders.Count >= @1", "London", 10)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");
Now, one thing that LINQ can do is query XML. So in theory if we load, say, this XML:
If you’re trying to retrieve distinct rows from your ADO.NET data table using code like
From oRow As DataRow In dtTable.Rows Select oRow Distinct
or
(From oRow As DataRow In dtTable.Rows Select oRow).Distinct()
You may find that rows that are being return are not unique. That’s because by default LINQ compares table rows by reference. If you need to compare by actual values – use DataRowComparer in Distinct method call:
(From oRow As DataRow In dtTable.Rows Select oRow).Distinct(DataRowComparer.Default)
By default if server-side filtering is enabled in UltraWebGrid controls, it displays a small “funnel” icon in the column header, if you click this icon – a list of unique values from this column is displayed and selecting a value filters the grid by displaying only rows that have this value.
This default behavior works, sort of – it has many issues. If your grid has paging enabled – only values from the current page will be displayed. If your columns or values has characters that grid doesn’t like (commas, square brackets etc.) an exception will be thrown (this happens because under the hood grid converts your filters into a string that is passed to DataTable’s RowFilter), there’re other issues as well.
ADO.NET DataTable offers handy RowFilter property (via its DefaultView object), for example to filter by some string value and display filtered data in a grid a code like this is used:
where odtMyData is a DataTable object and odgDataGrid is a Data Grid.
This works well, but what if you need preprocess data in the DataTable prior comparing it to the filter value – what if you need to apply a user-defined function to it? In my case I had a VB function called ProcessTags which stripped HTML tags from a string, so for example strings like this:
<a href="http://someurl">This is a sample text</a>
and this
<span style="color:red">This is a </span><label>sample text</label>
would be converted into the same text
This is a sample text
So I needed to create condition that would return DataTable rows with that have both HTML values by comparing to "This is a sample text".
How? RowFilter‘s syntax is pretty poor and external function cannot be used, so something like
odtMyData.DefaultView.RowFilter = "ProcessTags(Field) = 'This is a sample text'"
odgDataGrid.DataSource = odtMyData
odgDataGrid.DataBind()
Linq2Sql has a great use of stored procedures – it converts them into methods which you can easily call using standardized .NET syntax. For example if you have SP:
ALTER PROCEDURE MyProcedure(MyParam int) ...
after dragging it into Linq2Sql designer you can call it in your .NET code like this:
In the past I described how to perform string aggregates in T-SQL. In this post I will show how strings can be concatenated in LINQ.
I am using ADO.NET data table as a source for the query, but LINQ being LINQ can pull data pretty much from anything so this example can easily be adjusted.
First things first, let’s create the source. As in T-SQL post I am using ol’ faithful Northwind database and getting data from the Employees table:
Dim oConn As New SqlConnection(sMyConnStr) : oConn.Open()
Dim oComm As New SqlCommand("SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName", oConn)
Dim oAd As New SqlDataAdapter(oComm) : Dim oTable As New DataTable : oAd.Fill(oTable)
This will fill the datatable with employees’ first names and countries
Country FirstName
UK Anne
UK Michael
UK Robert
UK Steven
USA Andrew
USA Janet
USA Laura
USA Margaret
USA Nancy
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”).
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.
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.
Comments (0) 
Themocracy
