Linq: Expression not supported: a => new AnonymousType because expression cannot be translated as a field path

Question

I am trying to write a linq query against the Mongo DB C# driver in order to pull back documents from a collection where they do not have a corresponding related record in another collection. A is an Account document, R is a Reference document which have different schema's but basically are related by data in certain fields.

I have come up with the query below in order to perform this request:

var result = (from a in _accountCollection.AsQueryable()
                              join r in _externalReferenceCollection.AsQueryable()
                              on new { Id = a.Id, CollectionName = "account", Key = "cimAccountId" } equals new { Id = r.TargetId, CollectionName = r.CollectionName, Key = r.Key } into ar
                              where !ar.Any()
                              select a).Take(batchSize);

However, despite this compiling with no errors when I run this expression I get the following error:

Expression not supported: a => new <>f__AnonymousType1`3(Id = a.Id, CollectionName = "account", Key = "cimAccountId") because expression cannot be translated as a field path.

I do not understand what Linq is trying to do here, since it seems to take issue between the anon type used in the join between collections and the requested return type in the select not being the same.

Can anyone please tell me what is wrong with the above?

Answer 1

I think you can change your linq to this:

    var result = from a in accounts
        join r in externalReferences on a.Id equals r.TargetId into ar
        where !ar.Any(r => r.Key == "cimAccountId" && r.CollectionName == "account")
        select a;

I verified it on some simple data:

    var accounts = new List<Account>
    {
        new Account {Id = 1},
        new Account {Id = 2},
        new Account {Id = 3},
    };
    var externalReferences = new List<ExternalReference>
    {
        new ExternalReference {TargetId = 1, CollectionName = "account", Key = "cimAccountId"},
        new ExternalReference {TargetId = 1, CollectionName = "account_Invalid", Key = "cimAccountId_Invalid"},
        new ExternalReference {TargetId = 2, CollectionName = "account_Invalid", Key = "cimAccountId_Invalid"},
        new ExternalReference {TargetId = 3, CollectionName = "account", Key = "cimAccountId"},
    };

The result is account with Id=2, that is, the only one without a record with collectionName = "account" and key="cimAccountId" in external reference collection.