entity framework and where with like

Question

I' m using this instruction:

db_user = db.CBR_User.FirstOrDefault(p => p.Codice_Fiscale == code);

I want use "like" operator instead == for manage case insensitive

How can do it?

thanks

score 3 · Answer 1 · answered Feb 10 '11 at 14:09

3

You can use Contains():

db_user = db.CBR_User.FirstOrDefault(p => p.Codice_Fiscale.Contains(code));

answered Feb 10 '11 at 14:09

Kristof Claes

10,797
3
30
42

-1 Contains() can be used to replace a SQL like. However, it is case sensitive and has no built-in case insensitive equivalent. Review the remarks section of the MSDN article on Contains: http://msdn.microsoft.com/en-us/library/dy85x1sa.aspx – Corin Oct 19 '12 at 20:55
Contains is translated to `LIKE` in SQL and there it _is_ case insensitive. `WHERE Name LIKE '%rob%'` returns the same results as `WHERE Name LIKE '%ROB%'`. – Kristof Claes Oct 20 '12 at 09:43
In fact case sensitiveness of `LIKE` depends on the collation setting in the database. The MSDN link you mentioned has nothing to do with the SQL generated by EF. – Kristof Claes Oct 20 '12 at 11:46
The case sensitivity of LIKE is indeed based on the collation in the database. However, the reason I found this question was precisely because Contains was behaving case sensitive even though the collation of the column was insensitive and I was looking for case insensitive alternatives to Contains. – Corin Oct 22 '12 at 15:52

score 2 · Accepted Answer · answered Feb 10 '11 at 14:35

2

If you want to do an equality comparison anyway, I suggest two ways:

db_user = db.CBR_User.FirstOrDefault(p => p.Codice_Fiscale.Equals(code, StringComparison.OrdinalIgnoreCase));

Or

db_user = db.CBR_User.FirstOrDefault(p => p.Codice_Fiscale.ToUpper() == code.ToUpper());

If you don't want that, you can use StartsWith, Contains, etc, with the parameter StringComparison.OrdinalIgnoreCase.

answered Feb 10 '11 at 14:35

Francisco

4,104
3
24
27

I'd also note that depending on your database (sql 2008) may not be case-sensitive anyway. – Erik Philips Dec 07 '12 at 16:57
Yes, indeed... it depends on the database – Francisco Feb 21 '13 at 19:55

score 1 · Answer 3 · edited May 23 '17 at 10:24

1

I know this is an old topic, but to expand on what Kristof Claes was saying,

db_user = db.CBR_User.FirstOrDefault(p => p.Codice_Fiscale.ToUpper().Contains(code.ToUpper()));

if you use the ToUpper option, it will act as if it is a case insensitive search. You could use ToLower to do the same thing, in case you have that penchant.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 07 '12 at 16:34

computercarguy

2,173
1
13
27

score 0 · Answer 4 · answered Feb 10 '11 at 14:11

0

In LINQ To Entity you have functions like StartsWith, EndsWith and Contains that you can use instead like

answered Feb 10 '11 at 14:11

ravenik

852
3
9
26

entity framework and where with like

4 Answers4

Linked