2

I am trying to achieve a query which includes a subquery which itself includes grouping.

I based my code from answers to this question

The purpose of the code is to perform a simple de-duplication of the 'person' table based on the email address and return the latest person row.

var innerQuery = (from p in db.Person
                              join r in db.Registration on p equals r.Person
                              join e in db.EventDetail on r.EventDetail equals e
                              where e.Client.ClientID == clientID
                              group p by p.Email into g
                              select g.Max(p => p.PersonID));

var query = (from p2 in db.Person where innerQuery.Contains(p2.PersonID) select p2);

When the query is attempted to execute, I get the following error message:

LINQ to Entities does not recognize the method 'Boolean Contains[Int32](System.Linq.IQueryable`1[System.Int32], Int32)' method, and this method cannot be translated into a store expression.

I have tested the innerquery and it just returns a list of ints as expected, but the query fails with the above message.

Any help greatly appreciated.

Community
  • 1
  • 1
ptutt
  • 1,338
  • 3
  • 18
  • 35

1 Answers1

2

Isn't query just a join?

var query = from p2 in db.Person
            join iq in innerQuery on p2.PersonID equals iq
            select p2;

I'm not sure about = iq part but I don't usually use that syntax sorry - in the other form it would be

.Join(innerQuery, p2 => p2.PersonId, iq => iq, (p2, iq) => p2);

for the join and the select.

Rup
  • 33,765
  • 9
  • 83
  • 112
  • Works if adjust to: from p2 in db.Person join iq in innerQuery on p2.PersonID equals iq select p2 Thanks. – ptutt Jul 26 '10 at 08:52