EntityFramework:使用两个不同上下文中的条件检索数据 [英] EntityFramework: Retrieve data with a condition on two different context

问题描述

我正在两个不同数据库(上下文不相同)之间导入数据.

所以我有两个不同的背景.目的是将上下文A的一些数据导入到上下文B.

上下文B中的数据从不直接编辑，它们仅是从上下文A中导入的.在上下文B中，我已经复制了从中导入数据的ID.

现在，我正在尝试检索不在上下文B中或具有较新版本的所有数据的列表.

两个表中都有一个ModifiedAt"字段，让我知道该字段是否已被修改.

这是我当前的代码:

//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.

第一部分(我检查上下文B是否具有元素)起作用，而第二部分则出现此异常:

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

但是要在Id和修改时间之间建立这种联系，我做起来不那么简单(一开始，我从其他上下文中获得了POCO类，我还尝试了匿名类型，结果相同)

我想念什么?

编辑1

我也尝试了完全相同的结果: contextA.Dossiers.Where(da =>！currentItems.Keys.Contains(da.Id)|| currentItems.Any(db => db.Key == da.Id&& db.Value

编辑2

我尝试了lambda，但是在这里它不喜欢同时使用两个上下文:

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});

->

指定的LINQ表达式包含对以下查询的引用: 与不同的上下文相关联.

解决方案

据我所知您没有丢失任何东西.

您不能创建使用其他上下文或内存引用对象中的任何内容的查询.这两件事都不能翻译成SQL.只能使用简单的值类型，因为它们可以转换为SQL参数.

您正在使用词典，该词典是键-值对的集合，并且这些kvp不能转换为SQL.另外，您的简单POCO类是.net对象，无法使用.

我知道的唯一例外是.Contains()方法与某个值类型的集合/列表结合使用，可以在查询中进行翻译.例如:

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

除了处理基于记录的内容外，我看不到单一查询解决方案.

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.

解决方案

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.

这篇关于EntityFramework:使用两个不同上下文中的条件检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！