Improve company productivity with a Business Account.Sign Up

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

LINQ -- easy "StartsWith" issue

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
finance_teacher
Asked:
finance_teacher
  • 2
1 Solution
 
Fernando SotoRetiredCommented:
Because the Linq to SQL and Linq to Entity Framework work a little differently with strings please indicate which technology are you using.
0
 
finance_teacherAuthor Commented:
LINQ to Entities, but am willing to do anything to make it work.
0
 
Fernando SotoRetiredCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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