SQLite query not executing properly in a WPF project

Question

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?

I am pretty sure you need to use SqlQuery rather than ExecuteSqlCommand. — Robert
– Robert, Commented Feb 26, 2020 at 14:59

mm8 · Accepted Answer · 2020-02-26 15:08:35Z

1

Try this:

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

answered Feb 26, 2020 at 15:08

mm8

170k10 gold badges71 silver badges102 bronze badges

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

Comments

Robert · Accepted Answer · 2020-02-26 15:10:48Z

0

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();
}

answered Feb 26, 2020 at 15:10

Robert

2,5431 gold badge28 silver badges36 bronze badges

Collectives™ on Stack Overflow

SQLite query not executing properly in a WPF project

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related