How can l use Regex.Replace method within LINQ

Question

I have a controller with following method getMail():

public JsonResult getMail(string id)
{
    int Id = Convert.ToInt32(id);
    string pattern = @"\bmem_id\b";
    string replace = "100";         
    var result = (from a in db.tblmail_type
                  where a.Id == Id                              
                  select new MailModel
                  {
                      subject = a.Subject,
                      Content = Regex.Replace(a.Content, pattern, replace);
                  });
    return Json(result, JsonRequestBehavior.AllowGet);
}

This method is used for getting mail content. Before getting content, I want to replace a "mem_id" to "100" in the mail content. Default content is given below:

Content = "You have successfully registered with Member ID: mem_id"

I have used Regex.Replace() method in the LINQ. But this code doesn't change the content. When I change this code to this form which is given below, it's work properly.

public JsonResult getMail(string id)
{
    int Id = Convert.ToInt32(id);
    var input = db.tblmail_type.Where(x=>x.Id==Id).FirstOrDefault().Content;
    string pattern = @"\bmem_id\b";
    string replace = "100";            
    string content = Regex.Replace(input, pattern, replace);
    var result = (from a in db.tblmail_type
                  where a.Id == Id
                  select new MailModel
                  {
                      subject = a.Subject,
                      Content = content
                  });
    return Json(result, JsonRequestBehavior.AllowGet);
}

Why does this happen? Can anyone specify the reason behind this weird problem? How can I replace the "Content" within the LINQ?

First method you are replace each mail message. Second method you are replacing all the mail messages. The Regex is not in the loop. — jdweng
– jdweng, Commented Mar 31, 2018 at 8:41
What is the type of db.tblmail_type? I suspect the problem may be that in the first case your Regex.Replace is being executed in the database, which may have different regex rules. — Jon Skeet
– Jon Skeet, Commented Mar 31, 2018 at 8:57
@Jon Skeet, I have stored different type of mail in the table called tblmail_type in the database. How can I replace the content within the linq query. Do you have any solution? — Vignesh VS
– Vignesh VS, Commented Mar 31, 2018 at 9:22
That's still not the compile-time type of db.tblmail_type. What does VS show if you hover over it? We don't know how you're accessing the database at all. — Jon Skeet
– Jon Skeet, Commented Mar 31, 2018 at 10:07

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

You can use any one of the following solutions,

Solution 1:

Unfortunatelly, you won't be able to send the regex processing logic directly to the database. You'll need to get the Content from the database and then iterate over the list and apply regex. This can be done by using AsEnumerable(). It breaks the query into two part. First part is inside part( query before AsEnumerable()) is executed as Linq-to-SQL. Second part is outside part( query after AsEnumerable()) is executed as Linq-to-Objects. First part is executed on database and all data brought in to the client side. Second part (here it is where, select) is performed on the client side. So in short AsEnumerable() operator move query processing to client side.

var result = ( from a in db.tblmail_type.AsEnumerable()
               where a.Id == Id
               select new MailModel {
                        subject = a.Subject,
                        Content = Regex.Replace(a.Content, pattern, replace)
              });

Solution 2:

var result = db.tblmail_type.Where(x => x.Id == Id).AsEnumerable().Select(x => new MailModel { Content = Regex.Replace(x.Content, pattern, replace) , subject = x.Subject}).ToList();

Solution 3:

 var result = db.tblmail_type.Where(x => x.Id == Id).ToList();
 result.ForEach(x => x.Content = Regex.Replace(x.Content, pattern, replace));

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 31, 2018 at 11:00

Abhilash Ravindran C K

1,8562 gold badges17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vignesh VS Over a year ago

Thank you for the detailed explanation. These three solutions are perfectly working!

Collectives™ on Stack Overflow

How can l use Regex.Replace method within LINQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related