ASP.NET core get count from a table using raw query

Question

How do I run a raw query in ASP.NET core to return the row count from a table?

Currently, I am doing this and the returned result is -1. I think the return result is based on the number of records affected.

 int numberOfRows = await
                    appDbContext.Database.ExecuteSqlInterpolatedAsync(
                        $"SELECT CODE FROM [samaster] WHERE CODE={productBrandCode} AND WAREHOUSE={warehouse} ");

Any idea on how to get the count back to numberOfRows variable will be appreciated.

NOTE: The above table is not a model so I need to run a raw query.

Thanks

Please mark the answer as an accepted if it was helpful or comment it if-else. — XAMT
– XAMT, Commented Apr 16, 2020 at 13:59

Rypox · Accepted Answer · 2020-04-30 13:55:27Z

It is currently not possible to get the query result when using ExecuteSqlInterpolatedAsync. The same applies to any additional LINQ Statements.

You can, however, use the underlying ADO.net Provider:

    public IList<IDictionary<string, dynamic>> SelectDynamic(string table)
    {
        using (var command = Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = $"SELECT * FROM [{table}]";
            command.CommandType = CommandType.Text;

            Database.OpenConnection();

            using (var result = command.ExecuteReader())
            {
                var entities = new List<IDictionary<string, dynamic>>();

                while (result.Read())
                {
                    var dict = new Dictionary<string, dynamic>();

                    for (int i = 0; i < result.FieldCount; i++)
                    {
                        dict.Add(result.GetName(i), result.GetValue(i));
                    }

                    entities.Add(dict);
                }

                return entities;
            }
        }
    }

Add this to your DbContext Class and Call it with:

using (var context = new MyDbContext()) // Or get it with DI, depends on your application
{
     var count = context.SelectDynamic("samaster").Where(d => d["CODE"] == productBrandCode && d["WAREHOUSE"] == warehouse).Count();
}

Beware, however, that this is an expensive operation if you have a lot of rows in your table! An alternative approach to only fetch the relevant results would be to replace

command.CommandText = $"SELECT * FROM [{table}]";

with

command.CommandText = $"SELECT CODE FROM [samaster] WHERE CODE={productBrandCode} AND WAREHOUSE={warehouse}";

and pass the parameters as function parameters.

public IList<IDictionary<string, dynamic>> SelectDynamic(string productBrandCode, string warehouse)
    {...

Also make sure to escape all parameters if they are in any way submitted by user input to prevent SQL Injection Attacks!

Yes, I end up using the Command Syntax. Strangely enough, there is nothing built in to do this.

XAMT · Accepted Answer · 2020-04-16 06:13:01Z

0

There are two common approaches :

A.

int numberOfRows = await appDbContext.Database.ExecuteSqlInterpolatedAsync($"SELECT CODE FROM [samaster] WHERE CODE={productBrandCode} AND WAREHOUSE={warehouse} ").Count();

B.

int numberOfRows = await appDbContext.Database.ExecuteSqlInterpolatedAsync($"SELECT count(*) FROM [samaster] WHERE CODE={productBrandCode} AND WAREHOUSE={warehouse} ").First();

edited Apr 16, 2020 at 6:13

answered Apr 16, 2020 at 6:07

XAMT

1,6372 gold badges15 silver badges33 bronze badges

5 Comments

Markus S. Over a year ago

I think you should explicitly point out that this code is only safe from sql injection when the string interpolation is done directly as part of passing the query-parameter to ExecuteSqlInterpolatedAsync. When you interpolate the string before by storing in a separate variable, this code will be vulnerable.

Jivan Bhandari Over a year ago

Isn't the above query sql injection safe in the question?

Jivan Bhandari Over a year ago

@XAMT I don't have any method called Cout() or First()

XAMT Over a year ago

@Bikram; Add using System.Linq;

Jivan Bhandari Over a year ago

@XAMT I don't know if you tested your code or not. But using System.Linq does not solve the Count method on mine

Jivan Bhandari · Accepted Answer · 2020-05-01 02:13:58Z

Since nobody gave me the correct answer. I end up using the following.

public async Task<bool> IsAValidProduct(string productBrandCode)
        {
            int count = 0;
            await using DbCommand command = appDbContext.Database.GetDbConnection().CreateCommand();
            command.CommandText =
                "SELECT COUNT(CODE) FROM [samaster] WHERE CODE=@productBrandCode AND WAREHOUSE=@warehouse ";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqlParameter("@productBrandCode", SqlDbType.VarChar)
                {Value = productBrandCode});

            command.Parameters.Add(new SqlParameter("@warehouse", SqlDbType.VarChar)
                {Value = warehouse});
            await appDbContext.Database.OpenConnectionAsync();

            count = (int) await command.ExecuteScalarAsync();

            await appDbContext.Database.CloseConnectionAsync();
            return count == 1;
        }

Gitreef Akram · Accepted Answer · 2022-06-01 09:37:00Z

0

int c= dbObj.Database.ExecuteSqlRaw(sql); //User this code

answered Jun 1, 2022 at 9:37

Gitreef Akram

1

2 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Reza Heidari Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Collectives™ on Stack Overflow

ASP.NET core get count from a table using raw query

4 Answers 4

1 Comment

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related