Can I use an extension method inside a 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's 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 a custom 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 store, the details of which is exposed to LINQ via the IEnumerable<T> or IQueryable<T> interfaces. When you are querying against an object that implements IQueryable<T>, such as an Entity Framework Table, the interface exposes the underlying LINQ provider, that knows about querying in Entity Framework / SQL.

When using any method in a query, the .NET method must have a conversion to the underlying data provider, for this to work. For LINQ-to-Objects (where no database is involved), this conversion is trivial (ie. no conversion needed), so you can use any extension methods. For LINQ-to-SQL, or LINQ-to-Entities (as you are using), the underlying data provider must know how to translate the CLR method to a representation in the underlying storage, such as SQL. This is the job of LINQ providers.

Therefore, you cannot use a custom extension method inside your LINQ-to-Entities query. For that to work, the LINQ provider would need to know how to represent your method in SQL, and it does not know that.

A common way to achieve this anyway, is to execute the query in the underlying dataprovider by calling one of the eager methods such as ToArray() or ToList(), and then further refining the query after that with your custom method. Because the result is simple CLR objects, LINQ-to-Objects is used, and you can use your custom CLR method. Just be aware that this could potentially fetch many results from the database. For your example, you are only fetching one result, so this does not matter.

Applying the mentioned pattern to your code would 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 the LINQ to Entities, which fails due to the fact that 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 run as LINQ to Entities like so:

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 method in-memory on the result, like so:

// 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;