LINQ to Entities does not recognize the method Replace?

Question

How to use replace method in entity framework. I use following code but encounter error.

using (SepasProjectEntities sp=new SepasProjectEntities())
{
var query = (from p in sp.HISAccidentLocationMappings
                         where p.Name.Replace('y','x').Contains(txt1.Text)
                         select p
                           ).ToList();
}

An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.String Replace(Char, Char)' method, and this method cannot be translated into a store expression.

What should I do for `replace` method? – Raymond Morphy Jan 07 '14 at 18:31 — Raymond Morphy, Jan 07 '14 at 18:31

score 15 · Answer 1 · answered Jan 07 '14 at 18:42

15

Based on this MSDN article that contains list of supported methods by Entity Framework - there is just one overload of Replace method that is supported, and it's

System.String Replace(String oldValue, String newValue)

And not

System.String Replace(char oldValue, char newValue)

that you are using. Try to replace it with string version from

Name.Replace('y','x')

to

Name.Replace("y","x")

I didn't try it, but based from documentation it should work

answered Jan 07 '14 at 18:42

Sergey Litvinov

7,408
5
46
67

Based on that article - Contains(string val) method is also supported by EF – Sergey Litvinov Jan 07 '14 at 18:45

keeehlan · Accepted Answer · 2014-01-07T18:49:28.383

11

I usually use the other format of LINQ queries, like this:

using (SepasProjectEntities sp = new SepasProjectEntities())
{
    var query = sp.HISAccidentLocationmappings
                    .Where(p => p.Name != null
                        && p.Name
                            .Replace("y", "x")
                            .Contains(txt1.Text))
                    .ToList();
}

Replace(char, char) won't work but Replace(string, string) will. Contains(string) should also work.

edited Jan 07 '14 at 18:49

answered Jan 07 '14 at 18:30

keeehlan

7,874
16
56
104

2

in that way you will load all HISAccidentLocationmappins objects from DB, and then filter them on server side. E.g if that table in DB has 10000 records, and WHERE will filter just 5 of them, then all 10000 records will be loaded to application, that is a big waste of resources. – Sergey Litvinov Jan 07 '14 at 18:37
@SergeyLitvinov The OP is trying to use the `Replace()` and `Contains()` methods in a LINQ query. I don't think he has many other options. – keeehlan Jan 07 '14 at 18:39
I get this error: `An exception of type 'System.NullReferenceException' occurred in App_Web_bmikir7o.dll but was not handled in user code` – Raymond Morphy Jan 07 '14 at 18:42
Query syntax vs method syntax is irrelevant here: it can compile to the same thing. This will work, but means pulling down all of the data first, which could be VERY slow. – Tim S. Jan 07 '14 at 18:45
@TimS. I realized a couple of things I hadn't known before, so I updated my answer again. Hope that helps. – keeehlan Jan 07 '14 at 18:47
1

One comment I want to make (which is in my answer): If possible, I would recommend running the `Replace` against txt1.Text. This way the `Replace` is only done once, instead of having the database run the operation against potentially every row in the target table. – Brian Ball Jan 07 '14 at 19:30

score 1 · Answer 3 · answered Jan 07 '14 at 18:43

1

Can you flip it around? By that, I'm asking if you can do the replace on the txt1.Text value (and store it in a local variable), then compare it to the value in the database (I'm pretty sure String.Contains IS supported so long as you use the single argument overload).

answered Jan 07 '14 at 18:43

Brian Ball

12,268
3
40
51

I originally had that same idea, but then realized that `Contains()` is in fact supported by LINQ to Entities. – keeehlan Jan 07 '14 at 18:48
The error is about `Replace` not `Contains`. I think @SergeyLitvinov has the best answer: certain overloads of `Replace` are supported, the OP is invoking one that isn't. – Brian Ball Jan 07 '14 at 18:51
His answer and mine are pretty much the same after all the edits :p – keeehlan Jan 07 '14 at 18:52
True, but I would still recommend doing the replace on txt1.Text before running the query (if possible), it's better to do replace once, than to have the database do it for every row in the target table. – Brian Ball Jan 07 '14 at 18:55

LINQ to Entities does not recognize the method Replace?

3 Answers3