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!
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.