Working with Data isn’t limited to merely filtering out the items you don’t want or merging it with similar data…which is a topic for another day.

You might also have to slice and dice them until they fit the requirement.

Clients come with all sorts of fancy requirements these days, so it’s crucial that you learn how these fancy looking solutions ought to be prepared and delivered.

Don’t worry, you are reading this aren’t you?

Default View Might Sound Boring, But it Ain’t

The DefaultView method is great for slicing, dicing and removing duplicates.

While it doesn’t come with an overload that lets us filter data conditionally, it checks two out of the three boxes we have, so it’s definitely worth investing some time into.

Let’s have a look, now shall we?

Dt_Data.AsEnumerable.NOPE.THAT’S.NOT.HOW.IT.WORKS

Since yours truly is used to converting data into a collection of IEnumerables before zapping it with my flavor of LINQ, I often make the mistake of adding the AsEnumerable method to everything without thinking twice.

DefaultView can be directly called from a DataTable itself.

You don’t have to dress it up into an IEnumerable, which means less work for us!

Dt_Data.DefaultView.ToTable(Feed Me Master)

Now it’s time to feed our little slicer-dicer before presenting it before our client.

Let’s Get Our Hands Dirty

Remember, the method can capture distinct values as well, so let’s see how that’s done.

Dt_Data.DefaultView.ToTable(True)

This returns a set of unique rows.

But what if the client only want “Title” and “Genre”?

We will have to invoke its second overload.

Again, nothing to fear here either, you just have to add a comma after specifying whether you wish to retain the duplicates or not (by providing a Boolean value), and the intellisense is intelligent enough to pick the overload for you.

Dt_Data.DefaultView.ToTable(True, “Title”, “Genre”)

It seems as though the client has changed his mind and now wants “Title” and “Genre” along with its duplicates.

Dt_Data.DefaultView.ToTable(False, “Title”, “Genre”)

The client has changed his mind yet again. What, that surprises you?

Our client wants the first three columns, but he doesn’t know what its names are.

Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Take(3).ToArray)

Using LINQ, we may convert the DataColumns from Dt_Data into an Array of String which can be fed into the DefaultView method.

The first three columns are then Taken, satisfying his bizarre requirement.

But our client doesn’t look too impressed.

He wants us to skip the first three columns this time.

Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Skip(3).ToArray)

Client-san tried to throw us off, but unbeknownst to him, we have LINQ on our side.

Just like Take, you can Skip elements too.

Now he wants it back, but this time he only wants those columns which precede the first column starting with the letter ‘L’.

He didn’t like it when I asked him why he kept changing his mind, so I didn’t question him further. Gosh, these clients can be really unreasonable sometimes…also it looks like we have hit a dead end with this one, I mean how are you going to pull this o-

Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).TakeWhile(Function(tk) Not tk.Trim.ToUpper.StartsWith(“L”)).ToArray)

I bet he wasn’t expecting us to meet his expectations with this one.

If we were to use Where here, it would recruit all the columns which does not start with the “L”.

It’s in situations like these where TakeWhile shines.

Now lets say in addition to the condition stated above we only wanted to limit ourselves to the first three columns.

Dt_Data.AsEnumerable.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).TakeWhile(Function(tk,i) Not (tk.ToString.Trim.StartsWith(“L”) And i<3).ToArray)

I’ve declared two arguments here, and when you do that, the second argument transforms into a counter and auto-increments from zero to number of incoming elements minus one.

It might look scary at first, but the more you get to know it, the easier it becomes to digest.

An Alternative for Selecting Columns:

The DefaultView does not allow you to select columns by their indexes, if that is what you were looking for.

What you can do instead, is Select the columns you want and load them into datarow which is then pushed into a new DataTable.

Dt_Data.AsEnumerable.Select(Function(s) Dt_Result.Clone.LoadDataRow({s(0), s(1),s(3)},False)).CopyToDataTable

Or even

Dt_Data.AsEnumerable.Select(Function(s) Dt_Result.Rows.Add(New Object{s(0), s(1),s(3)}).CopyToDataTable

Select also allows you to reference columns by their names, so you can choose the method which you are comfortable with.

Only problem here, is that you have to initialize the output DataTable and add the required columns before executing the LINQ query given above.

The better you get at LINQing things together, the better you get at satisfying client requirements which translate into more free time for you.

Recap:

Dt.DefaultView.ToTable(False)

Returns all records(including duplicates)

Dt.DefaultView.ToTable(True)

Returns unique records

Dt.DefaultView.ToTable(True, “Column1”, “Column2”)

Returns unique records from the DataTable by comparing records created from Column1 and Column2.

Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Take(n))

Retrieves first n columns

 Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Skip(n))

Skips first n columns

Dt_Data.DefaultView.ToTable(False, Dt_Data.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).TakeWhile(Function(tk, i) i<n And Not tk.Trim.StartsWith(“L”)).ToArray)

Returns the first n columns before the first column starting with “L” and inflicts mental torture.

Dt_Data.AsEnumerable.Select(Function(s) Dt_Result.Clone.LoadDataRow({s(0), s(1),s(3)},False)).CopyToDataTable

Allows us to reference columns by their index.

DefaultView is mostly used to select a handful of columns, or when you want to remove duplicates, and it can come in handy at times.