Solved

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

Posted on 2011-02-28
6
767 Views
Last Modified: 2013-11-11
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
Comment
Question by:kwh3856
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 35004544
What for is the @ character?  This one is normally used if you use predefined varaibles what you do not do here.
0
 

Author Comment

by:kwh3856
ID: 35007857
I thought that was the wildcard symbol.  If not...what is a wildcard symbol in LINQ?
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 35007966
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
Stressed Out?

Watch some penguins on the livecam!

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 35008262
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
 

Author Closing Comment

by:kwh3856
ID: 35009884
Fernando
Thank you very much. I appreciate it when you answer my questions because I am always learning something new. Thanks
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 35009951
Not a problem Kenny, always glad to help.  ;=)
0

Featured Post

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question