Entity Framework 4 - Joining SQL View with IQueryable<T> Repository

Question

If i have the following IQueryable<T> Repository:

public class OrderRepository : GenericRepository<Order>, IOrderRepository
{
   public IQueryable<Order> Find()
   {
       return Context.CurrentEntitySet;
   }
}

And i use it like this to get a particular order:

var order = orderRepository.Find().SingleOrDefault(x => x.OrderId == 1);

All is fine.

Now, i have a SQL Server 2008 View called OrderDetailsView, which i have added to my EDMX. It has a field called OrderId which i want to join on. It's a 1-1 between Order and OrderDetailsView.

I want to return a particular Order, and the corresponding OrderDetailsView record.

I'm not sure what the correct approach here is.

• Can i have a navigational property to the View on my Order entity? I can add the association - but the designer is complaining about it not being mapped - i'm assuming it requires a FK, but obviously a View cannot have a FK.

• Do i need to do an explicit join? If so, that means they are independant entities with no association, so i would require another Repository - and i'd need two database calls, as you can't perform a JOIN across two repositories.

Ideally, i would like to do this:

return ctx.Orders.Include("OrderDetailsView").SingleOrDefault();

But as mentioned, i don't know if it's possible to add a navigational property that points to a View.

Any ideas?

Answer 1

Got it - i needed to add a Referential Constraint (since there is no FK):

Now i can eager load the view like any other nav.

Well that was easier than i thought! :)