locked
"Model only" relationship? RRS feed

  • Question

  • Hi all,

    I have a database that I can't touch... is there a way to create a (databasefirst modelling tools preferably) model that I can create associations with that allow me to add and manipulate the model in the application without enforcing these changes back on the database?

    So far everything I've tried packs it in stating that a FK needs to be underlying this or some complaints about "mappings" missing... I kind of want the association WITHOUT the mapping... but I can't see how this can be done, if it can... at least with the EDMX designer - maybe I HAVE to use code-first somehow? if so (a) is it easy to switch to code first, or do I need to start again? and (b) are there any walkthroughs/tutorials on achieving this?

    In 95% of cases I don't need to be able to write to this DB if that helps... just wanted to be able to map to it and change it to have ACTUAL relationships that are there in the data, but not enforced by anything in the original system.

    Thanks


    - sure I'm noJedi but that's no reason to stop trying to make stuff levitate! -


    • Edited by noJedi Wednesday, June 1, 2016 12:22 PM
    Wednesday, June 1, 2016 12:20 PM

Answers

  • >Partition class file?

    Yes.  Typo Partial Class.

    >if you mean partial, then surely codefirst is not required,

    Yes it is.  In Database First and Model First you must maintain the mapping files that have the Code Model, Entity Model, and Storage Model and all the mappings between them.  In Code-First these are generated at runtime, as needed.

    >Keep in mind this is a REALLY old rubbish DB... there are duplicate rows for a "key" and so on... I may be better off creating a whole bunch of views

    YEs you may.  EF will be perfectly happy mapping to views with INSTEAD OF triggers, so long as you specify the key for each entity and the relationships between.  Again Code-First to an Existing Database is the right workflow for this, as the keys and relationships are not present in the database metadata.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, June 2, 2016 4:30 PM

All replies

  • Use Code-First.  Start with the Code-First From an Existing Database workflow, and then add the relationships in additional parital class files for your entities and DbContext.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, June 1, 2016 12:48 PM
  • Partition class file? I'm not familiar with that... Do you mean partial classes? or is this something to do with Analysis Services https://technet.microsoft.com/en-us/library/microsoft.analysisservices.partition(v=sql.110).aspx ?

    if you mean partial, then surely codefirst is not required, as the Model tools also generate partial classes...?

    Care to elaborate on HOW this might be done and/or if its even worth it?

    Eg: does it enable you to get additional things loaded from the Db or to you have to make that happen also... somehow?, so I might be better off creating an abstraction layer that wraps EFs DB layer, but at least ITS a 1:1 with the rubbish DB?

    Keep in mind this is a REALLY old rubbish DB... there are duplicate rows for a "key" and so on... I may be better off creating a whole bunch of views and creating the abstraction at the DB but I can't touch that layer...


    - sure I'm noJedi but that's no reason to stop trying to make stuff levitate! -

    Wednesday, June 1, 2016 11:39 PM
  • Not sure if this is helpful, but I typically just use LINQ to SQL by adding a DBML file to the project. Then it is just a matter of dragging the tables from the SQL connection to the designer surface. 

    It would be nice if the VS modeling tools were updated to support CASE functionality including Entity Relationship Diagrams. 

    Thursday, June 2, 2016 12:21 AM
  • Hi noJedi,

    >>I have a database that I can't touch... is there a way to create a (databasefirst modelling tools preferably) model that I can create associations with that allow me to add and manipulate the model in the application without enforcing these changes back on the database?

    According to your description, it seems that couldn't change the database, and you want to create the mapping models form the database. if it is the case, you could use database first, which could generate the models form the database, for more information, please refer to:

    https://msdn.microsoft.com/en-us/data/jj206878

    >>So far everything I've tried packs it in stating that a FK needs to be underlying this or some complaints about "mappings" missing... I kind of want the association WITHOUT the mapping... but I can't see how this can be done, if it can... at least with the EDMX designer - maybe I HAVE to use code-first somehow? if so (a) is it easy to switch to code first, or do I need to start again? and (b) are there any walkthroughs/tutorials on achieving this?

    Do you mean that you want to create a custom partial class which don't need to map to database? if it's the case, you could create a new file called Person.partial.cs (if you have a Person.cs on your edmx file) for your other code. You are free to use any filename for your partial classes. It is only necessary that your partial class is defined in the namespace of the Entity.

    namespace MyModel{
       public partial class Person {
          // write your custom method here.
       } 
    }

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/wa80x488.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 2, 2016 1:33 AM
  • >Partition class file?

    Yes.  Typo Partial Class.

    >if you mean partial, then surely codefirst is not required,

    Yes it is.  In Database First and Model First you must maintain the mapping files that have the Code Model, Entity Model, and Storage Model and all the mappings between them.  In Code-First these are generated at runtime, as needed.

    >Keep in mind this is a REALLY old rubbish DB... there are duplicate rows for a "key" and so on... I may be better off creating a whole bunch of views

    YEs you may.  EF will be perfectly happy mapping to views with INSTEAD OF triggers, so long as you specify the key for each entity and the relationships between.  Again Code-First to an Existing Database is the right workflow for this, as the keys and relationships are not present in the database metadata.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, June 2, 2016 4:30 PM
  • Okay... good to know about the (subtle?) difference between model/db first vs codefirst... that makes sense now.

    Is that because you can't control the mappings except in CodeFirst?

    I can't really touch the DB (in prod) so the only way I could introduce views would be in ANOTEHR DB that provided a front for this one... doesn't sound great...

    I will move forward with this CodeFirst approach thanks (the Linq to SQL approach is one I've used before but has some limitations and I wanted to avoid that, but thanks for the reminder Andy... keeping that as my backup...)


    - sure I'm noJedi but that's no reason to stop trying to make stuff levitate! -

    Friday, June 3, 2016 1:53 AM
  • >Is that because you can't control the mappings except in CodeFirst?

    Technically you can control the mappings in Model-First, but it's much harder.   Code-First + Views is just easier.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, June 3, 2016 1:16 PM