Solved

LINQ -- easy "StartsWith" issue

Posted on 2013-12-17
3
3,001 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 63

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 63

Accepted Solution

by:
Fernando Soto earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 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