Getting row count I managed to get, but why the other approach doesn't work? I am using SQLite with Microsoft's SQLite extension for Entity Framework Core.

This works :

using (var db = new DBConnection())
{
    foreach (var table in db.Model.GetEntityTypes())
    {
        await using var cmd = db.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = $"SELECT COUNT(*) FROM {table.Name.Split(".").Last()}";
        await db.Database.OpenConnectionAsync();
        var count = await cmd.ExecuteScalarAsync();

       Console.WriteLine(count);
    }
} 

This doesn't :

using (var db = new DBConnection())
{
    foreach (var table in db.Model.GetEntityTypes())
    {
        var query = db.Database.SqlQueryRaw<int>("SELECT COUNT(*) FROM {0}", $"SELECT COUNT(*) FROM {0}",{table.Name.Split(".").Last()});

        var count = query.Single();

       Console.WriteLine(count);
    }
}

What did I do wrong?

CodePudding user response：

Somehow you've mis-interpreted the way the second method handles the parameters for the query. Let's break it down:

• Template: "SELECT COUNT(*) FROM {0}"
• Parameter 0: $"SELECT COUNT(*) FROM {0}"
  • Because you have prefixed this with a dollar sign, the value in the curly braces is treated as an expression, which gives the value 0. So this becomes "SELECT COUNT(*) FROM 0"
• Parameter 1: {table.Name.Split(".").Last()}
  • The curly braces around this aren't needed, and I'm not sure that would compile as-is.

When EF executes this, it takes the template and replaces {0} with the value in parameter 0. So you would end up executing the following statement:

SELECT COUNT(*) FROM SELECT COUNT(*) FROM 0

I think you'd be looking for something like:

db.Database.SqlQueryRaw<int>("SELECT COUNT(*) FROM {0}", table.Name.Split(".").Last());

CodePudding user response：

Could you try the following? The second approach contains duplicate select statements.

var tableName = table.Name.Split(".").Last();
var query = db.Database.SqlQueryRaw<int>($"SELECT COUNT(*) FROM {tableName}").ToList();

Be very careful when using FromSqlRaw, and always make sure values are either from a safe origin, or are properly sanitized. SQL injection attacks can have disasterous consequences for your application.