Because EFCore does not support direct execution of sql statements at present, there is no SqlQuery function to execute.

There are three ways to execute Sql statement in ABP: 1.ExecuteSqlCommand2.FromSql3. Use RawSqlCommand, RelationalDataReader, DbDataReader to read

1. Use ExecuteSqlCommand to execute sql statement and return the number of changed rows

        /// <summary>
        ///Execute the given command
        /// </summary>
        ///< param name = "SQL" > command string < / param >
        ///< param name = "parameters" > parameters to apply to the command string < / param >
        ///< returns > the result returned by the database after executing the command < / returns >
        public int Execute(string sql, params object[] parameters)
        {
            return _dbContextProvider.GetDbContext().Database.ExecuteSqlCommand(sql, parameters);
        }

2. The second method is not used temporarily, occupying a pit

3. Use RawSqlCommand, RelationalDataReader and DbDataReader to read

        public async Task<DataTable> ExecuteDataTable(string sql, params object[] parameters)
        {
            return await Task.Run(() =>
            {
                var db = _dbContextProvider.GetDbContext().Database;
                var conn = db.GetDbConnection();
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                DataTable dt = new DataTable();
                RawSqlCommand rawSqlCommand = db.GetService<IRawSqlCommandBuilder>().Build(sql, parameters);
                RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(db.GetService<IRelationalConnection>(), parameterValues: rawSqlCommand.ParameterValues);
                DbDataReader dr = query.DbDataReader;
                int fieldCount = dr.FieldCount;
                //Get the schema and fill in the first row of data
                if (dr.Read())
                {
                    for (int i = 0; i < fieldCount; i++)
                    {
                        string colName = dr.GetName(i);
                        dt.Columns.Add(colName, dr.GetFieldType(i));
                    }
                    DataRow newrow = dt.NewRow();
                    for (int i = 0; i < fieldCount; i++)
                    {
                        newrow[i] = dr[i];
                    }
                    dt.Rows.Add(newrow);
                }
                //Fill in subsequent data
                while (dr.Read())
                {
                    DataRow newrow = dt.NewRow();
                    for (int i = 0; i < fieldCount; i++)
                    {
                        newrow[i] = dr[i];
                    }
                    dt.Rows.Add(newrow);
                }
                dr.Close();
                dt.AcceptChanges();
                return dt;
            });
        }

The returned is a DataTable, and the code of the object is

            foreach (DataRow row in dataTable.Rows)
            {
                BasListDto basListDto = new BasListDto();
                basListDto.Code = row["Code"].ToString();
                basListDto.Name = row["Name"].ToString();
                basListDto.Id = (int)row["Id"];
                basLists.Add(basListDto);
            }

Detailed explanation, three objects used: RawSqlCommand, RelationalDataReader and DbDataReader

Occupy a pit

①

②

 

Refer to blog 1 https://www.cnblogs.com/hao-1234-1234/p/10243698.html

2.https://www.cnblogs.com/OpenCoder/p/9766793.html

3.http://www.cnblogs.com/tianma3798/p/10025009.html

4.https://stackoverflow.com/questions/51876236/ef-fromsql-with-related-entities

5.https://blog.csdn.net/halibote330/article/details/76583029