SQL Like functionality in Linq for int datatype

Question

0.00/5 (No votes)

See more:

Hi,
I need to use the SQL Like functionality in Linq for int field. Here i used SqlMethod.Like() for string datatype but i cant use this for int datatype even-though i used the string conversion inside the SqlMethod.Like(). Here is sample code which i tried .

Sample:

string check = "2*";

SQL

var people = (from t in sample.PROCESSes
                          where SqlMethods.Like(SqlFunctions.StringConvert((double)t.ProcessID), (check.Replace("*", "%"))) == true
                          select t).ToList();

Thanks in Advance

Posted 21-Aug-13 19:13pm

RajeshKumar Sugumar

Updated 26-Aug-13 17:59pm

v2

Add a Solution

3 solutions

Solution 1

You can use Lambda Expressions for that as mentioned below :

C#

var people = sample.PROCESSes.Where(t => t.ProcessID.ToString().StartsWith("2")).ToList();

Posted 21-Aug-13 20:05pm

CodeBlack

Comments

RajeshKumar Sugumar 22-Aug-13 4:42am

string check = "2*" or "*2" or "2*2"
like this i want to filter the ProcessID. So i Cant use Lamda Expressions

CodeBlack 22-Aug-13 4:49am

can you post your sql query sample ?

RajeshKumar Sugumar 22-Aug-13 5:04am

This is Query i am using in SqlServer.

Select * from PROCESS Where CAST(ProcessID as varchar) Like '2*'

The Linq Query which i tried is

var people = (from t in sample.PROCESSes
where SqlMethods.Like(SqlFunctions.StringConvert((double)t.ProcessID), (check.Replace("*", "%"))) == true
select t).ToList();

AND

var query1 = from c in sample.PROCESSes
where SqlFunctions.CharIndex(SqlFunctions.StringConvert((double)c.ProcessID), check) > 0
select c;
these are ways but i not getting the output ......

CodeBlack 22-Aug-13 5:11am

Select * from PROCESS Where CAST(ProcessID as varchar) Like '2*'

is it '2*' or '2%'

RajeshKumar Sugumar 22-Aug-13 5:14am

its "2%" only

CodeBlack 22-Aug-13 5:28am

ok. So that mean your query will return only those rows for which ProcessID starts with 2.

So the solution which i have provided will also return only those rows for which ProcessID starts with 2.

1. If you want to make query like : Select * from PROCESS Where CAST(ProcessID as varchar) Like '%2'

then use :
var people = sample.PROCESSes.Where(t => t.ProcessID.ToString().EndsWith("2")).ToList();

2. If you want to make query like : "Select * from PROCESS Where CAST(ProcessID as varchar) Like '%2%'

then use :

var people = sample.PROCESSes.Where(t => t.ProcessID.ToString().Contains("2")).ToList();

RajeshKumar Sugumar 22-Aug-13 5:32am

ok. But i use this query for my search page so the user can search the values in any way (2*,*2,*2*) . Here i want to do all this operation in same Where condition itself . This is the problem which i am facing here.

CodeBlack 22-Aug-13 6:33am

*2 means ProcessID ending with 2, 2* means ProcessID starting with 2, and *2* means ProcessID contains 2. So I think there is no need for first two conditions as those two conditions are satisfied with last one *2* (%2%).

So only *2* is enough. So
var people = sample.PROCESSes.Where(t => t.ProcessID.ToString().Contains("2")).ToList();

RajeshKumar Sugumar 22-Aug-13 10:26am

*2 means ProcessID ends with 2, 2* means ProcessID starts with 2, *2* means ProcessID Contains 2. If i used Contains for all these means the excepted output will me failed.

If the User Except the ProcessID starts with 2 means , the Outcome result will be "Contains" ProcessID.

Sample Data
PROCESSID
21
11
12
222
245
357
572

if the User Search for 2* means , the outcome result will be "21,222,245". If i used "contains" means the outcome result will be "21,12,222,245,572". So this is Wrong Output.

CodeBlack 23-Aug-13 0:05am

yes. that was a typo mistake.

so if you use contains then output will be all the processId which is having 2 anywhere like 12,21,121,222,122,321

RajeshKumar Sugumar 23-Aug-13 6:21am

Thanks !!!

But this is not my excepted Solution. I need to use the SQL Query as like in Linq .

Anyway thanks .......

Solution 2

how-to-do-a-like-query-with-linq[^]

Hope it will help..

Posted 26-Aug-13 18:20pm

Code-Hunt

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RajeshKumar Sugumar · Accepted Answer · 2013-09-03T00:26:00

SQL

public IQueryable<TRow> WildcardSearchByInt<TRow>(string propertyName, string searchCritriea,IObjectSet<TRow> records) where TRow :class
      {
          return ((ObjectSet<TRow>)records).Where("CAST(it." + propertyName + " as System.String) LIKE @search", new ObjectParameter("search", searchCritriea.Replace("*", "%")));
      }

This is code which is used for SQL Like Functionality for INT DataType in LINQ to ENTITIES

Sample
-------

XML

IQueryable<process> processQuery = null;
           if (process.processIDString != null && process.processIDString .Contains("*"))
           {
               processQuery = WildcardSearchByInt<process>("Process_ID", process.processIDString, processEntity);
           }