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"
- Because you have prefixed this with a dollar sign, the value in the curly braces is treated as an expression, which gives the value
- 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.