Can I use extension method inside LINQ query?

Question

I have the following code:

public QuestionDetail GetQuestionDetail(int questionId) { Question question = _questionsRepository.GetById(questionId); QuestionDetail questionDetail = new QuestionDetail() { QuestionId = questionId, Text = question.Text.FormatCode() }; return questionDetail; } 

I replaced this with:

  public QuestionDetail GetQuestionDetail(int questionId) { var questions = _questionsRepository .GetAll() .Include(q => q.Answers) .Select(m => new QuestionDetail { QuestionId = m.QuestionId, Text = m.Text.FormatCode() }) .FirstOrDefault(); return questions; } 

Now I get the following error message:

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

Here is my FormatCode ()

 public static class CodeDisplay { public static string FormatCode(this string content) { var data1 = content .Split(new[] { "<pre>", "</pre>" }, StringSplitOptions.None); var data2 = data1 .Select((s, index) => { string s1 = index % 2 == 1 ? string.Format("{0}{2}{1}", "<table class='code'>", "</table>", SplitJoin(s)) : s; return s1; }); var data3 = data2.Where(s => !string.IsNullOrEmpty(s)); var data4 = string.Join("\n", data3); return data4; } private static string SplitJoin(string content) { IEnumerable<String> code = content.Split(new[] { '\n' }, StringSplitOptions.RemoveEmptyEntries) .Select((line, index) => string.Format("<tr><td>{0}</td><td><pre><code>{1}</code></pre></td></tr>\n", (index + 1).ToString("D2"), HttpUtility.HtmlEncode(line))); return string.Join("", code) + "\n"; } } 

Answer 1

Short answer: Yes, you can use your own extension method inside a LINQ query, but you cannot use an extension method that the underlying data provider does not know how to execute.

LINQ stands for Language-Integrated-Query and is a language feature in C #. You can use any .NET method in a LINQ query. LINQ as such does not know about the underlying data warehouse, the details of which are exposed to LINQ via the IEnumerable<T> or IQueryable<T> interfaces. When you request an object that implements IQueryable<T> , such as an Entity infrastructure table, the interface provides the underlying LINQ provider that knows about queries in the Entity Framework / SQL.

When using any method in a query, the .NET method must have a transformation to the underlying data provider for this to work. For LINQ-to-Objects (where no database is used), this conversion is trivial (i.e. no conversion is required), so you can use any extension methods. For LINQ-to-SQL or LINQ-to-Entities (how you use), the underlying data provider needs to know how to convert the CLR method to a view in a underlying repository such as SQL. This is the task of LINQ providers.

Therefore, you cannot use your own extension method inside your LINQ-to-Entities query. To do this, the LINQ provider must know how to present its method in SQL, and it does not know this.

A common way to achieve this, in any case, is to execute the query in the base dataprovider by calling one of the impatient methods, such as ToArray() or ToList() , and then refine the query afterwards using a custom method. Since the result is simple CLR objects, LINQ-to-Objects are used and you can use your own CLR method. Just keep in mind that this can potentially extract a lot of results from the database. For your example, you get only one result, so it does not matter.

Applying the specified template to your code will look like this:

 public QuestionDetail GetQuestionDetail(int questionId) { var questions = _questionsRepository .GetAll() .Include(q => q.Answers) .Take(1) // Constrain to one result fetched from DB .ToArray() // Invoke query in DB .Select(m => new QuestionDetail { QuestionId = m.QuestionId, Text = m.Text.FormatCode() }) .FirstOrDefault(); return questions; } 

Answer 2

Instead of trying to run the FormatCode () method in LINQ to Entities, which fails because the ADO.NET provider does not know how to translate it into SQL, you can run the maximum part of the query that can be executed as LINQ for Entities , eg:

 var questionTmp = _questionsRepository .GetAll() //.Include(q => q.Answers) // commented out since you aren't selecting this .Select(m => new // Anonymous type { QuestionId = m.QuestionId, Text = m.Text, // raw data to be used as input for in-memory processing }) .FirstOrDefault(); // or use .ToList(); if you want multiple results 

Then run the in-memory method on the result, for example:

 // For one var question = new QuestionDetail { QuestionId = questionTmp.QuestionId, Text = questionTmp.Text.FormatCode(), }; // Or for many var questions = questionsTmp.Select(q => new QuestionDetail { QuestionId = q.QuestionId, Text = q.Text.FormatCode(), }); return question; // or questions; 

Answer 3

You can try

 public QuestionDetail GetQuestionDetail(int questionId) { Question question = _questionsRepository.GetById(questionId).ToList(); QuestionDetail questionDetail = new QuestionDetail() { QuestionId = questionId, Text = question.Text.FormatCode() }; return questionDetail; } 

This materializes the question in memory, and you can apply the desired format.