locked
Where does Entity Framework store the relationship between objects? RRS feed

  • Question

  • ** I recently started working on Entity Framework project, and I hate it, so please forgive me for not liking this technology, but it is not a good approach in my opinion as it makes too many SQL calls for the simplest loads; end Entity Framework rant **

    I modified a table that was using the Id field to be the primary key from another table; the field that was called 'Id' is now called TestSetupID, and a new field I added that is now called ID, is an Identity field.

    The lazy loading (another thing I hate about EF) stopped working on this property.

    The SQL Generated by EF is as follows:

    SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[SSTempGround] AS [SSTempGround],
        [Extent1].[SSTempAmbient] AS [SSTempAmbient],
        [Extent1].[BleedCall] AS [BleedCall],
        [Extent1].[BleedIncrement] AS [BleedIncrement],
        [Extent1].[ShowTesttoYield] AS [ShowTesttoYield],
        [Extent1].[CountTolerance] AS [CountTolerance],
        [Extent1].[VolumeReleased] AS [VolumeReleased],
        [Extent1].[PressureReduced] AS [PressureReduced],
        [Extent1].[PumpEfficiency] AS [PumpEfficiency]
        FROM [dbo].[DefaultValueTests] AS [Extent1]
        WHERE [Extent1].[Id] = @EntityKeyValue1
    -- EntityKeyValue1: '1133' (Type = Int32, IsNullable = false)
    -- Executing at 12/29/2015 1:52:34 PM -06:00
    -- Completed in 1 ms with result: SqlDataReader

    The part that is wrong is this:

    WHERE [Extent1].[Id] = @EntityKeyValue1; this should now be WHERE [Extent1].[TestSetupID] = @EntityKeyValue1;

    How do I tell Entity Framework to use the new field. I have the relationship setup in the database as follows:

    It looks to me like this is correct; is there some place EF stores this relationship that is cached to the wrong values?

    Thank you,

    --

    To levitate is hard; showing someone else is really hard.

    Tuesday, December 29, 2015 8:05 PM

All replies

  • ** I recently started working on Entity Framework project, and I hate it, so please forgive me for not liking this technology, but it is not a good approach in my opinion as it makes too many SQL calls for the simplest loads; end Entity Framework rant **

    That's because you don't know how to make it stop doing it, like disconnecting from the database and stopping a foreach loop from going back to the database on each iteration to read data for individual object again. You get 1,000 objects in a List,  and then you loop on a List and read 1,100 times again, because the List of objects are not disconnected from the database.  :) 

    There are adjustments that can cut down on the verboseness of generating T-SQL with any ORM.

    The lazy loading (another thing I hate about EF) stopped working on this property.

    I don't know what you are talking about. 

    You don't tell EF to do anything if you are doing things correctly if you have the parent and children objects within the parent set correctly, then EF will take care of the population of parent and foreign key properties by itself.  

    It looks to me like this is correct; is there some place EF stores this relationship that is cached to the wrong values?

    Nope, and the Identity you have looks kind of suspect at the database schema level.  

    Tuesday, December 29, 2015 9:35 PM
  • One other thing, each object on the EF virtual model has primary and foreign properties within them. But you don't see the foreign-key properties if using DB first if you don't tell the wizard to expose the foreign-key properties in the objects.  

    https://msdn.microsoft.com/en-us/data/jj206878.aspx?f=255&MSPPError=-2147217396

    Tuesday, December 29, 2015 10:17 PM
  • My ORM (Rad Studio Code Generation Toolkit http://radstudio.codeplex.com ), uses all stored procedures and never executes anything unless I tell it to.

    Your comment of 'That's because you don't know how to make it stop doing it', would be better accompanied by a 'How to stop it from doing that, than just a criticism of my comment.'

    I didn't ask for your opinion of the identity column, I asked where does EF store this information?

    Wednesday, December 30, 2015 3:53 PM
  • I am not using DB First or Code First; I just want control over saving object independent of other objects.

    The project was using code first but I turned that off and manually update any changes in both places.

    Is there some file or object that EF stored definitions in?

    Wednesday, December 30, 2015 3:55 PM
  • My ORM (Rad Studio Code Generation Toolkit http://radstudio.codeplex.com ), uses all stored procedures and never executes anything unless I tell it to.

    What's the point of using an ORM if all you are doing is running stored procedures? You defeat the purpose of using an ORM, and you might as well just use straight up ADO.NET with SQL Command objects, sprocs, inline T-SQL and  datareader.  

    Your comment of 'That's because you don't know how to make it stop doing it', would be better accompanied by a 'How to stop it from doing that, than just a criticism of my comment.'

    I gave you one, which you seem to have ignored, like disconnected from DB a List<T> of objects stopping the read from the DB again as one iterates through the List<T> hitting the DB again on each iteration. 

    I didn't ask for your opinion of the identity column, I asked where does EF store this information?

    It looked goofy so I commented on it.

     

    Wednesday, December 30, 2015 5:26 PM
  • I am not using DB First or Code First; I just want control over saving object independent of other objects.

    I do it all the time with DB first, since one can the Wizard to expose primary and foreign key properties in the entities.  So, I don't see the problem, and it makes no sense that you think you can't do the same when using EF.

    JFYI, when the EF entity is inserted into the DB table using an Identity primary-key column, EF populates the key back to the EF entity  primary-key property after the insert. Therefore, one can address the object right have the insert and get its assigned primary-key so that it can be used to populate all children objects foreign-key properties when inserting the children objects manually. :) 

    The project was using code first but I turned that off and manually update any changes in both places.

    I don't have a clue as to what you are talking about. You turned it off? You turned what off? 

    Is there some file or object that EF stored definitions in?

    https://msdn.microsoft.com/library/cc982042(v=vs.100).aspx

    It is used in DB for sure, and I thin Model First. Code

    Wednesday, December 30, 2015 5:55 PM
  • Code First has no EDMX.
    Wednesday, December 30, 2015 5:55 PM
  • Because my ORM is very simple, as saving an object is as simple as:

    if you have a class Person, saving is simply:

    bool saved = gateway.Save(Person ref person);

    Loading an object is as simple as:

    Person person = gateway.FindPerson(personID);

    My tool also creates class objects using partial classes based upon the database structure, so you can update the [ClassName].business.cs and the [ClassName].data.cs is not affected if you recode generate.

    And my tool only executes stored procedures you tell it; I logged the application at my work, in 5 minutes of use Entity Framework creates 500K of SQL statements.

    ss

    Wednesday, December 30, 2015 9:09 PM
  • My tool also code generates the stored procedures, and the DataAccessComponent so the point of using it is to create a 100% stored procedure driven data tier.

    The stored procedures are very easy to modify to create your own custom procs.

    I just can't believe EF is Microsoft's recommended tool for Data Access; but they also hide file extensions by default because they think users are all idiots.

    Wednesday, December 30, 2015 9:17 PM
  • My tool also code generates the stored procedures, and the DataAccessComponent so the point of using it is to create a 100% stored procedure driven data tier.

    Yeah like little rabbits breeding with so many rabbits no one knows what the little rabbits do anymore. I have been there and done that starting with MS SQL Server 6.0.  

    I hate those little rabbits, and I avoid them like the plague. And besides, EF when submitting the T-SQL for execution uses the internal MS SQL Server Stored Procedure to run the generated T-SQL with all the benefits that a Stored Procedure provides.

    The stored procedures are very easy to modify to create your own custom procs. 

    I hate them with all of my heart with every last bit of my heart do I hate them. I hate them so. But now I am dealing with Oracle Packages now, and I hate them equally so.  

    I just can't believe EF is Microsoft's recommended tool for Data Access; but they also hide file extensions by default because they think users are all idiots.

    EF is right there with all the other ORM(s), like nHibernate and others. And your ignorance is what? Well, what can be said about it?

    I am not trying to be smart either. But the whine, I have seen it before. They whined about .NET too back in the day, but .NET is not going away and nether is EF. :)

    Maybe, you should leave the big ol bad boy and top dog EF alone and step down to something lessor.  :)

    https://github.com/linq2db/linq2db

    Wednesday, December 30, 2015 10:27 PM
  • I have used NHibernate, Linq To SQL, and briefly now Entity Framework, and it is the worst of the 3, and I prefer an all stored proc data tier just to avoid dynamic SQL; my tool is over 10 years old; it predates EF, as it was not production ready in 2007 or 2008 when I first looked at it.

    My tool used dynamic SQL until 2010 and I switched to use all stored procedures because it a better approach. As for calling a stored proc with dynamic sql to execute, I would rather have a predefined stored procedure built at development time.

    Since you don't seem to be able to answer a question; please stop responding if you can't answer it.

    My question was, where does EF store the relationships?

    If it doesn't store it, than it must look it up at runtime? that is what I think is horrible about EF to me, besides dynamic SQL, change tracking, code first are all baggage I would never want in my data tier.

    If you think an all stored procedure driven data tier is lesser than dynamic SQL, than I don't think your opinion amounts to much.


    Thursday, December 31, 2015 3:32 PM
  • Since you don't seem to be able to answer a question; please stop responding if you can't answer it

    I suggest that you stop your whining, because that's all you are doing. I have answered your questions which seem to have gone right over your head. You come in here moaning about this, that  and the other talking about how great your little object code generator is, because that's all it is an OCG, it is not an ORM  then use it and stop your whining and ride out -- leave.

    Thursday, December 31, 2015 3:46 PM
  • The fact that you're worrying about where ef keeps relationships is kind of a bad sign really.

    You could dig through this.

    https://msdn.microsoft.com/en-gb/data/jj713564.aspx?f=255&MSPPError=-2147217396

    If you generate the model from a database which has foreign keys and constraints set up then you rarely need to worry about what's going on within ef. That's part of the idea of an orm.

    When sql server generates queries, they are kept so long as they're not pushed out of memory.

    Thus there is often no gain in using a stored procedure over linq generated sql. So long as linq generates sensible sql anyhow.

    .

    What else.

    If you consider change tracking an overhead then just close the context and deal with the entities disconnected.

    .

    I've used ef, nhibernate and linq to sql. Wrote my own code generator years back.

    My experience is that EF is clearly the best of those alternatives.

    Your mileage may vary, but bear in mind you're posting in a Microsoft forum which is about entity framework.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Thursday, December 31, 2015 5:25 PM
  • Andy, I have done the same.

    I've used ef, nhibernate and linq to sql. Wrote my own code generator years back.

    My experience is that EF is clearly the best of those alternatives.

    The OP has a lot of nerve coming in here spitting nails and whining like this in a MS forum. What the OP is looking for is some kind of sliver bullet about where the relationships are kept?  He wants his Pamper changed, hand held and the bottle too.  He's the great OGC guru, and it doesn't take a rocket scientist to figure it out.

    The he starts talking about he wants to save child objects manually, and I gave him the sliver spoon on it, and it went right over his head. And he is the supposed to be the great and all might OCG writer? 

    Thursday, December 31, 2015 5:42 PM
  • EF has a default association convention relevant with class name and property name.

    the convention would help EF to make relation between entities.

    if you want to establish  association between entities , like your sample the primary key is TestSetupId instead of ID

    you could use Data Annotation attributes to override them 

    please refer to 

    https://msdn.microsoft.com/en-us/data/jj591583.aspx

    And i recommend you turn off the lazyloading feature And use Eagerly loading by using Include explicitly

    Why i recommend turn off the loayloading , please refer to 

    https://msdn.microsoft.com/en-us/data/jj574232.aspx?f=255&MSPPError=-2147217396


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    • Edited by Matthew LEAN . D Monday, January 4, 2016 6:36 AM
    • Proposed as answer by Fred Bao Monday, January 11, 2016 1:01 AM
    Monday, January 4, 2016 6:33 AM