Hi iGulfam, thanks for the reply. Your example is TSQL though. I'm looking for a way to do this via c# and linq to sql. When using tsql directly, I can already just use 'like' with an int, no problem. I'm currently working on an app where I don't really
have any control over the existing database design, so if possible, I want to do all my data access code within my app. If there is no reasonable way to achieve this however, I will just have to get a stored procedure added to the database that implements
what I need and just call that stored procedure from my c#/linq code.
is there any reasonable way to achieve this via c# and linq to sql?
that will require pulling the whole table down to the client into a dataview object first, then filtering it further, right? If so, I think I would go the stored proc route first because this table has 100,000+ records. I need the WHERE criteria to limit
this, but my WHERE criteria needs to contain wildcards, and work on int.
This is my first project I'm attempting to use linq to sql on, and of course my luck brings problems immediately... right away, this is the first query I need, hehe. I'm not hating on linq to sql, I don't know it well yet, just commenting more on 'my luck'
:)
Contains method should work like the function "Like" in T-SQL query. If no rows be returned, have you checked the converted query by LINQ to SQL? Or you can use SQL Server Profiler to check the final T-SQL query directly.
To implement the "Like" function in LINQ to SQL, you can also check out this helpful post:
thanks for the reply. I started using a stored procedure but I wanted to try your suggestion also. I am getting a NotSupportedException. It says the IsMatch method has no supported translation to TSQL? Here is what my code looks like:
var clientsQuery = from clients in dc.ClientInformations
where System.Text.RegularExpressions.Regex.IsMatch(clients.ClientID.ToString(), datamasks[0]) == true
select clients;
did I get something wrong there? Or is it really just not supported?
I don't think the other responses really went for what you really asked for, i.e a way to make a L2S query perform a pattern based query, and on an integer field at that. And not in memory.
Regex.IsMatch can't be used, since it can be translated into SQL, so what is needed is this.
var clientsQuery = from clients in dc.ClientInformations
where System.Data.Linq.SqlClient.SqlMethods.Like(clients.ClientID.ToString(), "10_1")
select clients;
ToString will result in an SQL CONVERT and the L2S specific Like method will be translated to an SQL LIKE operation.
Or, you can create it without pattern matching...
var clientsQuery = from clients in dc.ClientInformations
where
clients.ClientID.ToString().StartsWith("10") && clients.ClientID.ToString().EndsWith("1") && clients.ClientID.ToString().Length==4
select clients;
c0pe
Member
319 Points
435 Posts
need to implement tsql 'like' operator on an int field?
Jul 27, 2010 01:19 AM|LINK
Hello,
I need to return rows based on my where clause using wildcards (% and _).
e.g.
select * from myTable
where SomeIntField like '10_1'
this works in tsql, how do I do in linq to sql using c#?
Tried 'contains' like this but it returns no records:
var myQuery = from i in myDataContext.IDs
where i.ID.ToString().Contains('10_1')
select i
?
iGulfam
Contributor
4794 Points
947 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 27, 2010 07:41 AM|LINK
You can convert that int field to varchar to use wild card search. That is your query will be like
My BLOG
c0pe
Member
319 Points
435 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 27, 2010 11:28 AM|LINK
Hi iGulfam, thanks for the reply. Your example is TSQL though. I'm looking for a way to do this via c# and linq to sql. When using tsql directly, I can already just use 'like' with an int, no problem. I'm currently working on an app where I don't really have any control over the existing database design, so if possible, I want to do all my data access code within my app. If there is no reasonable way to achieve this however, I will just have to get a stored procedure added to the database that implements what I need and just call that stored procedure from my c#/linq code.
is there any reasonable way to achieve this via c# and linq to sql?
iGulfam
Contributor
4794 Points
947 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 27, 2010 11:44 AM|LINK
You can do this in C# using RowFilter property of DataView. Store result of your query in a dataview (let say dvTest) and then write following line
My BLOG
c0pe
Member
319 Points
435 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 27, 2010 02:55 PM|LINK
that will require pulling the whole table down to the client into a dataview object first, then filtering it further, right? If so, I think I would go the stored proc route first because this table has 100,000+ records. I need the WHERE criteria to limit this, but my WHERE criteria needs to contain wildcards, and work on int.
This is my first project I'm attempting to use linq to sql on, and of course my luck brings problems immediately... right away, this is the first query I need, hehe. I'm not hating on linq to sql, I don't know it well yet, just commenting more on 'my luck' :)
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: need to implement tsql 'like' operator on an int field?
Jul 29, 2010 03:16 AM|LINK
Hi c0pe,
Contains method should work like the function "Like" in T-SQL query. If no rows be returned, have you checked the converted query by LINQ to SQL? Or you can use SQL Server Profiler to check the final T-SQL query directly.
To implement the "Like" function in LINQ to SQL, you can also check out this helpful post:
http://davidhayden.com/blog/dave/archive/2007/11/23/LINQToSQLLIKEOperatorGeneratingLIKESQLServer.aspx
Thanks.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
nareshguree2...
Star
11118 Points
1997 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 29, 2010 03:22 AM|LINK
for your query
here contain function work like in with %.
from u in dc.Users
where u.Contains(Name)
suppose you entered name = a
it will return result like
Ajit,
Naresh,
Navish
sansan
All-Star
53942 Points
8147 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 29, 2010 04:39 AM|LINK
you can do this
That should do a wildchar search for you.
try that and see if it works for you
c0pe
Member
319 Points
435 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 29, 2010 11:45 PM|LINK
Hi sansan,
thanks for the reply. I started using a stored procedure but I wanted to try your suggestion also. I am getting a NotSupportedException. It says the IsMatch method has no supported translation to TSQL? Here is what my code looks like:
var clientsQuery = from clients in dc.ClientInformations where System.Text.RegularExpressions.Regex.IsMatch(clients.ClientID.ToString(), datamasks[0]) == true select clients;did I get something wrong there? Or is it really just not supported?
thanks again, I appreciate it.
gunteman
All-Star
22406 Points
3305 Posts
Re: need to implement tsql 'like' operator on an int field?
Jul 30, 2010 12:43 AM|LINK
I don't think the other responses really went for what you really asked for, i.e a way to make a L2S query perform a pattern based query, and on an integer field at that. And not in memory.
Regex.IsMatch can't be used, since it can be translated into SQL, so what is needed is this.
var clientsQuery = from clients in dc.ClientInformations where System.Data.Linq.SqlClient.SqlMethods.Like(clients.ClientID.ToString(), "10_1") select clients;ToString will result in an SQL CONVERT and the L2S specific Like method will be translated to an SQL LIKE operation.
Or, you can create it without pattern matching...
var clientsQuery = from clients in dc.ClientInformations where clients.ClientID.ToString().StartsWith("10") && clients.ClientID.ToString().EndsWith("1") && clients.ClientID.ToString().Length==4 select clients;