[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

LINQ -- easy "StartsWith" issue

Posted on 2013-12-17
3
Medium Priority
?
3,708 Views
Last Modified: 2016-02-10
Below #2 works when using the "MicrosoftProduct"
LIST, but fails when trying to get via the
"Lookup_PartNumbers" database table.

How can I get the below #2
working when using a database table ?

Steps
 1. user types "10"
 2. all PartNumbers that "startWith" 10.... from
    Lookup_PartNumbers should appear, but nothing does
 3. user types "100"
 4. all PartNumbers that are exact matches from
    Lookup_PartNumbers appear correctly
----------------------------------------------------------------------------

        public JsonResult getData(string term)
        {
            /*
            List<string> MicrosoftProduct = new List<string>();
            MicrosoftProduct.Add("Office");
            MicrosoftProduct.Add(".NET");
            MicrosoftProduct.Add("Visual Studio");
            MicrosoftProduct.Add("sql server");
            MicrosoftProduct.Add("Windows7");
            MicrosoftProduct.Add("Window8");
            */

            var mySearchResults =
                from p in db.Lookup_PartNumbers
                //.Where(c => c.PartNumber.StartsWith(term, StringComparison.CurrentCultureIgnoreCase))
                    //above display below error
                    //LINQ to Entities does not recognize the method 'Boolean StartsWith(System.String, System.StringComparison)' method, and this method cannot be translated into a store expression.
                       
                //.Where(c => c.PartNumber.Contains(term))
                    //above does not error, but typing "10" displays nothing
                    //only after typing the FULL partNumber, "100", does the result display

                //.Where(c => c.PartNumber.StartsWith(term))
                    //above does not error, but typing "10" displays nothing
                    //only after typing the FULL partNumber, "100", does the result display

                .Take(10)
                .Select(c => c.PartNumber + c.PartDescription)
                .ToList()
                select p;

            List<string> getValues = mySearchResults.Where(item => item.ToLower().StartsWith(term.ToLower())).ToList();

            // Return the result set as JSON
            return Json(getValues, JsonRequestBehavior.AllowGet);
        }
0
Comment
Question by:finance_teacher
  • 2
3 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39724060
Because the Linq to SQL and Linq to Entity Framework work a little differently with strings please indicate which technology are you using.
0
 

Author Comment

by:finance_teacher
ID: 39724333
LINQ to Entities, but am willing to do anything to make it work.
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39724558
Hi finance_teacher;

Linq to Entity Framework only implements one of the overloads of the StartsWith methods the one that has only one parameter the other two are not implemented.

This should work.

var mySearchResults = db.Lookup_PartNumbers
                        .Where(c => c.PartNumber.StartsWith(term))
                        .Select(c => c.PartNumber + c.PartDescription)
                        .Take(10)
                        .ToList();

Open in new window


Now the above Linq to Entity Framework query should get translated to the following T-SQL statement

ELECT TOP (10) 
[Extent1].[PartNumber] + [Extent1].[PartDescription] AS [C1]
FROM [dbo].[Lookup_PartNumbers] AS [Extent1]
WHERE [Extent1].[PartNumber] LIKE N'10%'

Open in new window


Note that the server filters on LIKE N'10%' so if any PartNumber starts with "10" it should return it.

Pattern Matching by Using LIKE
LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server.

Please see the following documentation for reason why it may not be returning anything.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The PowerShell Core 6.0 of .NET release is just the beginning. The upcoming PowerShell Core 6.1 would have artificial intelligence and internet of things capabilities. So many things to look forward to in the upcoming release.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

612 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