Find number of recently added database rows in Asp.Net Entity Framework

Question

I'm trying to create a stats page on my website that shows how many rows have been added to a specific table in the last X minutes.

This is the first thing I tried:

int games = db.GameLogs.Where(t => t.GameEnded > DateTime.Now - TimeSpan.FromMinutes(1)).Count();

I get this ugly error:

This is the second thing I tried:

var games = db.GameLogs.FromSqlRaw("select count(*) from mtgbattles.gamelogs  where gameended > now() - interval 1 minute");

I get the error:

InvalidOperationException: The required column 'Id' was not present in the results of a 'FromSql' operation.

The query works fine in MySQL Workbench.

Guru Stron · Accepted Answer · 2020-07-05 23:14:56Z

1

Try moving date calculation out of request:

var dt = DateTime.Now.AddMinutes(-1); // maybe `DateTime.UtcNow`
int games = db.GameLogs
    .Where(t => t.GameEnded > dt)
    .Count();

As for FromSqlRaw - currently it has some limitations, including :

The SQL query must return data for all properties of the entity type.

So your select statement should include all fields for GameLogs to make FromSqlRaw work.

edited Jul 5, 2020 at 23:14

answered Jul 5, 2020 at 23:06

Guru Stron

150k11 gold badges186 silver badges232 bronze badges

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

3 Comments

John Shedletsky Over a year ago

In general it seems like putting functions in a fluent EF sql call always causes problems. This is the second time it's bitten me. Your suggestion works great, thanks.

Guru Stron Over a year ago

@JohnShedletsky was glad to help. Also there is SqlServerDbFunctionsExtensions.DateDiffMinute but they should not work for MySQL I assume)

John Shedletsky Over a year ago

MS needs to reconsider their SQL Server licensing situation. I have to use dumpster SQL because MSSQL costs 15k/core. As a shareholder, I guess it's good. But it's not a way to hold onto the future.

Collectives™ on Stack Overflow

Find number of recently added database rows in Asp.Net Entity Framework

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related