@manhnguyenv

Welcome to my blog!

Executing Raw SQL Queries using Entity Framework Core

February 12, 2020 23:51

The way to executing raw SQL in EF Core (edit)

EF Core Extension class

    public static class EFCoreExt
    {
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public static List<TExecuteQuery<T>(this DBContext dbstring querywhere T : classnew()
        {
            using (var command = db.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
 
                db.Database.OpenConnection();
 
                using (var reader = command.ExecuteReader())
                {
                    var lst = new List<T>();
                    var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
                    while (reader.Read())
                    {
                        var newObject = new T();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
                            if (prop == null)
                            {
                                continue;
                            }
                            var val = reader.IsDBNull(i) ? null : reader[i];
                            prop.SetValue(newObjectvalnull);
                        }
                        lst.Add(newObject);
                    }
 
                    return lst;
                }
            }
        }

Categories

Recent posts