CODEWITHSUNDEEP
c#wpfsqlite
Attila Szász
Attila Szász

Reputation: 747

SQLite query not executing properly in a WPF project

I'm working on a WPF application and using SQLite database. I can do every CRUD operation with Entity Framework, but in some specific cases I have to use raw SQL queries, and sometimes it's not returning what I need.

Here is a sample code:

using (var db = new DbContext(AppIO.DatabaseFilePath)) {
    var key = 12;
    string sql = $"SELECT COUNT(*) FROM SomeTable WHERE SomeField={key}";
    var result = db.Database.ExecuteSqlCommand(sql);
}

I simplified the example. Here the result, what I got is -1. I copied the sql string value (after it's built) and executed in SQLiteStuido on the same database and it returned the correct value.

The DatabaseFilePath is correct. The connection is set correctly. I'm checking the same databases (in code and in SQLiteStudio). Any other idea?

Upvotes: 0

Views: 285

Answers (2)

Robert
Robert

Reputation: 2523

You have to call SqlQuery method and not ExecuteSqlCommand method. Since SqlQuery returns an IEnumerable you have to call Single. This is a the way to retreive scalar values from a query.

using (var db = new DbContext(AppIO.DatabaseFilePath)) {
    var key = 12;
    string sql = $"SELECT COUNT(*) FROM SomeTable WHERE SomeField={key}";
    var result = db.Database.SqlQuery<int>(sql).Single();
}

Upvotes: 0

mm8
mm8

Reputation: 169200

Try this:

var result = db.Database.SqlQuery<int>(sql).First();

Upvotes: 1

Related Questions