find a location for property in a new city

Tuesday 15 June 2010

Cross database joins in Entity Framework4

Quite new to Entity Framework 4 I really struggled with the concept of not being able to make an Entity Data Model containing entities of tables that live across different multiple databases. I am still really quite disappointed that EF4 cannot support this and the team behind it doesn't even seem to think that cross database entities are an issue worth considering despite how long this forum has been asking for them.

Anyway, there is a cheeky little way that it can be done. I will use the example of a forum to illustrate this. I need to display posts alongside the username and email address of the contributor of that post. The problem is that the user information lives in a separate database from the post information.

Time to unleash my MS Paint skills to illustrate:

Solution

I got around this by creating a View inside the ForumDB like so:
USE ForumDB
CREATE VIEW dbo.vUserDetails
AS 
 SELECT
  u.UserID,
  u.UserName,
  c.Email
 FROM
  UserDB.dbo.UserNames u
 INNER JOIN
  UserDB.dbo.ContactInfo c ON c.UserID = u.UserID
GO


With this new View in the database I can now create an entity of it in the EDM. Go to your edmx file, right click the background and select 'Update Model from Database...'

Select the data connection to the ForumDB. Then when you are on the Choose Your Database Objects step, make sure you are on the Add tab and select Views and check the vUserDetails view:


You now have access to all your user data. You add an association to link the UserID from the Posts table (Post entity) to the UserID in your new view.


Further

You now have SELECT access to this user data but you cannot currently INSERT/UPDATE/DELETE users. In this scenario this is okay, however if you have a different objective you can still achieve this using stored procedure in the Entities to run on INSERT functions etc.

Follow britishdev on Twitter

22 comments:

  1. If you use StoredProcedures , Why EntitiyFramework isn't it? Its a shame of MS.

    ReplyDelete
  2. Great and valuable information put in this blog, thank you so much for sharing this information with us. Visit OGEN Infosystem for responsive web design and SEO Service.
    SEO Service in Delhi

    ReplyDelete
  3. Allow's transition to advance and get you familiar with either well-known or new terms i.e, directed and unaided calculations which appear to be mistaking for the greater part of the individuals.Data Analytics Courses

    ReplyDelete
  4. I realize that this content is worth to read. I expect to see more posts from you that makes me impressed just like this one. Good job!!
    jigsaw puzzle

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. nice blog!! i hope you will share a blog on Data Science.
    data science courses

    ReplyDelete
  7. Oh my goodness! a tremendous article dude. Thank you Nonetheless I'm experiencing difficulty with ur rss . Don know why Unable to subscribe to it. Is there anybody getting equivalent rss problem? Anybody who is aware of kindly respond. Thnkx Unique Dofollow Backlinks

    ReplyDelete
  8. Building your own educational video streaming services from scratch can be tedious not to mention the long duration you might end up taking to launch it.

    ReplyDelete
  9. It is good to constantly coming up with creative ideas. Provides much needed knowledge. goal oriented blog posts and always tried to find creative ways to meet goals. whatsapp mod

    ReplyDelete
  10. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful. data analytics course in surat

    ReplyDelete
  11. I'm hoping you keep writing like this. I love how careful and in depth you go on this topic. Keep up the great work data analytics course in kanpur

    ReplyDelete
  12. It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing. business analytics course in surat

    ReplyDelete
  13. I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you? Plz reply as I’m looking to construct my own blog and would like to find out where u got this from. thanks a lot|data science course in Jodhpur

    ReplyDelete
  14. I have bookmarked your site since this site contains significant data in it. You rock for keeping incredible stuff. I am a lot of appreciative of this site.

    ReplyDelete
  15. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  16. You have done a great job. I will definitely dig it and personally recommend to my friends. I am confident they will be benefited from this site. data science training in mysore

    ReplyDelete
  17. You completed certain reliable points there. I did a search on the subject and found nearly all people will agree with your blog.
    data science online training in hyderabad

    ReplyDelete

  18. Two full thumbs up for this magneficent article of yours. I've really enjoyed reading this article today and I think this might be one of the best article that I've read yet. Please, keep this work going on in the same quality. data science course

    ReplyDelete