ZBAGI Posts - Answer Overflow

ZBAGI

•Created by ZBAGI on 5/31/2023 in #help

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

3 replies

CC#

•Created by ZBAGI on 8/16/2022 in #help

Azure app insights api query problem

Hi, I trying to query application insights via api by using provided nuget package - https://github.com/Azure/azure-sdk-for-net/blob/Azure.Monitor.Query_1.1.0/sdk/monitor/Azure.Monitor.Query/README.md everything works like in example. Except when I replace query "AzureActivity | top 10 by TimeGenerated" with what i need ie. requests it seems to not understand request scope. This query works without any problem when used in azure portal, so why is it complaining in api ? I made sure i use correct workspace and credentials. Here is the code:

string workspaceId = "<redacted>";

var auth = new ClientSecretCredential("<redacted>", "<redacted>", "<redacted>");
var client = new LogsQueryClient(auth);

var response = await client.QueryWorkspaceAsync(
    workspaceId,
    "requests",
    new QueryTimeRange(TimeSpan.FromDays(1)));

string workspaceId = "<redacted>";

var auth = new ClientSecretCredential("<redacted>", "<redacted>", "<redacted>");
var client = new LogsQueryClient(auth);

var response = await client.QueryWorkspaceAsync(
    workspaceId,
    "requests",
    new QueryTimeRange(TimeSpan.FromDays(1)));

Azure.RequestFailedException: 'The request had some invalid properties
Status: 400 (Bad Request)
ErrorCode: BadArgumentError

Content:
{"error":{"message":"The request had some invalid properties","code":"BadArgumentError","correlationId":"<redacted>","innererror":{"code":"SemanticError","message":"A semantic error occurred.","innererror":{"code":"SEM0100","message":"'where' operator: Failed to resolve table or column expression named 'requests'"}}}}

Azure.RequestFailedException: 'The request had some invalid properties
Status: 400 (Bad Request)
ErrorCode: BadArgumentError

Content:
{"error":{"message":"The request had some invalid properties","code":"BadArgumentError","correlationId":"<redacted>","innererror":{"code":"SemanticError","message":"A semantic error occurred.","innererror":{"code":"SEM0100","message":"'where' operator: Failed to resolve table or column expression named 'requests'"}}}}

Any idea what is wrong ?

2 replies

Gaming

Programming