SqlQuery in EF Core (EntityFrameworkCore) without DbSet

Executing SqlQuery in Entity Framework Core without DbSet

• Create asp.core MVC api project
• Add the required packages to project
• Add DbContext Class
• Add Extension methods
• Add Interface (IDepartmentService) to project
• Add Class (DepartmentService) and implement Interface (IDepartmentService)

Add the following packages to project.

 

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "myconnection": "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=(local);"
  },
  "AllowedHosts": "*"
}

SiteKeys.cs

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace EmployeeApi
{
    public class SiteKeys
    {
        
        public static string ConnectionString
        {
            get
            {             
                return Configuration["ConnectionStrings:myconnection"];
            }
        }

        private static IConfigurationRoot Configuration
        {
            get
            {
                IConfigurationRoot _config = new Microsoft.Extensions.Configuration.ConfigurationBuilder()
                    .SetBasePath(Directory.GetCurrentDirectory())
                    .AddJsonFile("appsettings.json").Build();
                return _config;
            }
        }
    }
}

MyDbContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi.Models.Core
{
    public class MyDbContext:DbContext
    {
        public MyDbContext(DbContextOptions options):base(options)
        {

        }
        public MyDbContext()
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(SiteKeys.ConnectionString);
            }           
        }
    }
}

Department.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi.Models.DataModels
{
    public class Department
    {
        public int DepartmentID { get; set; }
        public string DepartmentName { get; set; }
    }
}

DataTableExtensions.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace EmployeeApi.Models.Core
{
    public static class DataTableExtensions
    {
        public static List<T> ToList<T>(this DataTable table) where T : new()
        {
            var props = typeof(T).GetProperties().ToList();
            var result = new List<T>();
           
            foreach(DataRow row in table.Rows)
            {
                result.Add(DataRowToObject<T>(row, props));
            }
            return result;
        }

        private static T DataRowToObject<T>(DataRow row, IList<PropertyInfo> props) where T : new()
        {
            T item = new T();
            foreach (var prop in props)
            {
                if (row.Table.Columns.Contains(prop.Name))
                {
                    Type proptype = prop.PropertyType;
                    var targetType = IsNullableType(prop.PropertyType) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType;
                    var propertyVal = row[prop.Name];
                    propertyVal = Convert.ChangeType(propertyVal, targetType);
                    prop.SetValue(item, propertyVal, null);
                }
            }
            return item;
        }
        private static bool IsNullableType(Type type)
        {
            return type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>));
        }
        public static bool HasProperty(this object obj, string propertyName)
        {
            return obj.GetType().GetProperty(propertyName) != null;
        }
    }
}

DbContextExtensions.cs

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi.Models.Core
{
    public static class DbContextExtensions
    {
        public static List<T> SqlQuery<T>(this DbContext context,string sqlquery) where T:new()
        {
            
            var constring = context.Database.GetDbConnection().ConnectionString;
            SqlConnection con = new SqlConnection(constring);
            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandText = sqlquery;
                SqlDataAdapter da = new SqlDataAdapter(com);                
                DataTable dt = new DataTable();
                da.Fill(dt);               
                var toReturn = dt.ToList<T>();
                return toReturn.ToList();
            }
            catch (Exception ae)
            {
                throw new Exception(ae.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static List<T> SqlQuery<T>(this DbContext context, string query,params SqlParameter[] parameters) where T : new()
        {
            var constring = context.Database.GetDbConnection().ConnectionString;
            SqlConnection con = new SqlConnection(constring);
            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandText = query;                
                com.Parameters.AddRange(parameters);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
                var toReturn = dt.ToList<T>();
                return toReturn.ToList();
            }
            catch (Exception ae)
            {
                throw new Exception(ae.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static List<T> SelectStoredProcedure<T>(this DbContext context, string ProcedureName, params SqlParameter[] parameters) where T : new()
        {
            var constring = context.Database.GetDbConnection().ConnectionString;
            SqlConnection con = new SqlConnection(constring);
            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandText = ProcedureName;
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddRange(parameters);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
                var toReturn = dt.ToList<T>();
                return toReturn.ToList();
            }
            catch (Exception ae)
            {
                throw new Exception(ae.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }
}

Interface IDepartmentService.cs

using EmployeeApi.Models.DataModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi.Services
{
   public interface IDepartmentService
    {
        List<Department> SelectAll();
    }
}

DepartmentService.cs

using EmployeeApi.Models.Core;
using EmployeeApi.Models.DataModels;
using System;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace EmployeeApi.Services
{
    public class DepartmentService : IDepartmentService
    {
        MyDbContext _db = new MyDbContext();
        public List<Department> SelectAll()
        {           
            var depts = _db.SqlQuery<Department>("Select DepartmentID,DepartmentName from tblDepartment");
            return depts;
        }
    }
}

StartUp.cs

using EmployeeApi.Models.Core;
using EmployeeApi.Services;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddTransient<IDepartmentService,DepartmentService>();
            services.AddDbContext<MyDbContext>(x => x.UseSqlServer(SiteKeys.ConnectionString));
            services.AddMvcCore().AddJsonOptions(x => x.JsonSerializerOptions.WriteIndented = true);
            services.AddControllers();
           
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
          
            app.UseRouting();
            
            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

DepartmentController

using EmployeeApi.Services;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DepartmentController : ControllerBase
    {
        IDepartmentService _departmentService;
        public DepartmentController(IDepartmentService departmentService)
        {
            _departmentService = departmentService;
        }
        [HttpGet]
        public JsonResult Get()
        {            
            var departments = _departmentService.SelectAll();
            return new JsonResult(new
            {
                Error = false,
                Message = "Success",
                Departments = departments
            }) ;
        }
    }
}

Note : your url in the browser should be like <baseurl>/api/Department

Required output

{
  "error": false,
  "message": "Success",
  "departments": [
    {
      "departmentID": 1,
      "departmentName": "Computer Science"
    },
    {
      "departmentID": 2,
      "departmentName": "Urdu"
    },
    {
      "departmentID": 3,
      "departmentName": "Pashto"
    }
  ]
}