Compose queries across Entity Data Models

Question

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?

Answer 1

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:

1. 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.
2. 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.
3. Define DB View or Stored procedure containing your query (or core of your query) and map it in one model to new entity.
4. 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.
5. 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).
6. 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).

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

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

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