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?

you can enumerate first (which is usually a bad idea), or explain what you wanna do in IsWord. With that, someone may be able to help you (or explain why it's impossible), without... — Raphaël Althaus, Jun 13 '14 at 15:27

Ben Robinson · Accepted Answer · 2014-06-13T15:30:14.733

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.

edited Jun 13 '14 at 15:30

answered Jun 13 '14 at 15:20

Ben Robinson

21,601
5
62
79

Thanks for the attempt, but you didn't read the explanation I offered in the post. At the present time `IsWord` doesn't do anything, but it if I can get a workable solution, it will check `trindex.word` to see that it is a `word` that matches my specs. So, I need `IsWord`. – seebiscuit Jun 13 '14 at 15:25
Then you enter the scary world of creating a dynamic method using expression trees. See the link I posted. Then you enter the scary world of creating a dynamic method using expression trees. See the link I posted. Also if the record set is not large then bringing the whole lot into memory and calling your method as per Believe204's answer is workable. However the bigger the record set gets the less efficient it is. – Ben Robinson Jun 13 '14 at 15:33
I'm afraid I don't know enough about expression trees, to give you advice on how to turn your method into a dynamic linq method using expressions. – Ben Robinson Jun 13 '14 at 15:54
Nice response. Even though @Believe2014 provided a nice working answer, you really answered my question, i.e., forget the custom method... – seebiscuit Jun 13 '14 at 15:55
@Seabiscuit it may be doable using expressions, or to at least some equivalent. But you can't pass straightforward c# methods into and linq query that uses EF or any other linq provider that goes off and translates expressions into something like SQL. It simply doesn't know whow to convert your c# into SQL. – Ben Robinson Jun 13 '14 at 16:00

score 1 · Answer 2 · edited May 23 '17 at 10:33

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

I've been considering processing the list in the collection's view. — seebiscuit, Jun 13 '14 at 15:52
Either the SqlServer has to do the record filtering or the application server has. If SqlServer is chosen to do the task, it will save network latency and bandwidth. But it will make Sql a bottleneck if everything is done in that manner. Finding a good balance is the key. — Believe2014, Jun 13 '14 at 15:54

Custom Method in LINQ Query

The Code

The Problem

Any ideas?

2 Answers2

Linked