Using EF and devart for both oracle and sql server?

joeu · Post by **joeu** » Wed 18 Aug 2010 17:25

Hi,

We are seriously considering purchasing your dotConnect for oracle.
I have a task to demonstrate to the business how the same code can be used for both oracle and sql server.

1. Have you got an example / instructions about how i would do this?

Currently i have created models for both sql and oracle but that means i have 2 of every type of object. (or i don’t create the code and have nothing on one side, including context)

2. Do i need the code first implementation to do this? (CTP4)
3. Does it work with code first and db first?

4. Also on the same theme, can you explain the use/differences between:
edmx
ssdl
csdl
msl

Thanks so much for your help in advance

Joe

AndreyR · Post by **AndreyR** » Thu 19 Aug 2010 10:06

1. Yes, you can download Entity Framework Query Samples, we have made a sample illustrating the work with Oracle, MSSQL, MySQL, PostgreSQL and SQLite.
It is implemented having one CSDL, one MSL and a number of SSDL files.
2. There is no need in Code First functionality to implement this scenario.
3. It works with both code first and DB first approaches.
4. .edmx file is an XML file which encapsulates SSDL, CSDL, MSL, and Designer parts of the model.
SSDL is a part that contains information about storage (types of db columns, stored procedures, parameters, and so forth).
CSDL is a part that contains the conceptual model - classes, properties, methods.
MSL is a mapping layer that links the previous two.
More information on this topic is available here in MSDN

joeu · Post by **joeu** » Thu 19 Aug 2010 12:43

1. Can you explain how you would create the SSDL, CSDL, MSL?

2. if this is cut up the edmx file, is there another way?

3. Can you explain how you would do this in a code first approach?

Thanks again

AndreyR · Post by **AndreyR** » Thu 19 Aug 2010 14:50

1. The most simple way is to create an .edmx file from database, and then divide it into three files. You can write these files manually also.
In your case I recommend you to create two .edmx files - for Oracle and SQL Server - and then take one .csdl, one .msl and two .ssdl files. Then simply change the connection strings to point to the same .csdl, msl and different .ssdl.
One note: don't forget to change the MetadataArtifactProcessing property of the model to CopyToOutptutDirectory.
2. When MetadataArtifactProcessing is set to CopyToOutptutDirectory, metadata files (.ssdl, .msl, .csdl) are copied to Bin folder of your application.
3. The code first can be used like it is described here.
The only thing is the problem with the DatabaseExists method, the workaround is described in this post.

joeu · Post by **joeu** » Thu 26 Aug 2010 17:24

so i created the csdl msll and ssdl files for oracle using your entity developer
i created the sql files using vs2010.

i have used the orace files + the ssdl for sql and i get an error when trying to connect to the sql db.

can i mix and match vs2010 files with the entity developer files?
or do i need to create the sql files using dotconnect for sql? (i'd rather not)

any ideas?

here is the errror:

System.Data.MappingException

{"Schema specified is not valid. Errors: \r\nOracleModel.msl(3,4) : error 2002: The EntityContainer 'OracleNorthwindefEntitiesStoreContainer' for the storage model specified as part of this MSL does not exist in MetadataWorkspace."}

at System.Data.Mapping.StorageMappingItemCollection.Init(EdmItemCollection edmCollection, StoreItemCollection storeCollection, IEnumerable`1 xmlReaders, List`1 filePaths, Boolean throwOnError)
at System.Data.Metadata.Edm.MetadataCache.StoreMetadataEntry.LoadStoreCollection(EdmItemCollection edmItemCollection, MetadataArtifactLoader loader)
at System.Data.Metadata.Edm.MetadataCache.StoreItemCollectionLoader.LoadItemCollection(StoreMetadataEntry entry)
at System.Data.Metadata.Edm.MetadataCache.LoadItemCollection[T](IItemCollectionLoader`1 itemCollectionLoader, T entry)
at System.Data.Metadata.Edm.MetadataCache.GetOrCreateStoreAndMappingItemCollections(String cacheKey, MetadataArtifactLoader loader, EdmItemCollection edmItemCollection, Object& entryToken)
at System.Data.EntityClient.EntityConnection.LoadStoreItemCollections(MetadataWorkspace workspace, DbConnection storeConnection, DbProviderFactory factory, DbConnectionOptions connectionOptions, EdmItemCollection edmItemCollection, MetadataArtifactLoader artifactLoader)
at System.Data.EntityClient.EntityConnection.GetMetadataWorkspace(Boolean initializeAllCollections)
at System.Data.EntityClient.EntityConnection.InitializeMetadata(DbConnection newConnection, DbConnection originalConnection, Boolean closeOriginalConnectionOnFailure)
at System.Data.EntityClient.EntityConnection.Open()
at System.Data.Objects.ObjectContext.EnsureConnection()
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
at OracleEntityFrameworkSpike.Program.Spike3() in D:\Code\Spikes\OracleEntityFrameworkSpike\OracleEntityFrameworkSpike\OracleEntityFrameworkSpike\Program.cs:line 33
at OracleEntityFrameworkSpike.Program.Main(String[] args) in D:\Code\Spikes\OracleEntityFrameworkSpike\OracleEntityFrameworkSpike\OracleEntityFrameworkSpike\Program.cs:line 17
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Shalex · Post by **Shalex** » Mon 30 Aug 2010 15:20

1. Have you followed all recommendations from the latest post of AndreyR?

2. It seems like the path to metadata files in your connection string is set not correctly. Please check it, try changing the relative path to metadata files to the absolute path. If this doesn't help, please post here your connection string to Oracle.

Shalex · Post by **Shalex** » Tue 31 Aug 2010 08:24

Please make sure that the value of the StorageEntityContainer attribute (the EntityContainerMapping tag) from MSL and the value of the Name attribute (the EntityContainer tag) from SSDL are the same. If not, set SSDL:EntityContainer>Name to the value of MSL:EntityContainerMapping>StorageEntityContainer.

If you encounter any mapping issues when implementing further steps of your approach, please refer to http://www.devart.com/dotconnect/oracle ... pping.html.

joeu · Post by **joeu** » Tue 31 Aug 2010 15:24

After changing the EntityContainer Name= I now get this error:
I did create the sql server ssdl using VS2010 and the oracle ssdl, msl and csdl using the devart-entity-developer. Should this work?

{"Schema specified is not valid. Errors: \r\nOracleModel.msl(7,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Int64[Nullable=False,DefaultValue=]' of member 'Categoryid' in type 'Northwind.Category' is not compatible with 'SqlServer.int[Nullable=False,DefaultValue=,StoreGeneratedPattern=Identity]' of member 'CategoryID' in type 'EntitiesSql.Store.Categories'.\r\nOracleModel.msl(34,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Int64[Nullable=False,DefaultValue=]' of member 'Employeeid' in type 'Northwind.Employee' is not compatible with 'SqlServer.int[Nullable=False,DefaultValue=,StoreGeneratedPattern=Identity]' of member 'EmployeeID' in type 'EntitiesSql.Store.Employees'.\r\nOracleModel.msl(57,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Int64[Nullable=False,DefaultValue=]' of member 'Employeeid' in type 'Northwind.Employeesterritory' is not compatible with 'SqlServer.int[Nullable=False,DefaultValue=]' of member 'EmployeeID' in type 'EntitiesSql.Store.EmployeesTerritories'.\r\nOracleModel.msl(58,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Int64[Nullable=False,DefaultValue=]' of member 'Territoryid' in type 'Northwind.Employeesterritory' is not compatible with 'SqlServer.int[Nullable=False,DefaultValue=]' of member 'TerritoryID' in type 'EntitiesSql.Store.EmployeesTerritories'."}

AndreyR · Post by **AndreyR** » Wed 01 Sep 2010 10:33

The problem seems to be associated with some minor type mismatches.
Take a close look at the members that are mentioned in this error message, and change types manually to the corresponding ones using XML Editor or Entity Developer design time (Model.Store in Model Explorer).
After some manual tuning everything should work smoothly.
This post should help you.

vladk · Post by **vladk** » Fri 10 Sep 2010 15:18

AndreyR wrote: One note: don't forget to change the MetadataArtifactProcessing property of the model to CopyToOutptutDirectory.

Your test example shows a solution with extra ssdl files copied to output directory. This seems to be not so convenient for web apps.
Is it possible to embed this second ssdl file into assembly resources? I tried to do this using 'Embedded resources' option of file properties but it does not work.

AndreyR · Post by **AndreyR** » Mon 13 Sep 2010 13:21

Yes, you can change the Metadata Artifact Processing to EmbedInOutputAssembly.
But don't forget to change connection string accordingly (they should start with res://*/ and contain the name of the generated resource).

vladk · Post by **vladk** » Tue 14 Sep 2010 15:27

AndreyR wrote:Yes, you can change the Metadata Artifact Processing to EmbedInOutputAssembly.
But don't forget to change connection string accordingly (they should start with res://*/ and contain the name of the generated resource).

Yes , I do this in this way. I explain this in more details:
I have Model1.edmx file that is generated for SQL and embedded into assembly using standard EntityDeploy build action.
I extract ssdl and convert it into Model1Ora.ssdl. Model1.edmx remains embedded as it was by default.
Here are 2 scenarios I do:
1) I copy Model1Ora.ssdl into /bin folder and reference it like this:
metadata=res://*/Model1.csdl|~\bin\Model1Ora.ssdl|res://*/Model1.msl
Everything works fine!

2) I embed Model1Ora.ssdl using "Embedded resourse" build action and use this:
metadata=res://*/Model1.csdl|res://*/Model1Ora.ssdl|res://*/Model1.msl
In this case Model1Ora.ssdl cannot be found.

If I create 2 edmx files (one for SQL and one for Oracle) then it works with the metadata from scenario #2, but I would like to avoid keeping unnecessary data.

So I suspect that EntityDeploy build action adds Model1Ora.ssdl in some special way and it's not enough just to embed this ssdl file as a plain resource into an assembly.

That is why I'm asking DevArt - maybe you have had some experience in this and have implemented #2 successfully, haven't you?

I use VS2008, .NET 3.5

AndreyR · Post by **AndreyR** » Wed 15 Sep 2010 10:45

Yes, we have met this problem. SSDL can be added as a resource by VS with a name that does not correspond to the one added as a resource by EntityDeploy custom action.
So, the simplest way is to open the assembly in .NET Reflector or any other assembly resource viewer, find out the exact name of the resource and put it into the connection string.