@manhng

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;
                }
            }
        }

EF Core

September 18, 2017 11:15

Getting Started With Entity Framework Core - Console

http://www.learnentityframeworkcore.com/walkthroughs/console-application

Step 1) Creating a .Net Core Console application

Step 2) Creating A Model

Step 3) Adding A Migration
dotnet ef migrations add CreateDatabase
dotnet ef database update

Step 4) Modifying The Database With Migrations
dotnet ef migrations add LimitStrings
dotnet ef database update

How to use the "dotnet ef migrations ..."

<Project Sdk="Microsoft.NET.Sdk.Web">
    ...
        <ItemGroup>
        ...
        <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="1.1.1" />
    </ItemGroup>
    <ItemGroup>
        <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.1" />
        <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />
</ItemGroup>
    ...
</Project>

 

Categories

Recent posts