Why does my raw query to count rows always returns -1?

Question

I'm trying to check if table exists, but not working correctly.

For some reason, count always returns -1. I already have a table in the database. It should return 1?

SearchEntities db = new SearchEntities();

var qry3 = "";
var sql4 = "SELECT Count(*) FROM SearchDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'portland'";
var count = db.Database.ExecuteSqlCommand(sql4);

I'm not familiar with EF but `ExecuteSqlCommand` is like `ExecuteNonQuery`? Maybe it always returns `-1` for a `SELECT` statement? — Soner Gönül, Jun 05 '14 at 07:00
The count is appearing as the first column of the first row of the *result set* that your query generates. I don't think that that is what `ExecuteSqlCommand` is returning though - you want something similar to `ExecuteScalar` or some method that actually *returns* the result set to you. — Damien_The_Unbeliever, Jun 05 '14 at 07:01

score 11 · Accepted Answer · edited Oct 25 '19 at 16:32

11

ExecuteSqlCommand doesn't return data, it always returns an Int32, which is the number of rows processed by the SQL script when it's a DDL/DML command.

You want SqlQuery<TElement>(String, Object[]) instead.

var count = db.Database.SqlQuery<int>(sql4).Single();

edited Oct 25 '19 at 16:32

Johwhite

323
4
18

answered Jun 05 '14 at 07:06

user247702

23,641
15
110
157

score 1 · Answer 2 · edited May 23 '17 at 12:34

1

ExecuteStoreQuery Directly Execute Commands Against the Data Source.

int result = entity.ExecuteStoreQuery<int>(@"
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TableName') 
    SELECT 1
ELSE
    SELECT 0
").SingleOrDefault();

See answer here.

edited May 23 '17 at 12:34

Community

1
1

answered Jun 05 '14 at 07:15

Hassan

5,360
2
22
35

1

`ObjectContext`, while still available, is an older API. The `DbContext` API is recommended these days. [ref](http://stackoverflow.com/a/15875489/247702) – user247702 Jun 05 '14 at 07:23

Why does my raw query to count rows always returns -1?

2 Answers2

Linked

Related