在实体数据模型中撰写查询

本文介绍了在实体数据模型中撰写查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的场景：$ b $我有一个使用EF进行数据访问的框架（EDM 1）我有一个使用框架服务的客户端应用程序，并且还使用EF进行数据访问（EDM2）

在某些情况下，我需要撰写查询并加入跨越2个EDM的实体。

有没有办法没有从第一个EDM获取内存中的数据，然后从第二个EDM的实体应用额外的谓词/连接在内存中？

我希望我正在阐述这个正确的方式

编辑 @Ladislav Mrnka：第一个EDM是可重用框架的数据访问层。 将EF生成的实体与此EDM耦合到消费客户端的没有任何意义如果我这样做，它会失败API的可重用性，我必须携带每次我想重新部署框架时，都会增加（客户端的EF元数据和DB表）。 此外，这将使管理设计师中的模型变得笨拙。

我目前正在使用你提到的第7项作为解决方案，而且性能是糟糕的由于事实上，我必须使用 EDM1从框架中返回更多的数据（即实体），然后根据基于谓词/条件的$ b过滤掉基于$ b的值$ b属性从实体在第二个EDM。最终结果是巨大的性能下降和不愉快的DBA。

因此，我最终推出了将实体检索到SPROC所需的逻辑，其中我可以访问两个EDM使用的表，并应用所需的谓词，并将整个查询运行在数据库中，而不是将数据存入内存，然后过滤出不必要的数据。下一个是我不能使用LINQ

您提到的项目8听起来很有趣，但从听起来我觉得您在设计时得到强力打字，或者做您？ 您可以将代码示例上传到某个地方，以便我可以尝试吗？

解决方案

重要编辑

使用两个 ObjectContext 类型实现此目的没有任何构建。您的查询必须始终针对单个 ObjectContext 执行。

可能是最好的方法： strong>这很有趣，足以让我自己尝试一下。我从非常简单的想法开始。两个EDMX文件（与POCO T4发生器一起使用），每个文件包含单个实体。我从第二个连接字符串获取元数据描述并将其添加到第一个连接字符串。我直接使用了 ObjectContext 和 ObjectSet 。通过这样做，我能够从单个 ObjectContext 实例查询和修改两个实体。我也尝试从两个模型创建查询连接实体，并且它工作。 这显然只有当两个EDMX映射到同一个数据库（相同的数据库连接字符串）时才起作用。

重要的部分是连接字符串：

< configuration> < connectionStrings> < add name =TestEntitiesconnectionString =metadata = res：//*/FirstModel.csdl | res：//*/FirstModel.ssdl | res：//*/FirstModel.msl | res：/ /*/SecondModel.csdl|res://*/SecondModel.ssdl|res://*/SecondModel.msl;provider=System.Data.SqlClient;provider connection string =& quot; Data Source =;初始目录= Test; Integrated Security = True; MultipleActiveResultSets = True& quot; providerName =System.Data.EntityClient/> < / connectionStrings> < / configuration>

此连接字符串包含两个型号的元数据 - FirstModel.edmx和SecondModel.edmx。

另一个问题是强制EF使用这两个文件的映射。每个EDMX文件必须为SSDL和CSDL定义唯一容器。 ObjectContext 提供名为 DefaultContainerName 的属性。该属性可以直接设置或通过一些构造函数重载。设置此属性后，将 ObjectContext 实例绑定到单个EDMX - 对于此方案，您不能设置此属性。省略 DefaultContainerName 可能会有一些后果，因为某些功能和声明可以停止工作（您将收到运行时错误）。除非您想使用某些高级功能，否则您不应该遇到POCO的问题。如果您使用的是实体对象（重型EF实体），那么您将很有可能已经有了这个实例。所有使用定义为字符串的实体集的方法都取决于容器，因此我建议仅在必要时使用此类配置 - 对于交叉模型查询。

最后一个问题是生成实体和强类型派生 ObjectContext 。要删除的方法是修改T4模板，以便一个模板从多个EDMX文件中读取数据并为所有这些文件生成上下文结束实体 - 我已经在我的项目中执行此操作，它的工作原理默认T4实现不符合前面段落中描述的所需方法Derived ObjectContext 从默认T4实现依赖于单个EDMX和实体容器。

---

部分已经在之前的编辑之前写过。

我正在离开其余的信息，只因为其中一些可以在其他场景中使用，使用多个数据库。

ORM就像实体框架一样，在对象世界和数据库世界之间进行映射。在EF中，对象世界由 CSDL 描述，数据库世界描述为 SSDL ，并将它们之间的映射描述为 MSL （全部只是具有众所周知的架构的XML）。在设计时，这些描述是存储在EDMX文件中的模型的一部分。在编译期间，这些描述是从EDMX中提取的，默认情况下将其作为资源文件添加到编译程序集中。

创建 ObjectContext 它接收连接字符串，其中包含对CSDL，SSDL和MSL资源文件的引用。 SSDL或MSL不指定从其他文件添加信息的include元素。 CSDL提供使用元素，这将允许您重用现有的映射，但此功能是不由设计师支持ConnectionString用于初始化 EntityConnection 实例，该实例又用于初始化ObjectContext的 MetadataWorkspace （运行时映射信息）。另外 ObjectContext 不提供将多个上下文嵌套到hiearchy中的任何功能。 连接字符串不能包含这些文件的多个实例的引用。 编辑：可以。我刚刚测试了在 ObjectContext 的实例上运行Linq或ESQL查询时，请参阅初始段落。

usese MSL将您的实体或POCO类（由CSDL定义）映射到DB查询（由数据库表的SSDL描述定义）。如果没有这个信息，它将无法正常工作（如果它存储在单独的EDMX中，则不能使用该信息）。

那么如何解决这个问题问题？有几种方法：

始终考虑：将映射合并到一个文件中（如果使用多个文件相同的数据库）。这是使用EF的方式，正如您所提到的，您正在查询相同的数据库，因此不需要两个EF模型。

第二个模型中的重复实体描述。如果您使用EF4和POCO，您可以将来自多个模型的相同描述映射到一个POCO类定义中。我不喜欢这个解决方案，但有时它可以帮助。

定义包含查询（或查询核心）的数据库视图或存储过程，并将其映射到一个模型中为新实体。

使用 DefiningQuery （如果您使用Update from数据库功能），并将其映射到新实体。 DefiningQuery是在SSDL中定义的自定义SQL查询，而不是表或视图描述。

使用功能与定制的CommandText指定DB查询。它与使用DefiningQuery类似，并且具有相同的限制。您必须手动（在EDMX中）将函数的结果映射到新的复杂类型（与映射到新实体的DefiningQuery的另一个区别）。

定义查询结果的新类型类型的属性必须与查询中返回的列具有相同的名称），并使用ObjectContext的 ExecuteStoreQuery （仅在EF4中）。

将查询分成两部分，每个部分分别在自己的上下文中执行，并使用linq-to-objects获取结果。我不喜欢这个解决方案。

这一个只是高层次的想法 - 我没有尝试，我不知道它的工作原理。如上所述，运行时映射取决于 MetadataWorkspace 实例的内容，该实例从 EntityConnection 。 EntityConnection 还提供了直接接收 MetadataWorkspace 实例的构造函数。因此一般来说，如果可以从多个EDMX填充 MetadataWorkspace ，则不需要多个ObjectContext实例，但是您的映射仍将分为两个EDMX。这将有助于您在两个映射文件之上编写自定义的Linq查询）。 编辑：应该是可能的，因为它正是EF在连接字符串中定义多个映射时正在做的。

使用< a href =blogs.msdn/b/adonet/archive/2008/11/24/working-with-large-models-in-entity-framework-part-2.aspx =nofollow CSDL使用功能将模型分解成多个重用的零件。

Is there a way to compose queries from 2 different entity models if the models are hitting the same underlying database.

The scenario I have is this: I have a framework that uses EF for data access.(EDM 1) I have a client application that uses services of the framework and also uses EF for it's own data access.(EDM2)

There are situations where I need to compose queries and join on entities that span the 2 EDMs.

Is there a way to do this without getting the data in memory from the first EDM and then apply additional predicates/joins in memory from the entities of the 2nd EDM?

I hope I'm articulating this the right way

EDIT @Ladislav Mrnka: The first EDM is the data access layer for a reusable framework. It doesn't make sense to couple the EF generated entities from this EDM with those of the consuming client It defeats reusabilty of the API if I did this and I'd have to carry around additonal bloat (EF metadata and DB tables of the client) everytime I wanted to redeploy the framework. Also this would make managing the model in the designer unwieldy.

I'm currently using what you mention n item 7 as the solutuon and the performance is abysmal due to the fact that I have to end up returning more data(i.e. entities) than needed from framework using EDM1 and then filter out the ones not needed based on predicates/conditions based on value of properties from entities in the second EDM. End result is a huge performance degradation and an unhappy DBA.

For this reason I ended up pushing the logic needed to retrieve the entities to a SPROC in which I can access the tables that both EDMs use and apply the predicates needed and have the entire query run in the DB as opposed to bringing the data in memory and then filter out unnecessary ones.Downside is that I can't use LINQ

Item 8 that you mention sounds interesting but from what it sounds like I doubt that you get strong typing at design time, or do you? Can you upload your code sample someplace so that I can try it out?

解决方案

Important edit

There is no build in support for achieving this with two ObjectContext types. Your query must always be executed against single ObjectContext.

Probably the best way to go: This was interesting enough for me to try it myself. I started with very simple idea. Two EDMX files (used with POCO T4 generators), each containing single entity. I take metadata description from second connection string and added it to first connection string. I used ObjectContext and ObjectSet directly. By doing this I was able to query and modify both entities from single ObjectContext instance. I also tryed to create query joining entities from both models and it worked. This obviously works only if both EDMX map to the same database (same db connection string).

The important part is connections string:

<configuration> <connectionStrings> <add name="TestEntities" connectionString="metadata=res://*/FirstModel.csdl|res://*/FirstModel.ssdl|res://*/FirstModel.msl|res://*/SecondModel.csdl|res://*/SecondModel.ssdl|res://*/SecondModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /> </connectionStrings> </configuration>

This connection string contains metadata from two models - FirstModel.edmx and SecondModel.edmx.

Another problem is to force EF to use mapping from both these files. Each EDMX file must define unique container for SSDL and CSDL. ObjectContext offers property called DefaultContainerName. This property can be set directly or through some constructor overloads. Once you set this property you bind your ObjectContext instance to single EDMX - for this scenario you must not set this property. Omitting DefaultContainerName can have some consequences because some features and declarations can stop working (you will get runtime errors). You should not have problems with POCO unless you want to use some advanced features. You will most probably have proplems if you are using Entity objects (heavy EF entities. All methods using entity sets defined as strings are dependent on container. Due to this I suggest using such configuration only when necessary - for cross models queries.

Last problem is generating entities and "strongly typed" derived ObjectContext. The way to go is modify T4 template so that one template reads data from multiple EDMX files and generates context end entities for all of them - I already doing this in my project and it works. Default T4 implementation doesn't follow needed approach described in previous paragraph. Derived ObjectContext from default T4 implementation is dependent on single EDMX and entity container.

This part has been written before previous edit.

I'm leaving the rest of information just because some of them can be useful in other scenarios including work with multiple databases.

ORM like entity framework operates on top of mapping between object world and database world. In EF the object world is described by CSDL, database world is described as SSDL and mapping between them is described as MSL (all are just XML with well known schema). At design time these descriptions are part of model stored in EDMX file. During compilation these descriptions are extracted from EDMX and by default included as resource files to compiled assembly.

When you create instance of ObjectContext it receives connections string which contains reference to CSDL, SSDL and MSL resource files. SSDL or MSL do not specify include element to add information from other files. CSDL offers Using element which will allow you reusing existing mapping but this feature is not supported by designer. ConnectionString is used to initialize EntityConnection instance which is in turn used to initialize ObjectContext's MetadataWorkspace (runtime mapping information). Also ObjectContext doesn't provide any functionality of nesting multiple contexts into hiearchy. Connection string can't contain reference to multiple instances of these files. Edit: It can. I just tested it. See the initial paragraphs.

When you run Linq or ESQL query on the instance of ObjectContext it usese MSL to map your entities or POCO classes (defined by CSDL) into DB query (defined by SSDL description of database tables). If it doesn't have this information it will not work (and it can't have that information if it is stored in separate EDMX).

So how to solve this problem? There are several ways:

Always consider: Merge your mapping into one file (if multiple files are used for same database). That is supposed way to use EF and as you mentioned you are querying same DB so two EF models are not needed.

Duplicate entity description in second model. If you use EF4 and POCO you can map same descriptions from multiple models into one POCO class definition. I don't like this solution but sometimes it can help.

Define DB View or Stored procedure containing your query (or core of your query) and map it in one model to new entity.

Use DefiningQuery in one model (you will probably need 3rd one if you use Update from database feature) and map it to new entity. DefiningQuery is custom SQL query defined in SSDL instead of table or view description.

Use Function with custom CommandText specifying DB query. It is similar to using DefiningQuery and it has the same limitation. You must manually (in EDMX) map the result of the function into new complex type (another difference to DefiningQuery which is mapped to new entity).

Define new type for result of the query (properties of the type must have same names as returned columns in query) and use ObjectContext's ExecuteStoreQuery (only in EF4).

Divide query into two parts each executed separately on its own context and use linq-to-objects to get result. I don't like this solution.

This one is only high level idea - I didn't try it and I don't know if it works. As described above runtime mapping is dependent on the content of MetadataWorkspace instance which is filled from EntityConnection. EntityConnection also provides constructor which receives MetadataWorkspace instance directly. So generally if it would be possible to fill MetadataWorkspace from multiple EDMX you will not need multiple ObjectContext instances but your mapping would be still separated into two EDMXs. This would hopefully allow you writing custom Linq queries on top of two mapping files). Edit: It should be possible because it is exactly what EF is doing if you define multiple mappings in connection string.

Use CSDL Using feature for breaking the model into multiple reused parts.