linq to entity does not recognize the method

Question

I need to call a method FormatCourseTitle() from linq query but receive error message "Linq to Entity does not recognize the method FormatCourseTitle..." How to solve this problem?

 public ActionResult Index()
{
    var searchResults = (from a in db.Courses
                    join b in db.Summary on
                    new { subject = a.subject, catalog = a.catalog, coursetitle = FormatCourseTitle(a.coursetitle) } equals
                                  new { subject = b.Subject, catalog = b.Catalogno, coursetitle = b.CourseTitle } into ab
                                 from b in ab.DefaultIfEmpty()
                                 where a.Active == true 
                                 select new JoinModel
                                 {
                                     Courses = a,
                                     Summary2020 = b
                                 } ).ToList();
    return View(searResults);

}
 public class JoinModel
{
    [Key]
    public int Id { get; set; }

    public Courselist Courses { get; set; }
    public Contentsummary Summary { get; set; }

}

 public string FormatCourseTitle(string courseTitle)
    {
        //find if last three characters are " or"
        string[] words = courseTitle.Trim().ToLower().Split(' ');
        int j = words.Count();
        string tempStr = string.Empty;              
            if (words[j] == "or")
            {
                tempStr = courseTitle.Substring(0, courseTitle.Length - 3);
            }
            else
            {
                tempStr = courseTitle;
            }


            return tempStr;
    }

Answer 1

I find a way to add condition in linq query to solve this problem.

var searchResults = (from a in db.Courses
                join b in db.Summary on
                new { subject = a.subject, catalog = a.catalog, coursetitle = a.coursetitle.Trim().EndsWith(" or")?a.coursetitle.Substring(0,a.coursetitle.Length-3):a.coursetitle } equals
                              new { subject = b.Subject, catalog = b.Catalogno, coursetitle = b.CourseTitle } into ab
                             from b in ab.DefaultIfEmpty()
                             where a.Active == true 
                             select new JoinModel
                             {
                                 Courses = a,
                                 Summary2020 = b
                             } ).ToList();

Answer 2

You should use extension method as

public static class StringHelper
    {

        public static string FormatCourseTitle(this string courseTitle)
        {
            //find if last three characters are " or"
            string[] words = courseTitle.Trim().ToLower().Split(' ');
            int j = words.Count();
            string tempStr = string.Empty;
            if (words[j] == "or")
            {
                tempStr = courseTitle.Substring(0, courseTitle.Length - 3);
            }
            else
            {
                tempStr = courseTitle;
            }


            return tempStr;
        }
    }

Change query to

var searchResults = (from a in db.Courses
                    join b in db.Summary on
                    new { subject = a.subject, catalog = a.catalog, coursetitle = a.coursetitle.FormatCourseTitle() } equals
                                  new { subject = b.Subject, catalog = b.Catalogno, coursetitle = b.CourseTitle } into ab
                                 from b in ab.DefaultIfEmpty()
                                 where a.Active == true 
                                 select new JoinModel
                                 {
                                     Courses = a,
                                     Summary2020 = b
                                 } ).ToList();