EntityFramework: Retrieve data with a condition on two different context

Question

I'm importing data between two different database(which have not the same context).

So I've two different context. The goal is to import some data of the context A to the context B.

Data in the context B are never directly edited, they are only imported from the context A. In the context B, I've in copy the ID from which it has been imported.

Now I'm trying to retrieve a list all data which aren't in the context B or have a more recent version.

I've a ModifiedAt" field in the two table, allowing me to know if the field has been modified.

Here is my current code:

//Here I get all my current data in the context B with their modification time
Dictionary<int,DateTime> currentItems = contextB.Dossiers.ToDictionary(d=>d.MatchingReferenceId, d=>d.ModifiedAt);

//And here the pain starts:
contextA.Dossiers.Where(da=> !currentItems.Keys.Contains(da.Id) || currentItems.FirstOrDefault(db=>db.Key == da.Id).Value <da.ModifiedAt)//And I'm looping on it with a foreach.

The first part(where I check if the context B has the element or not) works, with the second part, I got this exception:

Unable to process the type 'System.Collections.Generic.KeyValuePair`2[]', because it has no known mapping to the value layer.

But I can't do more simple to have this link between the Id and the modification time(at the start,I was having a POCO class from the other context, I also tried with anonymous type, same result)

What am I missing?

Edit 1

I also tried this with the exact same result: contextA.Dossiers.Where(da=> !currentItems.Keys.Contains(da.Id) || currentItems.Any(db=>db.Key == da.Id && db.Value

Edit 2

I tried lambda, but here it doesn't likes to play with two context at the same time:

var myList = (from db in contextB.Dossiers
                      let dstId = newContext.Dossiers.Select(d=>d.MatchingReferenceId)
                      from da in contextA.Dossiers
                      where !db.Contains(dSource.ID)|| (db.MatchingReferenceId == da.Id && db.ModifiedAt< da.ModifiedAt) 
                      select new {NewId =db.Id, OldId = da.Id});

-->

The specified LINQ expression contains references to queries that are associated with different contexts.

Answer 1

As far as I can tell you're not missing anything.

You cannot create a query which uses things from another context, or any in memory reference object. Both these things cannot be translated to SQL. Only simple value types can be used, since they can be translated into a SQL parameter.

You are using a dictionary, which is a collection of key-value-pairs, and these kvp's cannot be translated into SQL. Also your simple POCO class is a .net object, which cannot be used.

The only exception I know is the .Contains() method in combination with a collection/list of some value type, which can be translated in a query. For example:

List<int> someIds = ...
var result = context.Data.Where(d => someIds.Contains(d.Id)).ToList();

Aside from handling things on a per-record base, I do not see a single-query-solution.