Custom Method in LINQ Query

Question

I sum myself to the hapless lot that fumbles with custom methods in LINQ to EF queries. I've skimmed the web trying to detect a pattern to what makes a custom method LINQ-friendly, and while every source says that the method must be translatable into a T-SQL query, the applications seem very diverse. So, I'll post my code here and hopefully a generous SO denizen can tell me what I'm doing wrong and why.

The Code

    public IEnumerable<WordIndexModel> GetWordIndex(int transid)
    {
        return (from trindex in context.transIndexes
                let trueWord = IsWord(trindex)
                join trans in context.Transcripts on trindex.transLineUID equals trans.UID
                group new { trindex, trans } by new { TrueWord = trueWord, trindex.transID } into grouped
                orderby grouped.Key.word
                where grouped.Key.transID == transid
                select new WordIndexModel
                {
                    Word = TrueWord,
                    Instances = grouped.Select(test => test.trans).Distinct()
                });

    }

    public string IsWord(transIndex trindex)
    {
        Match m = Regex.Match(trindex.word, @"^[a-z]+(\w*[-]*)*",
                              RegexOptions.IgnoreCase);
        return m.Value;

    }

With the above code I access a table, transIndex that is essentially a word index of culled from various user documents. The problem is that not all entries are actually words. Nubers, and even underscore lines, such as, ___________,, are saved as well.

The Problem

I'd like to keep only the words that my custom method IsWord returns (at the present time I have not actually developed the parsing mechanism). But as the IsWord function shows it will return a string.

So, using let I introduce my custom method into the query and use it as a grouping parameter, the is selectable into my object. Upon execution I get the omninous:

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

I also need to make sure that only records that match the IsWord condition are returned.

Any ideas?

Answer 1

It is saying it does not understand your IsWord method in terms of how to translate it to SQL.

Frankly it does not do much anyway, why not replace it with

return (from trindex in context.transIndexes
                let trueWord = trindex.word
                join trans in context.Transcripts on trindex.transLineUID equals trans.UID
                group new { trindex, trans } by new { TrueWord = trueWord, trindex.transID } into grouped
                orderby grouped.Key.word
                where grouped.Key.transID == transid
                select new WordIndexModel
                {
                    Word = TrueWord,
                    Instances = grouped.Select(test => test.trans).Distinct()
                });

What methods can EF translate into SQL, i can't give you a list, but it can never translate a straight forward method you have written. But their are some built in ones that it understands, like MyArray.Contains(x) for example, it can turn this into something like

...
WHERE Field IN (ArrItem1,ArrItem2,ArrItem3)

If you want to write a linq compatible method then you need to create an expresion tree that EF can understand and turn into SQL.

This is where things star to bend my mind a little but this article may help http://blogs.msdn.com/b/csharpfaq/archive/2009/09/14/generating-dynamic-methods-with-expression-trees-in-visual-studio-2010.aspx.

Answer 2

If the percentage of bad records in return is not large, you could consider enumerate the result set first, and then apply the processing / filtering?

var query = (from trindex in context.transIndexes
             ...
             select new WordIndexModel
                {
                    Word,
                    Instances = grouped.Select(test => test.trans).Distinct()
                });
var result = query.ToList().Where(word => IsTrueWord(word));

return result;

If the number of records is too high to enumerate, consider doing the check in a view or stored procedure. That will help with speed and keep the code clean.

But of course, using stored procedures has disadvatages of reusability and maintainbility (because of no refactoring tools).

Also, check out another answer which seems to be similar to this one: https://stackoverflow.com/a/10485624/3481183