locked
Converting DAL to Business Objects with LINQ to Entities and multiple DBMS flavors RRS feed

  • Question

  • I've been investigating using LINQ to entities to implement a data access layer for an application.  Our existing application supports both Microsoft SQL Server and Oracle.

    Ideally I would like to abstract the DAL and be able to convert the retrieved data into my business objects.  What I am guessing based on my research is that I will need to write a separate layer for each DBMS which converts the generated entity objects into my business objects.

    For example, I connect VS 2010 to both databases and generate two separate sets of entity objects (.edmx) files.  In order to implement the data layer, I will have to have one set of helper functions that converts the SQL Server entity objects into my business/domain objects, and another set of helper functions which converts the Oracle entity objects to business/domain objects.

    Based on this, LINQ gives me a lot (query generation and (hopefully) efficiency), but I still have to write a fair amount of duplicated code to convert from the data layer to the business layer.  In a perfect scenario, the two generated entity models would be the same and I would only have to write one set of conversion functions - and at runtime the appropriate SQL would be generated based on the DBMS; I am guessing that is not the case, however (because there isn't a way to ensure the schemae will match exactly).

    Is this an accurate description of LINQ to Entities?

    Thursday, March 31, 2011 12:22 AM

Answers

  • Actually, it sounds like he's referring to splitting the EDMX into it's three component parts (the MSL, SSDL, and CSDL), so you'd use a shared CSDL and MSL, but separate SSDLs. I haven't done it personally, so I could be wrong, but I think you'd use EdmGen to generate your entity objects from your shared CSDL. Then you'd swap your connection strings.

    Using a shared set of POCOs against two fully separate EDMXs would probably also work, though I've never tried it.

    Finally the third option, which you mentioned in your original post, is using two sets of LINQ mappings to project each data schema into your business objects. This may be your best option if your databases are fairly different. I've personally been down this route if you'd like to know the pitfalls you'd run into, but it sounds like your data schemas are similar enough that the other two options would be a better fit.


    Layer Over LINQ - The LINQ toolkit that fixes all your LINQ to Entities pain points
    • Proposed as answer by Jackie-Sun Friday, April 8, 2011 1:52 AM
    • Marked as answer by Alan_chen Tuesday, April 12, 2011 7:12 AM
    Tuesday, April 5, 2011 11:39 AM
  • Hi,

    Yes, like JRS52 says, using EdmGen is an option to generate an SSDL file for your Oracle connection and then swap the connection string.

    BUT.. I hate using manual tools when developing, like EdmGen since it involves a manual part of the generation. So I rather prefer to have the following:

    1. The default edmx model (I decide which dbms I use the most and has this as the default)
    2. A project folder for each dbms, e.g Oracle
    3. In each of these folders, import the dbms datamodel
    4. On each datamodel in the project folders set Code Generation Strategy to None on the edmx file

    When you have done this, the assembly will in resources contain a csdl, msl and ssdl for each all the edmx'es. These will typically have the namespace name (like Oracle.MyModel.ssdl and MySql.MyModel.ssdl) in addition to the base MyModel.csdl, MyModel.ssdl and MyModel.msl. You can then mix your connectionstring with these.

    Eg:

    For default connection:
    metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl

    For Oracle connection:
    metadata=res://*/MyModel.csdl|res://*/Oracle.MyModel.ssdl|res://*/MyModel.msl

    For MySql connection
    metadata=res://*/MyModel.csdl|res://*/MySql.MyModel.ssdl|res://*/MyModel.msl

    NOTE: This is only the metadata part of the connection string, the rest you have to figure out based on your DBMSes.

    I'm a bit worried about your statement that it is the same for the most part. This may break my logic, since the generated files from the edmx is so close coupled. I suspect that this may cause some problems for you by using my suggested method since it requires practical identical model. But it's worth a try. You could also try to sort out the differences between your models and create seperate edmx'es which is db specific just for these differences.

    If this isn't possible, the only way for you is to do it the hard way, by generating code manually that manually connects to each edmx and maps data manually.

    Hope this helps, if not just ask. I will be away for the rest of the week, so it may be a while before I can answer any more.


    --Rune
    • Proposed as answer by Jackie-Sun Friday, April 8, 2011 1:52 AM
    • Marked as answer by Alan_chen Tuesday, April 12, 2011 7:12 AM
    Tuesday, April 5, 2011 2:05 PM

All replies

  • Hi!

    Your database model in MSSQL and Oracle, are they exactly the same? Same tables, columns, procedures etc?

    If it is so, then you can actaully use the same conceptual model against different storage models.

    The connection string defines three different xml files (which is generated from the edmx file) which are the conceptual model, the storage model and the mapping model.

    What you can do is just to have two different connection strings which uses the same conceptual and mapping model, but different storage model, and you could convert your DAL <-> Business objects against only one conceptual model.

    I have done this in a project that combines MSSQL and MySql and it works like a charm :)

    Hope this helps,


    --Rune
    Friday, April 1, 2011 8:07 AM
  • They are the same for the most part - most current code uses JDBC to talk to the database and it doesn't differentiate in 99% of cases.

    It sounds like you are saying that I could simply use the generated POCO classes and DbContext classes, but pass in a different connection string and have it automatically switch between providers - and it should work as long as the schemae are close enough to the generated model?

    What steps did you use to get to where you are?  I added a new EDMX model and used generate from database to create POCOs for SQL server.

    Monday, April 4, 2011 10:56 PM
  • Actually, it sounds like he's referring to splitting the EDMX into it's three component parts (the MSL, SSDL, and CSDL), so you'd use a shared CSDL and MSL, but separate SSDLs. I haven't done it personally, so I could be wrong, but I think you'd use EdmGen to generate your entity objects from your shared CSDL. Then you'd swap your connection strings.

    Using a shared set of POCOs against two fully separate EDMXs would probably also work, though I've never tried it.

    Finally the third option, which you mentioned in your original post, is using two sets of LINQ mappings to project each data schema into your business objects. This may be your best option if your databases are fairly different. I've personally been down this route if you'd like to know the pitfalls you'd run into, but it sounds like your data schemas are similar enough that the other two options would be a better fit.


    Layer Over LINQ - The LINQ toolkit that fixes all your LINQ to Entities pain points
    • Proposed as answer by Jackie-Sun Friday, April 8, 2011 1:52 AM
    • Marked as answer by Alan_chen Tuesday, April 12, 2011 7:12 AM
    Tuesday, April 5, 2011 11:39 AM
  • Hi,

    Yes, like JRS52 says, using EdmGen is an option to generate an SSDL file for your Oracle connection and then swap the connection string.

    BUT.. I hate using manual tools when developing, like EdmGen since it involves a manual part of the generation. So I rather prefer to have the following:

    1. The default edmx model (I decide which dbms I use the most and has this as the default)
    2. A project folder for each dbms, e.g Oracle
    3. In each of these folders, import the dbms datamodel
    4. On each datamodel in the project folders set Code Generation Strategy to None on the edmx file

    When you have done this, the assembly will in resources contain a csdl, msl and ssdl for each all the edmx'es. These will typically have the namespace name (like Oracle.MyModel.ssdl and MySql.MyModel.ssdl) in addition to the base MyModel.csdl, MyModel.ssdl and MyModel.msl. You can then mix your connectionstring with these.

    Eg:

    For default connection:
    metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl

    For Oracle connection:
    metadata=res://*/MyModel.csdl|res://*/Oracle.MyModel.ssdl|res://*/MyModel.msl

    For MySql connection
    metadata=res://*/MyModel.csdl|res://*/MySql.MyModel.ssdl|res://*/MyModel.msl

    NOTE: This is only the metadata part of the connection string, the rest you have to figure out based on your DBMSes.

    I'm a bit worried about your statement that it is the same for the most part. This may break my logic, since the generated files from the edmx is so close coupled. I suspect that this may cause some problems for you by using my suggested method since it requires practical identical model. But it's worth a try. You could also try to sort out the differences between your models and create seperate edmx'es which is db specific just for these differences.

    If this isn't possible, the only way for you is to do it the hard way, by generating code manually that manually connects to each edmx and maps data manually.

    Hope this helps, if not just ask. I will be away for the rest of the week, so it may be a while before I can answer any more.


    --Rune
    • Proposed as answer by Jackie-Sun Friday, April 8, 2011 1:52 AM
    • Marked as answer by Alan_chen Tuesday, April 12, 2011 7:12 AM
    Tuesday, April 5, 2011 2:05 PM