Filter by linq

[fairymemay (noona)]

I want to filter datatable by linq
condition count character column Create ID <6 as below.

dt_input.AsEnumerable().Where(Function (row) row((“Create ID”)<“7”).Length.ToString).CopyToDataTable

error as below.

Please guide me for solve it.
Thank you

[Parvathy (PS Parvathy)]

Hi @fairymemay

Try this:

filteredRows = dt_input.AsEnumerable().Where(Function(row) row.Field(Of String)("Create ID").Length < 6).CopyToDataTable()

filteredRows is of DataType System.Data.DataTable.

Hope it helps!!

[mkankatala (Mahesh Kankatala)]

Hi @fairymemay

Check the below linq expression,

dt_input.AsEnumerable().Where(Function (row) row("Create ID").toString.Length < 6).CopyToDataTable

Hope it helps!!

[lrtetala (Lakshman Reddy)]

Hi @fairymemay

dt_Input.AsEnumerable().Where(Function(row) row("Create ID").ToString.Length <= 7).CopyToDataTable

Cheers!!

[fairymemay (noona)]

@lrtetala @mkankatala @parvathy

after run error as below.
Assign: The source contains no DataRows.

Sometime data after filter don’t have data.
How to manage it.

[Parvathy (PS Parvathy)]

Hi @fairymemay

filteredRows = dt_input.AsEnumerable().Where(Function(row) row.Field(Of String)("Create ID").Length < 6)

filteredRows is of DataType IEnumerable(System.Data.DataRow)

=> Use an if condition and give like below

If 
   filteredRows.Any()
Then
     dt_Output = filteredRows.CopyToDataTable()
Else
    dt_Output = New System.Data.DataTable()
End If

dt_Output is of DataTpye System.Data.DataTable.

Hope it helps!!

[mkankatala (Mahesh Kankatala)]

Okay @fairymemay

In that case use If condition then give the below expression,

dt_input.AsEnumerable().Where(Function (row) row("Create ID").toString.Length < 6).CopyToDataTable.rows.Count>0

→ In then block take assign activity to store in Output Datatable.

- Assign -> OutputDatatable = dt_input.AsEnumerable().Where(Function (row) row("Create ID").toString.Length < 6).CopyToDataTable

Hope it helps!!

[ppr (Peter Preuss)]

@fairymemay

Assign: The source contains no DataRows.

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.AsE…

[Jayesh_678 (Jayesh)]

Hi,
Use below query

Dim filteredRows As DataTable = dt_input.AsEnumerable() _
.Where(Function(row) row.Field(Of String)(“Create ID”).Length < 6) _
.Select(Function(row) row.Table.Clone()) _
.DefaultIfEmpty(dt_input.Clone()) _
.FirstOrDefault()

[fairymemay (noona)]

@mkankatala same error as below.

[mkankatala (Mahesh Kankatala)]

First check in immediate panel that dt_Input have any datarows there or not… @fairymemay

[fairymemay (noona)]

@mkankatala yes, dt_input have data as below.

[mkankatala (Mahesh Kankatala)]

It was showing only Column not showing datarows, Could you check rows count of dt_Input in Immediate panel once.

dt_Input.Rows.Count

Error also showing there is no datarows in the Source, here source is dt_Input.

Hope you understand!!

[fairymemay (noona)]

@mkankatala dt_input.Rows.count = 2

[mkankatala (Mahesh Kankatala)]

Okay @fairymemay

Then do one thing, Before If activity, Take an assign activity and create a boolean datatype variable called Bool_Flag.

- Assign -> Bool_Flag = If(dt_input.AsEnumerable().Where(Function (row) row("Create ID").toString.Length < 6).CopyToDataTable.rows.Count > 0, True, False)

→ After Assign activity, insert If condition and give the below condition,

If Condition -
Bool_Flag = True

Then block
- Assign -> OutputDatatable = dt_input.AsEnumerable().Where(Function (row) row("Create ID").toString.Length < 6).CopyToDataTable

Else block
There is no rows in the OutputDatatable

Check the below workflow for better understanding,

Hope it helps!!

[fairymemay (noona)]

@mkankatala same errror.

Can you share your flow?

[mkankatala (Mahesh Kankatala)]

Could you share your Input excel file if it doesn’t have confidential information… @fairymemay

[fairymemay (noona)]

@mkankatala file input as attached.
input.xlsx (8.5 KB)

[mkankatala (Mahesh Kankatala)]

Okay @fairymemay I got it,

In the assign activity that we have used after Read range workbook activity change the expression as below,

- Assign -> Bool_Flag = If(dt_input.AsEnumerable().Any(Function (row) row("Create ID").toString.Length < 6), True, False)

It’s working fine now.

Hope it helps!!