C#•2y ago

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

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

Where 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; }
    }

Nope - it cannot directly serialize ForeignConstrains, any other method returns 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> which returns me TableName but not ConstrainName, upon trying 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.

2 Replies

Nergy101•2y ago

Just checking here: the same query runs on the same db/connectionstring directly just fine? The error suggests it's a LINQ-TO-SQL problem... I'd suggest using Dapper-ORM in that case, through extension methods on your dbcontext like you said... Its basically like doing the raw query but has some nice features for mapping to types in a more "raw" manner (different than linq to Sql) Or use the barebones c# Sql command and Sql result classes, with those I'm quite sure you can just get back the exact same records as you'd expect

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Entity framework custom select query