How can I query INFORMATION_SCHEMA through Entity Framework?

Question

I'm working on an app that uses metadata db information for analysis purposes, that is, query INFORMATION_SCHEMA and perform tasks based on the data.

In order to ease data manipulation, I implemented a code-first EF layer on top of the INFORMATION_SCHEMA tables/views. Why code first? because I didn't find a way to query the INFORMATION_SCHEMA schema through db first. So I come with:

[Table("INFORMATION_SCHEMA.SCHEMATA")]
internal class Schema
{
  #region Keys

  [Key, Column("CATALOG_NAME", Order = 0), MaxLength(256)]
  public string Catalog { get; set; }

  [Key, Column("SCHEMA_NAME", Order = 1), MaxLength(256)]
  public string Name { get; set; }

  #endregion Keys

  [Column("DEFAULT_CHARACTER_SET_CATALOG")]
  public string DefaultCharacterSetforCatalog { get; set; }

  [Column("DEFAULT_CHARACTER_SET_SCHEMA")]
  public string DefaultCharacterSetforSchema { get; set; }

  [Column("DEFAULT_CHARACTER_SET_NAME")]
  public string DefaultCharacterSetName { get; set; }

  [Column("SCHEMA_OWNER")]
  public string Owner { get; set; }
}

My problem is, when I try to read data using

  _informationSchema.Schemas.Load();

I get the message

  System.Data.SqlClient.SqlException: The specified schema name "INFORMATION_SCHEMA" either does not exist or you do not have permission to use it.

As far as I know, this schema exists. And I can query it with SSMS, so it is not a matter of authorization. I think Maybe EF is preventing queries against INFORMATION_SCHEMA for security reasons? I googled this problem without much success. The only alternate option I see would be to hard-code queries and loose strongly typed data. :( Can someone please help? Thanks in advance.

Answer 1

This is not an exact answer to the question, but rather the best approach to the problem:

First, I read that INFORMATION_SCHEMA is not fully reliable (See The case against INFORMATION_SCHEMA views). I had troubles with missing columns in my descriptions that made me come back to sys schema.

Next, when you install SSMS (Management Studio), which is free, it comes with a set of libraries which makes it super easy to navigate through the content of your model, and event perform some operations. These are:

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoExtended

Since 2017, these libraries also come as a nuget package: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-2017

Developping my app just went so smooth since I use this, I can only recommend it.

Answer 2

Using FromSql worked nicely for me:

internal class InformationSchemaColumn
{
    [Column("TABLE_NAME")]
    public string TableName { get; set; }

    [Column("COLUMN_NAME")]
    public string ColumnName { get; set; }

    [Column("IS_NULLABLE")]
    public string IsNullable { get; set; }

    [Column("CHARACTER_MAXIMUM_LENGTH")]
    public int CharacterMaximumLength { get; set; }
}

public class MyContext : DbContext 
{
  ....
  public IQueryable<InformationSchemaColumn> AllInformationSchemaColumns => AllInformationSchemaColumns.FromSql("SELECT * FROM INFORMATION_SCHEMA.COLUMNS");
  ....
}

Answer 3

I was in the same situation and I want to share how I solved this problem, it was something simple.

The only thing I did was a view.

CREATE VIEW [dbo].[ViewSystemColums]
AS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

And then I just mapped it into the Entity Framework to query the information.