.NET实体框架如何选择最近1分钟更新的记录？

The purpose of my application is to retrieve records based on a criteria.

我的应用程序的目的是根据标准检索记录。

The problem is that there could be millions of records but I only want to select records that were updated in the last 1 minute.

问题是可能有数百万条记录，但我只想选择最近1分钟更新的记录。

First time I select all records but after a minute I cannot select everything again as it would take a very long time. I only want to select Orders that were updated within that interval.

我第一次选择所有记录但是一分钟之后我不能再次选择所有记录，因为它需要很长时间。我只想选择在该时间间隔内更新的订单。

var records = context.Orders.Where(x => x.Category == "GAMING");

Is there something I could do? Subscribe to some sort of database event?

有什么我可以做的吗？订阅某种数据库事件？

2 个解决方案

#1

The most efficient way to do this would be by including a datetime column that held a timestamp (ex.DateInserted), and filtering based on its value:

最有效的方法是通过包含一个包含时间戳（ex.DateInserted）的日期时间列，并根据其值进行过滤：

var minuteAgo = DateTime.Now.AddMinutes(-1);
var recordsThisMinute =  context.Orders.Where(x => x.DateInserted >= minuteAgo && x.Category == "GAMING");

However, if you wish to produce the same results, without touching the table you could always try the following:

但是，如果您希望产生相同的结果，而不触及表格，则可以尝试以下方法：

//Assuming oldRecords is the variable in which
//you're storing the records you selected a minute ago
var recordsThisMinute = context.Orders.Where(x => x.Category == "GAMING").Except(oldRecords);

#2

Maybe You can use a static Dictionnary of idOrder,datetime in your DbContext managed in an overload off the SaveChanges method.

也许你可以使用idOrder的静态字典，在DbContext中的日期时间在SaveChanges方法的重载中管理。

public static IEnumarable<Order> GetLastUpdatedOrder()
{
//Return dictionnay here
}
public override int SaveChanges()
{
    var updatedOrder = ChangeTracker.Entries<Order>().Where(o => o.State == EntityState.Modified).ToList();
    //Manage here Your dictionnary
    var i = base.SaveChanges();
    return i;
}

#1