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

26 comments:

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

    ReplyDelete
  2. Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng sau:



    bồn massage



    bon ngam chan



    máy massage chân



    Cảm ơn các bạn!

    ReplyDelete
  3. Bài viết rất hay: Chúng tôi có các dịch vụ in ấn chuyên nghiệp sau:



    in bao bì vỏ hộp

    in hộp bìa cứng

    ReplyDelete
  4. Bài viết của bạn rất hay!

    Công ty In Ấn Phúc Nguyên cung cấp các dịch vụ giá rẻ nhất tại hà nội:



    Báo giá in thùng carton giá rẻ nhất



    in bao bì tại hà nội



    in hộp cứng

    ReplyDelete


  5. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to article very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    DedicatedHosting4u.com

    ReplyDelete
  6. Great. 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.

    Thanks
    Online affiliates

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. Permainan Sabung Ayam tentunya sudah pada tahu ya, yang dimana ayam melawan ayam pertandingan yang sangat seru ini bisa kalian nonton secara live lohh, banyak yang bermain di situs kami dan merasa sangat nyaman, bagi kalian yang ingin bermain bisa kunjungi situs kami, dijamin kalian akan merasa sangat senang.

    https://www.s128apk.asia/

    https://www.s128apk.asia/daftar-s128/

    https://www.s128apk.asia/sabung-ayam-deposit-pulsa-telkomsel-dan-xl-daftar-s128/

    https://www.s128apk.asia/login-s128-cara-login-judi-sabung-ayam-online/

    https://www.s128apk.asia/link-alternatif-s128/

    ReplyDelete
  11. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. ExcelR Data Analytics Courses Any way I’ll be subscribing to your feed and I hope you post again soon. Big thanks for the use

    ReplyDelete
  12. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    data science course

    ReplyDelete
  13. Bài viết rất hữu ích

    Chúng tôi chuyên cung cấp các sản phẩm sau:

    Cây Bạc hà



    Cây bạch bộ



    Bạch Đồng nữ

    ReplyDelete
  14. Know more about Data Analytics
    I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place

    ReplyDelete
  15. cool stuff you have and you keep overhaul every one of us

    data science interview questions

    ReplyDelete
  16. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  17. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  18. This is my first time visit here. From the tons of comments on your articles.I guess I am not only one having all the enjoyment right here! ExcelR Data Analytics Courses

    ReplyDelete
  19. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  20. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  21. very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  22. very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete