An Enthusiastic Programmer

Execute Raw SQL

24 Apr 2020 | CSharp-EF
EF

ORM can’t handle complex situations. From time to time, you may need to uplifting performance from executing raw SQL.

Microsoft Document described how the raw SQL queries stuff work on Raw SQL Queries.

Subject to the limitations, here’s a quote:

There are a few limitations to be aware of when using raw SQL queries:

  • The SQL query must return data for all properties of the entity type.
  • The column names in the result set must match the column names that properties are mapped to. Note this behavior is different from EF6. EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • The SQL query can’t contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

So, apply the raw SQL queries on Entity Framework Core still have limitations.

If you are looking for an unlimited solution, then you should use ADO.NET, which allows you to return whatever data type you wanted.

You can get the DatabaseFacade instance from the Context instance by calling the Database attribute. Once you get the DatabaseFacade instance, then you can do whatever custom operation you wanted.

class SchoolContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=.;Database=SchoolDb;Trusted_Connection=True;");
    }
    public DataSet executeProcedure(String procudureName, params SqlParameter[] sqlParameters)
    {
        return executeSqlCommand(procudureName, CommandType.StoredProcedure, sqlParameters);
    }
    public DataSet executeSql(String commandText, params SqlParameter[] sqlParameters)
    {
        return executeSqlCommand(commandText, CommandType.Text, sqlParameters);
    }
    private DataSet executeSqlCommand(String commandText, CommandType Commandtype, params SqlParameter[] sqlParameters)
    {
        DataSet myset = new DataSet();
        using (var command = Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = commandText;
            command.CommandType = Commandtype;
            foreach (var _kv in sqlParameters)
            {
                DbParameter _dbpara = command.CreateParameter();
                _dbpara.ParameterName = _kv.ParameterName;
                _dbpara.Value = _kv.Value;
                command.Parameters.Add(_dbpara);
            }
            Database.OpenConnection();
            DbDataAdapter adapter = DbProviderFactories.GetFactory(Database.GetDbConnection()).CreateDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(myset);
        }
        return myset;
    }
  }
}

Related Posts

Comments