How to filter records from DataTable for a condition ">=" ?

Sri Ram

I have a datatable with columns like First Name, Middle Name, Last Name. I need to filter the records in C# for a condition like shown below.

Select * From Table Where LastName >= 'john'

How to apply this where condition and get the filtered results set that will have records whose last names are like John, Justine, K*****, L***, M***.....Z*** ( last names from J to Z as per alphabetical order)

This datatable is populated by executing a stored procedure and I can't apply the filter at stored proc level as data is encrypted in backend.

I'm able to get the results for equality operator using the below logic.

e.g.

SQL vesion: Select * From Table Where LastName like '%john%'

C# version:

var employees = ( from e in dtEmployee.AsEnumerable()
where e.Field("LastName").Contains("john")
select e ).ToList();

Similarly, need to use the >= operator. How to get records for this filter condition? Can anyone please help on this?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Also, if you are looking to do that in query,
SELECT LastName
FROM Table
WHERE ASCII('j')LEFT(LastName ,1))
Amit Gupta9y ago
You can use IndexOf function to filter your records instead of contains.

IndexOf matches your string and returns the index, which gives more precise result.
Khan Abrar Ahmed9y ago
http://blogs.microsoft.co.il/bursteg/2007/10/16/linq-to-sql-like-operator/
http://codesamplez.com/database/linq-to-sql-like-operator
Ankit Sharma9y ago
Hi SriRam,

I have tested this code and i am getting the correct result.
Please check it once
var employee = (from e in dtEmployee.AsEnumerable()
where String.Compare(e.Field<string("LastName"), "john") >= 0
select e).ToList();
Tapan Patel9y ago
I am not that familiar with query expression so I tried with lambda.

Here is the working example.
public void TestGreaterThanEqualTo(string name = "john")
{
    List<string> names = new List<string> { "jgfdg", "kfsdff", "fsdfsf", "ldsfsf", "ssdfdfd", "tsdfsf" };

    var greaterthanname = names.Where(nm => nm[0] > name[0]);
    Debug.WriteLine(greaterthanname.Count()); //outputs 4 as expected.


}
Sri Ram9y ago
Thank you Ankit for your support.
Unfortunately, the stackoverflow solution doesn't filter records correctly..
Ankit Sharma9y ago
Hi,

Please refer to below links

https://stackoverflow.com/a/19603991

https://stackoverflow.com/questions/3365122/how-to-compare-a-string-column-value-in-a-linq-query

Hope this helps