ASP.NET Core .NET 5 打造WebAPI入門教學(4_11) - 自行擴充DbContext之ExecSQL用法功能

自己寫額外的方法

public partial class TodoContext : DbContext
    {
        public List<t> ExecSQL<t>(string query)
        {
            using (var command = Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                Database.OpenConnection();

                List<t> list = new List<t>();
                using (var result = command.ExecuteReader())
                {
                    T obj = default(T);
                    while (result.Read())
                    {
                        obj = Activator.CreateInstance<t>();
                        foreach (PropertyInfo prop in obj.GetType().GetProperties())
                        {
                            if (!object.Equals(result[prop.Name], DBNull.Value))
                            {
                                prop.SetValue(obj, result[prop.Name], null);
                            }
                        }
                        list.Add(obj);
                    }
                }
                Database.CloseConnection();
                return list;
            }
        }
    }

使用

var result = _todoContext.ExecSQL<TodoList>("select * from todolist");

參考資料

閒聊：用 LINQ 還是自己寫 SQL？

Entity Framework Core原始 SQL 查詢

Entity Framework6 原始 SQL 查詢

Entity framework Core Raw SQLQueries with custom model