Use Replace Method In Linq-Sql Query

Question

I used RegEx to remove HTML TAGS within LINQ-SQL query but the following error has thrown:

Method 'System.String Replace(System.String, System.String, System.String)' is not supported for execution as SQL.

Help helpDBSession = new Help();
          IEnumerable<Article> articles = null;
          if (lang.ToLower() == "en")
          {
              articles = helpDBSession.Articles.Where(artilce =>  artilce.NameEn.Contains(searchPattern) ||
                       System.Text.RegularExpressions.Regex.Replace(artilce.ContentEn, "<(.|\n)*?>",String.Empty).Contains(searchPattern));
          }
          else
          {
              articles = helpDBSession.Articles.Where(artilce => artilce.NameAr.Contains(searchPattern) ||

                  System.Text.RegularExpressions.Regex.Replace(artilce.ContentAr, "<(.|\n)*?>", String.Empty).Contains(searchPattern));
          }
          if (articles != null && articles.Count() > 0)
          {
              return articles.ToList();
          }

Answer 1

Two things wrong here:

1. You are parsing HTML with a RegEx. You should use the HTML Agility Pack to parse out HTML, not rely on RegEx. See here for reasons why.
2. You are treating Linq2Sql as if SQL is not around - when using Replace, it tries to pass it through to SQL Server - this of course fails, as SQL does not have this function. This would fail anyway, as there is no overload to string.Replace that takes three strings.

You did not explain what exactly you need to achieve, but if you need to store some HTML in SQL, I suggest you parse it with the agility pack and use that for cleaning out the tags, then save the result to SQL Server.

Answer 2

Based on the error message I'm assuming LINQ to SQL can not translate the RegEx section of the LINQ statements because SQLServer does not support RegEx.

You will have to:

• Get articles from the database with on the "Contains" section of your where statement.
• Convert the on the results to a list.
• Apply your regular expression to a where on the list.

For example:

Help helpDBSession = new Help();

IEnumerable<Article> articles = null;

if (lang.ToLower() == "en")
{
    articles = helpDBSession.Articles.Where(
                   artilce =>  artilce.NameEn.Contains(searchPattern)
    )
}
else
{
    articles = helpDBSession.Articles.Where(
                   artilce => artilce.NameAr.Contains(searchPattern)
    )
}

if (articles != null && articles.Count() > 0)
{
    if (lang.ToLower() == "en")
    {
        return articles.ToList().Where(
            artilce => System.Text.RegularExpressions.Regex.Replace(
                artilce.ContentEn, 
                "<(.|\n)*?>",String.Empty).Contains(searchPattern)
            )
        );
    }
    else
    {
        return articles.ToList().Where(
            artilce => System.Text.RegularExpressions.Regex.Replace(
                artilce.ContentAr, 
                "<(.|\n)*?>",String.Empty).Contains(searchPattern)
            )
        );
     }
}