❔ Entity framework custom select query

I want to get result for the following query in EF

SELECT
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    fk.name AS ConstrainName
FROM
    sys.foreign_keys AS fk
JOIN
    sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
WHERE
    OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
ORDER BY
    TableName, ConstrainName;

SELECT
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    fk.name AS ConstrainName
FROM
    sys.foreign_keys AS fk
JOIN
    sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
WHERE
    OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
ORDER BY
    TableName, ConstrainName;

SELECT
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    fk.name AS ConstrainName
FROM
    sys.foreign_keys AS fk
JOIN
    sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
WHERE
    OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
ORDER BY
    TableName, ConstrainName;

SELECT
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    fk.name AS ConstrainName
FROM
    sys.foreign_keys AS fk
JOIN
    sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
WHERE
    OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
ORDER BY
    TableName, ConstrainName;

I fought myself that it should be easy -after all querying some random stuff feels like a basic functionality:
so i tried:

public static async Task<IEnumerable<ForeignConstrains>> GetForeignConstrainsAsync(this DbContext context, string tableName)
{
    var sql = @$"
        SELECT
            OBJECT_NAME(fk.parent_object_id) AS TableName,
            fk.name AS ConstrainName
        FROM
            sys.foreign_keys AS fk
        JOIN
            sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
        WHERE
            OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
        ORDER BY
            TableName, ConstrainName;
    ";

    return await context.Database.SqlQueryRaw<ForeignConstrains>(sql).ToListAsync();
}

public static async Task<IEnumerable<ForeignConstrains>> GetForeignConstrainsAsync(this DbContext context, string tableName)
{
    var sql = @$"
        SELECT
            OBJECT_NAME(fk.parent_object_id) AS TableName,
            fk.name AS ConstrainName
        FROM
            sys.foreign_keys AS fk
        JOIN
            sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
        WHERE
            OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
        ORDER BY
            TableName, ConstrainName;
    ";

    return await context.Database.SqlQueryRaw<ForeignConstrains>(sql).ToListAsync();
}

public static async Task<IEnumerable<ForeignConstrains>> GetForeignConstrainsAsync(this DbContext context, string tableName)
{
    var sql = @$"
        SELECT
            OBJECT_NAME(fk.parent_object_id) AS TableName,
            fk.name AS ConstrainName
        FROM
            sys.foreign_keys AS fk
        JOIN
            sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
        WHERE
            OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
        ORDER BY
            TableName, ConstrainName;
    ";

    return await context.Database.SqlQueryRaw<ForeignConstrains>(sql).ToListAsync();
}

public static async Task<IEnumerable<ForeignConstrains>> GetForeignConstrainsAsync(this DbContext context, string tableName)
{
    var sql = @$"
        SELECT
            OBJECT_NAME(fk.parent_object_id) AS TableName,
            fk.name AS ConstrainName
        FROM
            sys.foreign_keys AS fk
        JOIN
            sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
        WHERE
            OBJECT_NAME(fk.referenced_object_id) = '{tableName}'
        ORDER BY
            TableName, ConstrainName;
    ";

    return await context.Database.SqlQueryRaw<ForeignConstrains>(sql).ToListAsync();
}

Where

ForeignConstrains

ForeignConstrains

ForeignConstrains

ForeignConstrains is:

   public class ForeignConstrains
    {
        public string ConstrainName { get; set; }
        public string TableName { get; set; }
    }

   public class ForeignConstrains
    {
        public string ConstrainName { get; set; }
        public string TableName { get; set; }
    }

   public class ForeignConstrains
    {
        public string ConstrainName { get; set; }
        public string TableName { get; set; }
    }

   public class ForeignConstrains
    {
        public string ConstrainName { get; set; }
        public string TableName { get; set; }
    }

Nope - it cannot directly serialize ForeignConstrains, any other method returns

int

int

int

int so useless for me. Every "solution" just makes DbSet for object or custom type which i do not wish to do, i want to have it purely as an extension method - how hard could it be ?! The closest i got to solution is to get

SqlQueryRaw<string>

SqlQueryRaw<string>

SqlQueryRaw<string>

SqlQueryRaw<string> which returns me

TableName

TableName

TableName

TableName but not

ConstrainName

ConstrainName

ConstrainName

ConstrainName, upon trying

string[]

string[]

string[]

string[] i get same issue:

The element type 'XXX' used in 'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.

The element type 'XXX' used in 'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.

The element type 'XXX' used in 'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.

The element type 'XXX' used in 'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.

❔ Entity framework custom select query

Similar Threads

Similar Threads

Similar Threads