How can I retrieve metadata via sys.objects or INFORMATION_SCHEMA through Entity Framework?

Question

I have certain school tasks that require me to retrieve metadata from a few tables. One of them is trying to get all keys from the tables I am working with. I have managed to find a valid SQL query:

SELECT 
    CONSTRAINT_NAME AS 'KeyName', 
    TABLE_NAME AS 'TableName', 
    COLUMN_NAME AS 'ColumnName'
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_NAME = 'CRONUS Sverige AB$Employee'
    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Relative'
    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Absence'
    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Qualification'``

I would like to retrieve this information using Entity Framework, but I don't know how to query INFORMATION_SCHEMA.KEY_COLUMN_USAGE or sys.objects.

If I were to query the Employee table for example it would look like this:

using (CronusEntities cronusEntities = new CronusEntities())
{
    var query = from relative in cronusEntities.CRONUS_Sverige_AB_Employee_Relative
                where relative.Employee_No_ == no
                select relative;

    List<CRONUS_Sverige_AB_Employee_Relative> relatives = new List<CRONUS_Sverige_AB_Employee_Relative>();

    foreach (var rel in query)
    {
        relatives.Add(rel);
    }

    return relatives;
}

But I can't find any option to query sys.objects with linq.

Is it possible to do this via Entity Framework or do I have to use some other method?

The other tasks include retrieving all indexes and all table_names so if I can learn how to query sys.objects and/or Information_schema then I could solve all of them.

EDIT: I have tried making a raw query like this but i get the following error message: System.Data.Entity.Core.EntityCommandExecutionException: The data reader has more than one field My raw query ettempt looks like this:

public List<string> GetKeys()
        {
            using (CronusEntities cronusEntities = new CronusEntities())
            {
            
                    var keys = cronusEntities.Database.SqlQuery<string>(@"SELECT CONSTRAINT_NAME AS 'KeyName', TABLE_NAME AS 'TableName', COLUMN_NAME AS 'ColumnName'
                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE TABLE_NAME = 'CRONUS Sverige AB$Employee' 
                    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Relative'
                    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Absence'
                    OR TABLE_NAME = 'CRONUS Sverige AB$Employee Qualification'").ToList();

                    return keys;
            }
        }

Hope I was clear enough, thank you in advance!

I am using Visual Studio 17.0.6, Entity Framework 6.

Answer 1

You can run raw queries as mentioned here: https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

using (var context = new BloggingContext())
{
    var blogNames = context.Database.SqlQuery<string>(
                       "SELECT Name FROM dbo.Blogs").ToList();
}

Also check this answer: How do I query sysobjects with linq? or this article: https://social.msdn.microsoft.com/Forums/en-US/6217cdf5-930a-4243-a4c1-a3b21cbb075d/linq-to-entities-for-sys-schema-views?forum=adodotnetentityframework which suggests exposing the sys metadata views as a user view, imported in your model that you can query against.