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.

Achromatize

Have you tried putting brackets around currentItems.FirstOrDefault(db=>db.Key == da.Id) So you get (currentItems.FirstOrDefault(db=>db.Key == da.Id)).Value? Without the brackets, its trying to get the value of the Dictionary, instead of getting the value of a single item in the Dictionary. – Beer

@Bob I'm sorry, but you're wrong. FirstOrDefault returns a element of the dictionary, and then the .Value is adressed directly on a specific item. (I also tested with bracket, but it doesn't change anything). With .Value on the dictionary, it will not even compile. – Achromatize

Dealing with the case to move or copy data from database to another database. You don't need to use EF because you don't have to deal with business logic much, no lazy loading... ADO.NET with BulkCopy with be the best case for you and save tons of your time – Blockhouse

@CuongLe Thank you for your response. But if I'm using this, it has a lot of reasons: First: The import only one part of software, which uses EF Second: I didn't show it here because it wasn't the point, but I've a lot of process and normalization to do, and for this, EF is great help to me. Third, like I said, database schema are very different(in name and in structure), some things have to be stored when generating elements. – Achromatize

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.

Benadryl

I see what you mean, but there isn't any single structured element which is understood by EF? I was thinking that if methods like contains can works, and if it has methods to works with dictionaries(ToDictionary), it should works with simple KeyValuePair element. – Achromatize