Exception when using Contains() in LINQ Query

Question

I am having a problem trying to get the following to work. I have tried it 2 different ways, both compile but both throw exceptions at runtime.

Solution A throws

NotSupportedException "LINQ to Entities does not recognize the method 'MyDB.MemberClass GetMemberClass(MyDB.Member, System.DateTime)' method, and this method cannot be translated into a store expression."

Solution B throws

NotSupportedException "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."

Here's my code:

    public delegate string GetClassShortName(Member m, DateTime d);

    private void UserControl_Initialized(object sender, EventArgs e)
    {
        //Solution A
        var members = from m in dbContext.Members
                      where new[] { "A", "P", "S", "J" }.Contains(MemberHelper.GetMemberClass(m, DateTime.Now).ShortName)
                      orderby m.Line
                      select m;

        //Solution B
        GetClassShortName gcsn = (n,d) => (MemberHelper.GetMemberClass(n, d).ShortName);
        var members = dbContext.Members.Where(m => new[] { "A", "P", "S", "J" }.Contains(gcsn(m, DateTime.Now)));

        foreach (Member m in members)
        {
            ...

I am trying to get the members who have a 'class' that is within a set of values. (A, P, S, J). A member's class is dependant on what date you want it for as it changes over time. Therefor the class is stored in a different table and I use the MemberHelper.GetMemberClass function to get the class for the specified date. (In this case, now)

New Solution

I gave up on trying to do all the work with LINQ and changed my code as follows. There's probably a much more elegant way to do this, but it does work.

        var data = from mj in this.dbContext.MemberJournals
                      where mj.Effective <= date
                      orderby mj.Member.Line, mj.Effective
                      select mj;

        foreach (MemberJournal mj in data)
        {
            if (mj.ClassID == MemberHelper.GetMemberClass(mj.Member, date).ID)
            {
                if (new[] { "A", "P", "J", "S" }.Contains(mj.MemberClass.ShortName.Trim()))
                {
                    //Perform my Task
                    ...
                }
            }
        }

im not sure what your attempting to accomplish. entity framework is trying to convert something to sql. — Daniel A. White
– Daniel A. White, Commented Apr 27, 2012 at 23:27
I added explanation of what I am trying to get at the end of my post. — Nick Sperling
– Nick Sperling, Commented Apr 27, 2012 at 23:33

Kirk Woll · Accepted Answer · 2012-04-27 23:27:24Z

3

You can't invoke arbitrary C# methods within code that is intended to be translated into SQL. Code that can be translated into SQL is a very limited subset of otherwise valid C#.

answered Apr 27, 2012 at 23:27

Kirk Woll

77.8k23 gold badges190 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Sperling Over a year ago

Sounds like I may need to completely re-think this one. Any suggestions to get me pointed in the right direction?

Kirk Woll Over a year ago

Well, start by inlining your methods. Arbitrary methods are just in and of themselves going to invalidate SQL translation. However, it's plausible that the body of those methods are going to be valid (though in your case I suspect that's unlikely). So eliminate gcsn and MemberHelper.GetMemberClass and replace them with the contents of those functions. (feel free to modify your question to facilitate communicating your changes) Then we can see what's left and what may or may not be translatable to SQL.

Collectives™ on Stack Overflow

Exception when using Contains() in LINQ Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related