CODEWITHSUNDEEP
c#linqentity-framework
Nick Sperling
Nick Sperling

Reputation: 43

Exception when using Contains() in LINQ Query

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
                    ...
                }
            }
        }

Upvotes: 1

Views: 809

Answers (1)

Kirk Woll
Kirk Woll

Reputation: 77616

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#.

Upvotes: 3

Related Questions