Entity Framework LINQ - Subquery with Group By

Question

Hi guys,

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.

Answer 1

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.