locked
Generate ssdl from edmx file for several database providers RRS feed

  • Question

  • Hi,

    I have a EF model in edmx file generated from MS SQL server database. It contains csdl, msl and ssdl content which is after build copied to the target directory.

    Now my problem is, that I would like to switch my database provider to MS SQL Compact edition for example and the only think I need is to generate somehow the ssdl specifit for MS SQL Compact edition from my edmx file. My question is how to achieve this?

    I am asking because I have some specifich changes in the edmx file like associations names different that the generated ones. I could delete my model and add new one generated from other database, but this sucks for me to do this every time I change the database somehow.

    I was looking on EdmGen.exe, but it is able only to generate either all the files needed based on database connection string or the rest files from ssdl.

    I need to generate the ssdl based on edmx or csdl and msl.

    Is there a tool to do this?

    Thanks


    zDN@
    Friday, February 11, 2011 2:27 PM

Answers

  • Hi Jackie,

    Thanks for interesting link, that helped me to think out the solution.

    Expectially part:

    In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

    As I saw, author was manually changing the SSDL file generated first for MSSQL the same way as I did to be able to work with the CE database.

    I found the solution how to let the SSDL file to be generated, so here it is:

    1. Open VS 2010, Create new edmx generated from MSSQL database(the tables should be the same as later in CE).
    2. Migrate schema from MS SQL to CE database
    3. Use EdmGen.exe to generate edmx from the CE database
    4. Create copy of your edmx generated in step 1 and replace the ssdl part with the one generated from CE
    5. Use EdmGen.exe to validate the modified edmx
    6. Use EdmGen.exe to generate SSDL, CSDL and MSL - here I was rather comparing the files for MS SQL and CE and you will see the changes are performed only in SSDL and are the same that the author needed to do.
    7. New SSDL remains to embed into our assembly and setup new connection string for CE which is not difficult and the development can run on CE
    Hope this helps, thanks Jackie for kick off


    zDN@
    Wednesday, February 16, 2011 9:04 AM

All replies

  • Hello zDN@,

     

    Welcome to the MSDN Forum and thanks for posting here.

    As you description, I think you would like to know more about EDM Generator. The following is one introducted in MSDN library.

    EdmGen.exe is a command-line tool used for working with Entity Data Models (EDMs). You can use the EdmGen.exe tool to do the following:

    The EdmGen.exe tool is installed in the .NET Framework directory. In many cases, this is located in C:\windows\Microsoft.NET\Framework\v3.5. For 64-bit systems, this is located in C:\windows\Microsoft.NET\Framework64\v3.5. You can also access the EdmGen.exe tool from the Visual Studio command prompt (Click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt).

    If you want to know more about it, please see this:

    http://msdn.microsoft.com/en-us/library/bb387165(VS.90).aspx

    I hope this can help you. If I misunderstood you, please feel free to let me know.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 15, 2011 7:30 AM
  • Hello Jackie,

    I was investigating the EdmGen.exe tool, but it didn't solve my problem.

    I have a edmx file generated from MS SQL, in which there are basics of ssdl, csdl and msl files, where ssdl part is specific for MS SQL. Let me notice, that in my edmx there are specific changes made in VS designer that I don't want too loose.

    Now the problem is, how to generate from this edmx files: ssdl, csdl and msl for MS SQL Ce database?

    Generally, csdl and msl should be the same as those for MS SQL server. The only one which is missing is ssdl :(.

    If I use EdmGen for generating new edmx model from my CE database, I will get different one than my customized model which causes different mappings for MS SQL and CE.

     


    zDN@
    Tuesday, February 15, 2011 3:30 PM
  • Hi zDN@,

     

    Thanks for your feedback.

    Sorry for hearing that. Luckily, I have found that for you. Here's a simple example of using EDM with SQLCE 3.5, database included. You need to put the pubs.sdf database in c:\temp or change the connection string in the app.config to make this work. The sample uses both EntityClient and Entity Services. It could just as easily use LINQ for Entities.  The following is bob Beauchemin's blog article.

    Please see this: http://www.sqlskills.com/BLOGS/BOBB/post/SQL-Server-Compact-Edition-newest-stuff-SQLCE-and-entities.aspx

    I hope it can help you. If you have any questions please feel free to let me know.

     

    Good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 16, 2011 2:20 AM
  • Hi Jackie,

    Thanks for interesting link, that helped me to think out the solution.

    Expectially part:

    In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

    As I saw, author was manually changing the SSDL file generated first for MSSQL the same way as I did to be able to work with the CE database.

    I found the solution how to let the SSDL file to be generated, so here it is:

    1. Open VS 2010, Create new edmx generated from MSSQL database(the tables should be the same as later in CE).
    2. Migrate schema from MS SQL to CE database
    3. Use EdmGen.exe to generate edmx from the CE database
    4. Create copy of your edmx generated in step 1 and replace the ssdl part with the one generated from CE
    5. Use EdmGen.exe to validate the modified edmx
    6. Use EdmGen.exe to generate SSDL, CSDL and MSL - here I was rather comparing the files for MS SQL and CE and you will see the changes are performed only in SSDL and are the same that the author needed to do.
    7. New SSDL remains to embed into our assembly and setup new connection string for CE which is not difficult and the development can run on CE
    Hope this helps, thanks Jackie for kick off


    zDN@
    Wednesday, February 16, 2011 9:04 AM
  • You're welcome zDN@, and thank you for sharing your detailed solution to us~

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 16, 2011 9:23 AM