具有实体框架的缓存表4 [英] Cache table with Entity Framework 4

问题描述

我有一个表格，其中有很多行是产品表，当用户搜索网站时，我从这个表中进行选择，我还使用了选择中的Include方法，我使用了分析器，我注意到EF生成一个内部联接，左连接等的查询。

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.

我想做的是使用这个select查询并将结果插入临时缓存表在同一个数据库中，然后我可以创建一个服务来每x分钟更新这个表。

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.

问题是，如何让EF使用这个缓存表来选择行，所以我可以做一个select *而不是每次查询产品表与连接？

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?

谢谢！

推荐答案

我很确定EF没有临时表支持 - 至少开箱即用 - 但它不断变化。

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("...")

...我猜在那里你可以运行一个任意的SQL（我知道大多数事情可以被传递，但我不确定的限制，但你可以运行一个SP，创建索引等） - 设置一个临时表。

...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()

...将sql结果映射回您的某个实体 - 或者将非映射实体映射到字符串或某些东西。 （ dbcontext.MyTable.SqlQuery< T>（...）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.

基本上，这是一个DBA思考 - 但EF并不完美的这样的事情（参见类似的内容实体框架中临时表或表变量的推荐使用4.更新性能实体框架），但是您可能会喜欢上面的自定义运行查询。

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.

希望它有助于

编辑：这也可能有助于但是更多的涉及。

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

这篇关于具有实体框架的缓存表4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！