7
Answers

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

Sri Ram

Sri Ram

7y
285
1

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<string>("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?
Answers (7)
1
Tapan Patel

Tapan Patel

NA 8.1k 100.8k 7y
Also, if you are looking to do that in query,
 
  1. SELECT LastName   
  2. FROM Table  
  3. WHERE ASCII('j')<ASCII(LEFT(LastName ,1))  
 
1
Amit Gupta

Amit Gupta

NA 16.5k 25.5k 7y
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. 
0
Khan Abrar Ahmed

Khan Abrar Ahmed

NA 5.8k 199.8k 7y
http://blogs.microsoft.co.il/bursteg/2007/10/16/linq-to-sql-like-operator/
http://codesamplez.com/database/linq-to-sql-like-operator 
0
Ankit Sharma

Ankit Sharma

NA 8.8k 140.8k 7y
Hi SriRam,
 
I have tested this code and i am getting the correct result.
Please check it once
  
  1. var employee = (from e in dtEmployee.AsEnumerable()  
  2.                  where String.Compare(e.Field<string("LastName"), "john") >= 0  
  3.                  select e).ToList();  
0
Tapan Patel

Tapan Patel

NA 8.1k 100.8k 7y
I am not that familiar with query expression so I tried with lambda.
 
Here is the working example.
 
  1. public void TestGreaterThanEqualTo(string name = "john")  
  2. {  
  3.     List<string> names = new List<string> { "jgfdg""kfsdff""fsdfsf""ldsfsf""ssdfdfd""tsdfsf" };  
  4.   
  5.     var greaterthanname = names.Where(nm => nm[0] > name[0]);  
  6.     Debug.WriteLine(greaterthanname.Count());  //outputs 4 as expected.
  7.   
  8.   
  9. }  
 
0
Sri Ram

Sri Ram

NA 74 5.4k 7y

Thank you Ankit for your support.
Unfortunately, the stackoverflow solution doesn't filter records correctly..


0
Ankit Sharma

Ankit Sharma

NA 8.8k 140.8k 7y
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