Unable to read system views in Entity Framework

Question

I am trying to execute the following query

  SELECT object_id 
  FROM sys.tables 
  WHERE sys.tables.name = 'Projects'

as

int n = context.Database.SqlQuery<int>(
      "SELECT object_id from sys.tables where sys.tables.name = 'Projects'")
      .FirstOrDefault();

I get n as always 0

If I use SqlConnection and query it using a SqlCommand I get the correct results. So why does DbContext.Database.Connection not let me execute a plain SQL query?

For simplicity I have removed SqlParameter, so I am aware this code is not SQL Injection safe.

Answer 1

There is no problem with system views, Entity Framework cannot read value types in SqlQuery. So I had to change it,

public class SingleValue<T>{
    public T Value {get;set;}
}

int n = context.Database.SqlQuery<SingleValue<int>>(
  "SELECT object_id as Value from sys.tables where sys.tables.name = 'Projects'")
  .ToList().Select(x=>x.Value).FirstOrDefault();