Solved

LINQ -- easy "StartsWith" issue

Posted on 2013-12-17
3
2,744 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 62

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 62

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now