Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

Need help with WHERE clause in LINQ trying to use a wildcard

I am asking the user to key in a code to lookup.  I want them to be able to key in just part of the code and my result come back with the closest match to their partial entry in the field.  Example.

Actual Code: 234.32

They key in:
23   -  it would return in the list
234 - it would return in the list
234. it would return in the list.

Can someone show me where I am goin wrong?


var ICD9Result = from ICD9data in dc1234.ICD9s
                                 where ICD9data.ICD9_cd.ToString().StartsWith("@"+tbDiagCodeLookup.ToString())
                                 select new { ICD9data.ICD9_cd, ICD9data.Name };

Open in new window

0
kwh3856
Asked:
kwh3856
  • 3
  • 2
1 Solution
 
HugoHiaslCommented:
What for is the @ character?  This one is normally used if you use predefined varaibles what you do not do here.
0
 
kwh3856Author Commented:
I thought that was the wildcard symbol.  If not...what is a wildcard symbol in LINQ?
0
 
Fernando SotoCommented:
Hi Kenny;

The error is that you have prepend a @ to the search string. So what you are sending to the server is @23, @234 and @234.So remove the @ sign. So try it like this:

var ICD9Result = from ICD9data in dc1234.ICD9s
                     where ICD9data.ICD9_cd.ToString().StartsWith(tbDiagCodeLookup.ToString())
                     select new { ICD9data.ICD9_cd, ICD9data.Name };

Open in new window


There is a better way which gives more flexibility and that is to use the T-SQL Like command in the query as follows:

using System.Data.Linq.SqlClient;                 
                 
string pattern = "23%";

var ICD9Result = from ICD9data in dc1234.ICD9s
                 where SqlMethods.Like(ICD9data.ICD9_cd.ToString(), pattern)
                 select new { ICD9data.ICD9_cd, ICD9data.Name };                 

Open in new window



The pattern can be any T-SQL Like meta-characters.

Fernando
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Fernando SotoCommented:
Hi Kenny;

To your question, "I thought that was the wildcard symbol.  If not...what is a wildcard symbol in LINQ?", When you use the Startswith the function takes your string and adds a % to the end of the string. Using the SqlMethods.Like you place the meta-characters where you like  using the T-SQL Like pattern characters found here.

Fernando
0
 
kwh3856Author Commented:
Fernando
Thank you very much. I appreciate it when you answer my questions because I am always learning something new. Thanks
0
 
Fernando SotoCommented:
Not a problem Kenny, always glad to help.  ;=)
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now