Cache table with Entity Framework 4

Question

I have a table with a lot of rows that is the products table, when an user searches the website, I make a select from this table and I also use the Include method in the select, I used profiler and I noticed that EF generates a query with a lot of inner joins, left joins etc.

What I wanted to do is to use this select query and insert the result in a temporary cache table in the same database and then I can create a service to update this table every x minutes.

The problem is, how do I make EF use this cache table to select the rows so I could just make a select * instead of every time query the products table with the joins?

thanks!

Community · Accepted Answer · 2017-05-23 10:08:46Z

1

I'm pretty certain EF doesn't have temp tables support - at least out of the box - but it's constantly changing.

Your best bet is to do something like this...

dbcontext.Database.ExecuteSqlCommand("...")

...and I'm guessing there you could run an arbitrary SQL (I know most things can be passed in but I'm not sure about the limitations, but you could run a SP, create indexes etc.) - to set up a temp table.

Then the next step would be to do the opposite side something like this..

dbcontext.MyTable.SqlQuery("...").ToList()

...to map back the sql results into some entity of yours - or for a non-mapped entity to string or something. (dbcontext.MyTable.SqlQuery<T>("...").ToList())

The question is how to do it exactly - not sure of your specifics really. But you could create a temp table before hand and have it mapped - and use it for temp purposes.

Basically, that's a DBA thinking - but EF is not perfect for such things (see something similar here Recommed usage of temp table or table variable in Entity Framework 4. Update Performance Entity framework) but you might be ok with a custom ran queries like the above.

hope it helps

EDIT: this might also help from EF forums but it's more involving.

edited May 23, 2017 at 10:08

CommunityBot

11 silver badge

answered Apr 4, 2012 at 23:14

NSGaga

14.4k3 gold badges43 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1082693 Over a year ago

Thanks NSGaga! The main problem is about the other column names... but I think I'll end up by creating some sort of mapping myself... thanks!

NSGaga Over a year ago

added one more link - for a larger Db I had year ago I had to use custom ORM to get what I needed - but EF is in much better shape now, more stable, tested etc.

NSGaga Over a year ago

If you find this answering some / most of your question, please consider marking it as an answer. For others too, or if required edit the question to state the current status, what's answered and what's missing. thanks

Collectives™ on Stack Overflow

Cache table with Entity Framework 4

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related