Compose queries across Entity Data Models

by

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.

More Related Contents:

  1. What’s the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()?
  2. Disable lazy loading by default in Entity Framework 4
  3. ADO.NET DbContext Generator vs. ADO.NET Poco Entity Generator (ObjectContext)
  4. How to include a child object’s child object in Entity Framework 5
  5. Entity Framework 4 / POCO – Where to start? [closed]
  6. Asp.Net Web API Error: The ‘ObjectContent`1’ type failed to serialize the response body for content type ‘application/xml; charset=utf-8’
  7. The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid
  8. Unique keys in Entity Framework 4
  9. Entity Framework 4 selective lazy loading properties
  10. Upgrade EF 4 EDMX to EF 6
  11. Querying objects after AddObject before SaveChanges?
  12. EF Migrations: Rollback last applied migration?
  13. EF Code First giving me error Cannot insert explicit value for identity column in table ‘People’ when IDENTITY_INSERT is set to OFF. [duplicate]
  14. Using Entity Framework with an SQL Compact Private Installation
  15. Map two different entities to the same table?
  16. How to use Entity Framework to map results of a stored procedure to entity with differently named parameters
  17. AutomaticMigrationsEnabled false or true?
  18. Changing Inherited Types in Entity Framework
  19. ORM Entities vs. Domain Entities under Entity Framework 6.0
  20. LEFT JOIN in LINQ to entities?
  21. Entity Framework validation with partial updates
  22. Does Entity Framework 4 Code First have support for identity generators like NHibernate?
  23. NOT using repository pattern, use the ORM as is (EF)
  24. EF Query With Conditional Include
  25. Entity Framework CTP 4 – Code First Custom Database Initializer
  26. Set decimal(16, 3) for a column in Code First Approach in EF4.3 [duplicate]
  27. Entity Framework and calling context.dispose()
  28. Error 6002: The table/view does not have a primary key defined
  29. Entity Framework Core 2.0 – Run migrations step by step
  30. EF4 LINQ Ordering Parent and all child collections with Eager Loading (.Include())

Leave a Comment