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

10 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