locked
RIA/EntityFramework with Views RRS feed

  • Question

  • Hello

     I just wanted to share a problem that I had and what I had to do to solve it just in case anyone else runs into a similar issue.  I think this is more of an Entity Framework issue even though I'm using RIA w/LinQ to entities in my silverlight app.

    Please let me know if I should not post Entity Framework-related issues on this forum and I will carefully examine my posts to keep them strictly to RIA only.  

    I created a complex view (MySQL is the db) which had several outer joins and such.  The end result is about 8-9 tables were being hit with the query.   I created an entity from the view within the VS 2008 designer.   I noticed the results coming back from within the silverlight application did not match the results coming back from within Toad (the tool I use to manage the db /run queries manually against the database).   Both the entity framework/linq and toad were executing the same view.   The same number of rows (3 in this case) came back but the data was different.   Within toad, I saw the correct data (ie, multiple instructors for one class) but within the entity framework I saw just the "first" instructor repeated 3 times.  

    It turns out that the edmx designers wants to have a primary key even with views so by default, it generated the primary key based on all of my non-nullable columns in the view.   That was causing me this problem.   I could not simply delete the keys as I would get a compiler error insisting I have a key.

    Maybe this is not the best possible way to do it but I added the mysql "Rand()" to the query (mysql does not have ROWID so I had to try and find something that would be unique to each row and I could not use a timestamp either as it would be theoretically possible to have the same timestamp for more than 1 row (if the server is screaming fast) and I did this to be the case).

    Had to manually go into the edmx and remove the references to the view and do another "Update Model from database" and then and manually change the edmx again to indicate this rand is the only primary key.

    After doing that and re-running the application, it worked and brought back the correct data.

    I'm not using entity framework 4.0 so I don't know if they fixed that particular issue.

    Anyhow, I apologize for posting this to those that already knew this.   If there are others contemplating using a view (they are so useful in this application) and if you encounter a similar situation, perhaps adding a truely unique identifier (rowid, etc..) and making that the only key within the edmx should yield the results you were after.

    thanks!

     

     

     

     

    Thursday, November 12, 2009 9:33 AM

Answers

  • When I have suggested in the past that you check one of the Entity Framework forums for an answer it was because I didn't have an answer for you and thought that going to a different forum might get you an answer faster. Don't spend too much time worrying if you should ask a question, just ask it.

    Thursday, November 12, 2009 10:08 AM

All replies

  • When I have suggested in the past that you check one of the Entity Framework forums for an answer it was because I didn't have an answer for you and thought that going to a different forum might get you an answer faster. Don't spend too much time worrying if you should ask a question, just ask it.

    Thursday, November 12, 2009 10:08 AM
  • Hello Colin

    Thank you for your reply.    You've been a huge help in many of the RIA/Entity Framework issues that I have had and it's much appreciated.   

    I just wanted to make sure that you/others knew that I am whole-heartedly interested in maintaining within the moderation rules of this particular forum and I did not want to post a specific entity-framework-related issue if it was not within the guidelines of this forum.

    Having said what you just said in your reply, I thank you and will know that Entity-framework related issues within RIA apps is also acceptable on this forum.

    It was an interesting problem that I had with the View/Entity Framework within RIA.

    thanks again for all your RIA/EF help in the past, present and future!!! 

    Thursday, November 12, 2009 11:35 AM