EntityFramework: Retrieve data with a condition on two different context

cross-database datacontext entity-framework linq sql


Data is being imported between two databases (which have not the same context).

Thus, I have two distinct contexts. The objective is to import certain information from context A to context B.

Data in the context B are only imported from the context A; they are never directly modified there. I copied the ID from where it was imported into context B.

I'm now attempting to extract a list of all data that aren't in context B or have a newer version.

In the two tables, I have a ModifiedAt column that lets me know if a field has been amended.

My current code is as follows:

//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 section—where I determine if context B has the element or not—works; but, with the second part, I encountered the following exception:

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

However, there is nothing more I can do to make this connection 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 overlooking?

Edit 1

The same thing happened when I too tried it: contextA.Dossiers. !currentItems where(da=>. Keys. CurrentItems || Contains(da.Id). Anything (db=>db.Key == da.Id && db.Value

Edit 2

Lambda is what I attempted, however it doesn't like to play with two contexts at once here:

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.

8/16/2012 12:41:34 PM

Accepted Answer

I don't think you're missing anything, to my knowledge.

A query that employs elements from another context or any in-memory reference object cannot be created. Neither of these can be converted to SQL. Since simple value types can be converted into a SQL parameter, only those may be utilized.

A dictionary that you are utilizing is made up of key-value pairs that cannot be converted into SQL. Additionally, your straightforward POCO class is a.net object, which is useless.

The.Contains() function in conjunction with a collection/list of some value type, which may be translated in a query, is the sole known exception. For instance:

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

I don't see a single-query solution other than managing things per-record.

8/16/2012 12:37:31 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow