LINQ to Entities does not recognize the method 'Int32 IndexOf (System.String, System.StringComparison)' method

Question

I executed a linq request using Entityframework as below

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

while executing this method I got an exception as shown below

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

Contains () the default case-sensitive method, so again I need to convert to lower.Is there is any string matching method other than contains method, and is there any method to solve the indexOf method problem?

Answer 1

You really only have four options.

• Change the sorting of the database worldwide. This can be done in several ways, a simple Google search should reveal them.
• Change the sorting of individual tables or columns.
• Use the stored procedure and specify the COLATE statement in your query
• execute a query and return a large set of results, and then filter in memory using Linq for objects.

number 4 is not a good option if your result is not very small. # 3 is good if you cannot modify the database (but you cannot use Linq with it).

numbers 1 and 2 are options that you need to make for your data model as a whole or if you want to do this only in certain fields.

Change server collation: http://technet.microsoft.com/en-us/library/ms179254.aspx

Changing the database sort: http://technet.microsoft.com/en-us/library/ms179254.aspx

Change Column Sort: http://technet.microsoft.com/en-us/library/ms190920 (v = sql.105) .aspx

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

Answer 2

Instead, you can use this method below to reduce cases:

 var lowerCaseItem = item.ToLower(); 

If your element is of type string . And that can help you with this exception.

Answer 3

IndexOf method String class will not be recognized by Entity Framework, replace this function with SQLfunction or Canonical functions

You can also use here or here.

You can use the sample code below:

DataContext.Groups.FirstOrDefault (item => System.Data.Objects.SqlClient.SqlFunctions.CharIndex (item.Keywords, keyword) .Value> = 0 & item.IsEnabled)

Answer 4

Eric Funkhenbush’s answer is absolutely correct, considering it as a database problem. But I feel that you need a better structure for storing keyword data if you want to move it efficiently.

Please note that this answer is not intended to improve, it is intended to fix the problem in your data model, and not to adapt the environment to the existing (apparently erroneous, as there is a problem) data model that you have.

My main suggestion, regardless of the time limit (I understand that this is not the easiest fix), would be to add a separate table for keywords (with a many-to-many relationship with related classes).

 [GROUPS] * ------- * [KEYWORD] 

This will allow you to search for a keyword and only then get the elements associated with it, related to it (based on identifier, not complex string).

 int? keywordID = DataContext.Keywords.Where(x => x.Name == keywordFilter).Select(x => x.Id).FirstOrDefault(); if(keywordID != null) { getGroup = DataContext.Groups.FirstOrDefault(group => group.Keywords.Any(kw => kw.Id == keywordID)); } 

But I fully understand if this type of correction is no longer possible in the current project. I would like to mention this, though, if someone stumbles on this question in the future and still has the opportunity to improve the data structure.