12

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?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Jameel Moideen
  • 7,542
  • 12
  • 51
  • 79
  • I might improve the answer if you expose the related fields of your `DataContext.Groups` object. – Tolga Evcimen Apr 22 '14 at 06:19
  • 5
    `Contains` is transformed into `LIKE` statement within generated SQL query. Fact whether that `LIKE` is case sensitive or case insensitive depends on database configuration. Change your database to perform case insensitive string comparison and use `Contains`. – MarcinJuraszek Apr 22 '14 at 06:21
  • It's an entity framework datacontext where Group is DbSet – Jameel Moideen Apr 22 '14 at 06:21
  • If its not part of SqlFunctions http://msdn.microsoft.com/de-de/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx you have to use LINQ to obejects instead to query entities. – Rand Random Apr 22 '14 at 06:26
  • actually Inside contains method I think they are checking the same index of functionality then why entityframework throw exception. – Jameel Moideen Apr 22 '14 at 06:28
  • @ MarcinJuraszek - How can I change the db to perform case insensitive string comparison to use Contains? – Jameel Moideen Apr 22 '14 at 06:32
  • @JEMI - You can't use indexOf in a Linq to Entities query, because the linq provider doesn't know how to translate it to SQL. That's what the error message is telling you. You can't use most generic functions inside Linq to Entities queries. – Erik Funkenbusch Apr 22 '14 at 06:46

4 Answers4

8

The IndexOf method Of string class will not recognized by Entity Framework, Please replace this function with SQLfunction or Canonical functions

You can also take help from here or maybe here

You can use below code sample:

DataContext.Groups.FirstOrDefault(item => 
    System.Data.Objects.SqlClient.SqlFunctions.CharIndex(item.Keywords, keyword).Value >=0 && item.IsEnabled)
Gilad Green
  • 36,708
  • 7
  • 61
  • 95
Harikant
  • 269
  • 1
  • 6
7

You really only have four options here.

  1. Change the collation of the database globally. This can be done in several ways, a simple google search should reveal them.
  2. Change the collation of individual tables or columns.
  3. Use a stored procedure and specify the COLATE statement on your query
  4. 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

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
5

Instead you can use this method below for lowering the cases:

var lowerCaseItem = item.ToLower();

If your item is of type string. Then this might get you through that exception.

Tolga Evcimen
  • 7,112
  • 11
  • 58
  • 91
1

Erik Funkenbush' answer is perfectly valid when looking at it like a database problem. But I get the feeling that you need a better structure for keeping data regarding keywords if you want to traverse them efficiently.

Note that this answer isn't intended to be better, it is intended to fix the problem in your data model rather than making the environment adapt to the current (apparently flawed, since there is an issue) data model you have.

My main suggestion, regardless of time constraint (I realize this isn't the easiest fix) would be to add a separate table for the keywords (with a many-to-many relationship with its related classes).

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

This should allow for you to search for the keyword, and only then retrieve the items that have that keyword related to it (based on ID rather than a compound 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 can understand completely if this type of fix is not possible anymore in the current project. I wanted to mention it though, in case anyone in the future stumbles on this question and still has the option for improving the data structure.

Flater
  • 12,908
  • 4
  • 39
  • 62