CODEWITHSUNDEEP
c#sqlentity-framework
user1929393
user1929393

Reputation: 4277

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

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

Upvotes: 4

Views: 3402

Answers (2)

user247702
user247702

Reputation: 24222

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

Upvotes: 11

Hassan
Hassan

Reputation: 5430

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.

Upvotes: 1

Related Questions