CODEWITHSUNDEEP
c#sql
SlobodanT
SlobodanT

Reputation: 431

What would be C# equivalent for SQL ">" operator in where clause for char column

working on an c# application i came across something that I don't quite understand.

I have SQL column KONTO char(15) and it contains numeric data. When I run the following query...

select * from MyDatabase..GkKon

I get this result...

enter image description here

and thats just fine.

My C# code providing the same result is:

using (MyDatabaseEntities db = new MyDatabaseEntities())
{
    var data = db.GkKons.ToList();
    dataGridView1.DataSource = data;
}

I came across problem when I tried to select where greater than "01"

select * from MyDatabase..GkKon 
where KONTO>'01'

SQL did the job and I got

I tried doing something similar in C# but it gives me syntax error.

using (MyDatabaseEntities db = new MyDatabaseEntities())
{
    var data = db.GkKons.Where(x=>x.KONTO>"01").ToList();
    dataGridView1.DataSource = data;
}

Because LINQ can't use ">" on two strings. I tried using SqlCommand and Parameters to get the data and it works, but I am using EF and it seems its causing me problem.

Is there a way to do this using LINQ and EF? How does SQL do this "magic"?

Upvotes: 0

Views: 232

Answers (3)

Adrian Godoy
Adrian Godoy

Reputation: 165

Sql is comparing the byte value the coallition of the string. In c# you compare similar to that using the method string.Compare(s1,s2) it returns 

s1==s2 returns 0  
s1>s2 returns 1  
s1<s2 returns -1

something like this:

 var data = db.GkKons.Where(x=>string.Compare(x.KONTO,"01")==1).ToList();

or if you need the numeric order of the strings you can cast it to an int :

 var data = db.GkKons.Where(x=>int.Parse(x.KONTO)>1).ToList();

keep in mind that :

> string.Compare("0010", "01"); evaluates to
-1
but 
> string.Compare("10", "01"); evaluates to
1

Edit: changed byte value to coallition as @Gordon Linoff sugested

Upvotes: 4

MatteoCracco97
MatteoCracco97

Reputation: 416

Use linq: Let's assume that strings is a list of strings ("0","1","2"....) MajorString is a list of string that are > than the input inserted the compare method. InpuString is the string input , so every string that is greater than the input string is returned in the majorstring list. Moreover the compare method return 1 if the first string is greather than the second string. The output in this case will be : {"2","3"}

  List<string> strings = new List<string>();
  strings.Add("0");
  strings.Add("1");
  strings.Add("2");
  strings.Add("3");
  string InputString = "1"
  List<string> MajorString = strings.Where(x => string.Compare(x, InputString ) == 1).ToList();

Upvotes: 0

Fernando Milanez
Fernando Milanez

Reputation: 75

Try this:

var data = db.GkKons.Where(x => (x.KONTO > 1)).ToList();

Upvotes: 0

Related Questions