locked
EF 3.5 on tables without relations RRS feed

  • Question

  • Hello community :)

    I'm evaluating the use of EF (3.5 framework) within a new project, but i struggling as my database it's very old and has no FK's or relations, all that information is made available by joins.

    Here's an example:

    Equipments (id, name, localcode)

    Locals (id, localcode, designation)

    Underlines are PK's.

    When i generate my model via Visual Studio, i get 2 entities Locals and Equipments with no relations...

    I was expecting to be able to create a conceptual model to work with my data since i can't make any changes on my database. This way i could add some referential integrity.

    When i tried to add associations is when i start getting problems, in this case i want to associate by localcode, i create the association, but on the association mapping i can't force the system to use localcode, only the id's appear.

    So... first thing.. can i create conceptual model with relations since i don't have it on the database?

    Are there limitations?

    How can i do it?

    Any feedback will be much appreciated, thanks.

    PS: Since i wan to use EF with Sharepoint (without developing another layer between) i'm stuck with EF 3.5.

    Monday, March 26, 2012 7:50 PM

Answers

  • If you don't have the relations then you can't do:

    equipment.designation;

    But you can still do a join.  Select equipment from equipments join local from locals where local.id == equipment.localcode and select a new anonymous type object with {name=equipment.name, designation=local.designation},.  Then you can select anonymousobject.name and anonymousobject.designation.  

    But EF works much better when these static relations are set up at design time and the join is rolled into the query when you type .designation.  If you don't access .desgination, then it doesn't do the join.

    So I think you should add the relations to the EF model.  I don't believe it matters that the database itself doesn't have the relationship.  If the db can join without the relationship then it will still work.  EF just does an inefficient non-indexed join.  (Terrible but it works, AFAIK.)

    • Marked as answer by Allen_MSDN Monday, April 2, 2012 1:32 AM
    Monday, March 26, 2012 8:32 PM
  • Hi Wyck,

    Yes, sorry for my mistake. Below are an article and a vedio about database-first.

    http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-model-amp-database-first-walkthrough.aspx

    http://msdn.microsoft.com/en-us/data/ff191186

    Thank you, Wyck!

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDN Monday, April 2, 2012 1:32 AM
    Friday, March 30, 2012 2:37 AM

All replies

  • If you don't have the relations then you can't do:

    equipment.designation;

    But you can still do a join.  Select equipment from equipments join local from locals where local.id == equipment.localcode and select a new anonymous type object with {name=equipment.name, designation=local.designation},.  Then you can select anonymousobject.name and anonymousobject.designation.  

    But EF works much better when these static relations are set up at design time and the join is rolled into the query when you type .designation.  If you don't access .desgination, then it doesn't do the join.

    So I think you should add the relations to the EF model.  I don't believe it matters that the database itself doesn't have the relationship.  If the db can join without the relationship then it will still work.  EF just does an inefficient non-indexed join.  (Terrible but it works, AFAIK.)

    • Marked as answer by Allen_MSDN Monday, April 2, 2012 1:32 AM
    Monday, March 26, 2012 8:32 PM
  • But how? To be able to create associations i seem to need to have FK's, that i don't have, and i can't seem to find a way to say that the column localcode, will be my FK.
    Tuesday, March 27, 2012 10:42 AM
  • Hi Ricardo JM,

    Welcome to MSDN Forum.

    If the database has already exist, you can use database to do it, EF will help you to create the models from database. Below is an article about how to use database-first, please refer to it.

    As  @Wyck said, you can't add relationship between models if the relationship doesn't exist in the the database, then you can also join tables. Wyck has shown a demo, you can refer it.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 29, 2012 2:46 AM
  • Hi Ricardo JM,

    Welcome to MSDN Forum.

    If the database has already exist, you can use database to do it, EF will help you to create the models from database. Below is an article about how to use database-first, please refer to it.

    As  @Wyck said, you can't add relationship between models if the relationship doesn't exist in the the database, then you can also join tables. Wyck has shown a demo, you can refer it.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Allen_Li1988:  Looks like you're missing a link to that article, maybe?

    Thursday, March 29, 2012 4:22 PM
  • Hi Wyck,

    Yes, sorry for my mistake. Below are an article and a vedio about database-first.

    http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-model-amp-database-first-walkthrough.aspx

    http://msdn.microsoft.com/en-us/data/ff191186

    Thank you, Wyck!

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDN Monday, April 2, 2012 1:32 AM
    Friday, March 30, 2012 2:37 AM