C# – LINQ to Entities does not recognize the method ‘Int32 IndexOf(System.String, System.StringComparison)’ method

centity-frameworklinqnet

I have executed a linq query by using Entityframework like below

GroupMaster getGroup = null;
getGroup = DataContext.Groups.FirstOrDefault(item => keyword.IndexOf(item.Keywords,StringComparison.OrdinalIgnoreCase)>=0 && item.IsEnabled)

when executing this method I got exception like below

LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String, System.StringComparison)' method, and this
method cannot be translated into a store expression.

Contains() method by default case sensitive so again I need to convert to lower.Is there any method for checking a string match other than the contains method and is there any method to solve the indexOf method issue?

Best Answer

You really only have four options here.

Change the collation of the database globally. This can be done in several ways, a simple google search should reveal them.
Change the collation of individual tables or columns.
Use a stored procedure and specify the COLATE statement on your query
perform a query and return a large set of results, then filter in memory using Linq to Objects.

number 4 is not a good option unless your result set is pretty small. #3 is good if you can't change the database (but you can't use Linq with it).

numbers 1 and 2 are choices you need to make about your data model as a whole, or if you only want to do it on specific fields.

Changing the Servers collation: http://technet.microsoft.com/en-us/library/ms179254.aspx

Changing the Database Collation: http://technet.microsoft.com/en-us/library/ms179254.aspx

Changing the Columns Collation: http://technet.microsoft.com/en-us/library/ms190920(v=sql.105).aspx

Using the Collate statement in a stored proc: http://technet.microsoft.com/en-us/library/ms184391.aspx

Related Solutions

C# – LINQ to Entities does not recognize the method ‘Int32

You can split your query in two to make sure the list is in-memory before calling the GetNumberOfDevices() method. You can make a query in-memory by converting the results to a List, or in this case a List<GetFreeDevices>. That way, LinQ to Entities does not have to do any translations to and from XML and you can use your GetNumberOfDevices() method.

var view_query = (from i in query
     select new GetFreeDevices
     {
        MArticleNumber = i.ArticleNumber,
        MFirmware = i.Firmware,
        MGroup = i.Group,
        MName = i.Name,
        MSoftware = i.SoftwareVersion

     }).ToList();
var result_query = from i in query
     select new GetFreeDevices
     {
        MArticleNumber = i.MArticleNumber,
        MFirmware = i.MFirmware,
        MGroup = i.MGroup,
        MName = i.MName,
        MSoftware = i.MSoftware,
        SA = GetNumberOfDevices(i.MArticleNumber,2),
        STH = GetNumberOfDevices(i.MArticleNumber,3),
        SASTH = GetNumberOfDevices(i.MArticleNumber,7)
     };
return PartialView(result_query);

Please note that the last statement requires the PartialView to accept a list or IEnumerable instead of an IQueryable.

C# – LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression

Just save the string to a temp variable and then use that in your expression:

var strItem = item.Key.ToString();

IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem arises because ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails.

Note:

Make sure you also check out Alex's answer regarding the SqlFunctions helper class that was added later. In many cases it can eliminate the need for the temporary variable.

Best Answer

Related Solutions

C# – LINQ to Entities does not recognize the method ‘Int32

C# – LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression

Note:

Related Topic