0

I want to do fast, sub-second joins across a few tables using the Entity Framework (model first). The simplified data model is similar to the following:

Videos: ID (Primary Key); Name

Tags: ID (Primary Key); Name

TaggedVideos: TagID (FK); VideoID (FK)

Is there a way to pre-load TaggedVideos when the table is not modeled or exposed in the Entity Framework? I'm trying to avoid database hits when searching for all videos with a certain tag. Ideally, all three tables would be loaded into collections and I could join then using PLINQ.

If I add another column to TaggedVideos, I can accomplish this but I would prefer an elegant solution that doesn't clutter the data model.

Any help would be appreciated.

James
  • 954
  • 2
  • 13
  • 27

1 Answers1

0

Cache the tags and make sure the have a backward nav-property to the collection of videos.

You can then access your cached version of the tag and look up the associated videos without a join.

If you dont want to cache the whole video, in your caching code select use something like Videos.Select(v=>v.Id) to select just the Ids and cache that.

undefined
  • 33,537
  • 22
  • 129
  • 198
  • When I use backward navigation on the cached tags, I get an error: *The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.* This seems like the cache is still hitting the database and the error is coming from the fact I closed the context I used to build the cache. I could be wrong though. – James Jun 22 '12 at 00:32
  • @james You need to do a couple of things, firstly make sure you have disconnected items (you can do this by adding .AsNoTracking() to your query see my post on this here: http://blog.staticvoid.co.nz/2012/04/entity-framework-and-asnotracking.html) and secondly make sure your set is enumerated by using .ToArray() at the end of the query. – undefined Jun 22 '12 at 01:45
  • I'm still using EF 4.0 which doesn't support AsNoTracking. Time to upgrade. I give this a shot tomorrow. – James Jun 22 '12 at 03:19