Entity Framework Core Üzerinde DBSet Olmadan Raw SQL Query Yazma

Yazar: borsoft · 20 Mart 2020

Selamlar,

Bu makalede, Entity Framework Core ile çalışırken, bazen Linq ile yazılmayacak kadar complex sorgular ile uğraşmanız gerekebilir. Bu gibi durumlarda kodların DB’de olmaması, test senaryoları ve Debug yapılamaması yüzünden, View veya Procedurler tercih edilmeyebilir. Bu durumda, Entity Framework üzerinde Raw Sql Query yazmak, doğru bir seçenek olabilir.

İşin zor kısmı, bu makale itibari ile artık Entity Framework Core 3.1’de DBSet olmadan RawSql yazmak mümkün değildir. Kısaca artık dbData.Database.SqlQuery<SomeModel> kaldırılmış, onun yerine her zaman kullanılması zorunlu olan DBSet<> getirilmiştir. dbData.Product.FromSql(“SQL SCRIPT”) gibi.

İşte bu makalede, Sql tarafında View, Procedure veya yeni bir tablo oluşturmadan, nasıl custom sorgular yazılabileceğini hep beraber inceleyeceğiz.

Öncelikle çekilmek istenen sorgu aşağıdaki gibidir: Amaç, Customer servisi altında Northwind database’i üzerinde en fazla miktarda sipariş veren 5 müşterinin listesinin çekilmesidir. “GetCustomerOrderByRawSql()” methodunda, 3 farklı tablodan gruplanıp,sıralanarak bir sorgu çekilmektedir. İlgili sorgunun sonucu, projede “Top5OrderModel” modelidir. Bu, bir çeşit bizim ViewModelimizdir. Aşağıdaki CustomService, geriye ServiceResponse model dönmektedir. ServiceResponse modelinin List property’sine, sorgudan dönen “List<Top5OrderModel>()” değeri atanmaktadır.

Services/Customers/CustomerService:

namespace Services.Customers
{
    public class CustomerService : ICustomerService
    { 
        private readonly IRepository<DB.Entities.Top5OrderModel> _customerOrderRepository;
        public CustomerService(IRepository<DB.Entities.Top5OrderModel> customerOrderRepository)
        {           
            _customerOrderRepository = customerOrderRepository;
        }

        public ServiceResponse<Top5OrderModel> GetCustomerOrderByRawSql()
        {
            string sqlQuery = @"select top 5 cus.CustomerID, cus.CompanyName, ord.ShipCountry,sum(ordd.Quantity) as Total  from [dbo].[Orders] as ord
                                inner join [dbo].[Customers] as cus on ord.CustomerID=cus.CustomerID
                                inner join [dbo].[Order Details] as ordd on ord.CustomerID=cus.CustomerID
                                group by ShipCountry,CompanyName,cus.CustomerID
                                order by Total desc";
            var result = _customerOrderRepository.GetSql(sqlQuery);
            var response = new ServiceResponse<Top5OrderModel>(null);
            response.List = result.ToList();
            return response;
        }
    }
}

namespace Services.Customers

{

public class CustomerService : ICustomerService

{

private readonly IRepository<DB.Entities.Top5OrderModel> _customerOrderRepository;

public CustomerService(IRepository<DB.Entities.Top5OrderModel> customerOrderRepository)

{

_customerOrderRepository = customerOrderRepository;

}

public ServiceResponse<Top5OrderModel> GetCustomerOrderByRawSql()

{

string sqlQuery = @"select top 5 cus.CustomerID, cus.CompanyName, ord.ShipCountry,sum(ordd.Quantity) as Total from [dbo].[Orders] as ord

inner join [dbo].[Customers] as cus on ord.CustomerID=cus.CustomerID

inner join [dbo].[Order Details] as ordd on ord.CustomerID=cus.CustomerID

group by ShipCountry,CompanyName,cus.CustomerID

order by Total desc";

var result = _customerOrderRepository.GetSql(sqlQuery);

var response = new ServiceResponse<Top5OrderModel>(null);

response.List = result.ToList();

return response;

}

Services/Customers/ICustomerService: Aşağıda görüldüğü gibi CustomerService, GetCustomerOrderByRawSql() methoduna zorlanmıştır.

using Core.ApiResponse;
using Core.Filters.Customers;
using Core.Models;
using System;
using System.Collections.Generic;
using System.Text;
using DB.Entities;

namespace Services.Customers
{
    public interface ICustomerService : IEntityService<CustomerModel>
    {        
        ServiceResponse<Top5OrderModel> GetCustomerOrderByRawSql();
    }
}

using Core.ApiResponse;

using Core.Filters.Customers;

using Core.Models;

using System;

using System.Collections.Generic;

using System.Text;

using DB.Entities;

namespace Services.Customers

{

public interface ICustomerService : IEntityService<CustomerModel>

{

ServiceResponse<Top5OrderModel> GetCustomerOrderByRawSql();

}

Servisden dönen JsonResult:

Repository/GeneralRepository: Servis tarafından çağrılan GetSql() methodu, aslında FromSqlRaw() methodunun karşılığıdır.

namespace Dashboard.Repository
{
    public class GeneralRepository<T> : IRepository<T> where T : BaseEntity
    {
        private readonly VbtContext _context;
        private DbSet<T> _entities;
        public GeneralRepository(VbtContext context)
        {
            _context = context;
            _entities = context.Set<T>();
        }

        public IEnumerable<T> GetSql(string sql)
        {
            return Entities.FromSqlRaw(sql).AsNoTracking();
        }

        /// <summary>
        ///     Entities
        /// </summary>
        protected virtual DbSet<T> Entities => _entities ?? (_entities = _context.Set<T>());
    }
}

namespace Dashboard.Repository

{

public class GeneralRepository<T> : IRepository<T> where T : BaseEntity

{

private readonly VbtContext _context;

private DbSet<T> _entities;

public GeneralRepository(VbtContext context)

{

_context = context;

_entities = context.Set<T>();

}

public IEnumerable<T> GetSql(string sql)

{

return Entities.FromSqlRaw(sql).AsNoTracking();

}

/// <summary>

/// Entities

/// </summary>

protected virtual DbSet<T> Entities => _entities ?? (_entities = _context.Set<T>());

}

Core/ApiResponse/ServiceResponse: Tüm servisler, aşağıda görüldüğü gibi ServiceResponse tipinde ortak bir model döner. Burada önemli olan “List” List of <T> tipinde Top5OrderModel dönülür. Tek bir model dönülse, Entity tipinde dönülmektedir. Token, RefreshToken, CreatedTokenTime authentication amaçlı kullanılan alanlardır.

  [Serializable]
    public class ServiceResponse<T> : IServiceResponse<T>
    {
        public bool HasExceptionError { get; set; }

        [JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
        public string ExceptionMessage { get; set; }

        public IList<T> List { get; set; }

        [JsonProperty]
        public T Entity { get; set; }

        public bool IsSuccessful { get; set; }

        public string Token { get; set; }

        public string RefreshToken { get; set; }

        public long CreatedTokenTime { get; set; }

        public ServiceResponse(HttpContext context)
        {
            if (context?.Items["token"] != null)
            {
                Token = (string)context.Items["token"];
            }

            if (context?.Items["refreshToken"] != null)
            {
                RefreshToken = (string)context.Items["refreshToken"];
            }

            if (context?.Items["createdTokenTime"] != null)
            {
                CreatedTokenTime = (long)context.Items["createdTokenTime"];
            }
        }
    }

[Serializable]

public class ServiceResponse<T> : IServiceResponse<T>

{

public bool HasExceptionError { get; set; }

[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]

public string ExceptionMessage { get; set; }

public IList<T> List { get; set; }

[JsonProperty]

public T Entity { get; set; }

public bool IsSuccessful { get; set; }

public string Token { get; set; }

public string RefreshToken { get; set; }

public long CreatedTokenTime { get; set; }

public ServiceResponse(HttpContext context)

{

if (context?.Items["token"] != null)

{

Token = (string)context.Items["token"];

}

if (context?.Items["refreshToken"] != null)

{

RefreshToken = (string)context.Items["refreshToken"];

}

if (context?.Items["createdTokenTime"] != null)

{

CreatedTokenTime = (long)context.Items["createdTokenTime"];

}

Core/Models/Customer/Top5OrderModel: Sorgu sonucu ihtiyac duyulan ViewModel, aşağıdaki gibidir. Ama gerçekte, DB tarafında böyle bir tablo bulunmamaktadır. Ve oluşturulmayacaktır.

using Core;
using System;
using System.Collections.Generic;
using System.Text;

namespace DB.Entities
{
    public class Top5OrderModel : BaseEntity
    {
        public string CustomerId { get; set; }
        public string CompanyName { get; set; }
        public string ShipCountry { get; set; }
        public int Total { get; set; }
    }
}

using Core;

using System;

using System.Collections.Generic;

using System.Text;

namespace DB.Entities

{

public class Top5OrderModel : BaseEntity

{

public string CustomerId { get; set; }

public string CompanyName { get; set; }

public string ShipCountry { get; set; }

public int Total { get; set; }

}

Şimdi sıra geldi, bu modeli DBFirst ile oluşan Context’de tanımlamaya. Tabi ki DB’den komut ile oluşan NorthwindContex’e, bir tanımlama yapılmayacaktır. Çünkü yapılan tanımlama, tekrar komutun çalıştırılması ile ezilecektir.

- İlgili Komut: “dotnet ef dbcontext scaffold “Server=localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer –output-dir Entities –force“

DB/PartialEntites/DBContext: Aşağıda görüldüğü gibi “DBContext“, NorthwindContext’den türetilmiştir. Böylece bu kod üzerinde yapılan değişiklikler, “dotnet ef dbcontext scaffold” komutu ile yeniden oluşan DBContext ve DBSetler ile üzerine ezilmemiş olunur.

“public DbSet<Top5OrderModel> Top5OrderModel { get; set; }” : Top5OrderModel model sanki DB’den var olan bir tablo gibi tanımlanır. Bu aslında, sahte bir modeldir. Ne DB’de yaratılacaktır. Ne de zaten vardır. Tek amacı, Raw Sql şeklinde yazılan sorgudan dönen soncu, karşılamaktır.
*“modelBuilder.Entity<Top5OrderModel>(entity => { entity.HasNoKey(); })” : OnModelCreating() methodunda, yaratılan Top5OrderModel DBSet’inin bir keyinin olmadığının tanımlanması gerekmektedir. Aksi takdirde Index Hatası alınır.
- Not: HasNoKey tanımlaması, tüm Viewlar için de yapılmalıdır.

using Core.Models;
using DB.Entities;
using DB.Extensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Text;

namespace DB.Entities
{
    /*Run This Command :
     * dotnet ef dbcontext scaffold "Server=localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities --force*/
    public class DBContext : NorthwindContext
    {
        public DbSet<Top5OrderModel> Top5OrderModel { get; set; }

        public DBContext()
        {
        }

        public DBContext(DbContextOptions<NorthwindContext> options)
            : base(options)
        {
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Top5OrderModel>(entity =>
            {
                entity.HasNoKey();
            });            
        }
    }
}

using Core.Models;

using DB.Entities;

using DB.Extensions;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.Logging;

using System;

using System.Collections.Generic;

using System.Text;

namespace DB.Entities

{

/*Run This Command :

* dotnet ef dbcontext scaffold "Server=localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities --force*/

public class DBContext : NorthwindContext

{

public DbSet<Top5OrderModel> Top5OrderModel { get; set; }

public DBContext()

{

}

public DBContext(DbContextOptions<NorthwindContext> options)

: base(options)

{

}

protected override void OnModelCreating(ModelBuilder modelBuilder)

{

base.OnModelCreating(modelBuilder);

modelBuilder.Entity<Top5OrderModel>(entity =>

{

entity.HasNoKey();

});

}

Şimdi sıra geldi, CustomerService’indeki “Top5CustomerOrder()” methodunu, CustomerController’dan çağırmaya.

[ApiController]
[Route("[controller]")]
public class CustomerController : ControllerBase
{  
        public CustomerController(ICustomerService customerService)
        {
            _customerService = customerService;            
        }
        
        [HttpGet("Top5CustomerOrder")]
        public ServiceResponse<Top5OrderModel> Top5CustomerOrder()
        {
            var response = new ServiceResponse<Top5OrderModel>(HttpContext);
            response.List = _customerService.GetCustomerOrderByRawSql().List.ToList();
            response.IsSuccessful = true;
            response.Count = response.List.Count;
            return response;
        }
}

[ApiController]

[Route("[controller]")]

public class CustomerController : ControllerBase

{

public CustomerController(ICustomerService customerService)

{

_customerService = customerService;

}

[HttpGet("Top5CustomerOrder")]

public ServiceResponse<Top5OrderModel> Top5CustomerOrder()

{

var response = new ServiceResponse<Top5OrderModel>(HttpContext);

response.List = _customerService.GetCustomerOrderByRawSql().List.ToList();

response.IsSuccessful = true;

response.Count = response.List.Count;

return response;

}

Bu makalede, farklı tablolardan Raw Sql Query ile data çeken bir servisin, .Net Core Entity 3.1’de nasıl yazılabileceğini hep beraber inceledik. Normal şartlar altında pek de tercih edilmemesi gereken bu yöntem, çok kompleks querylerde linq yazmak yerine kullanılabilir. Son olarak bunun yerine, Sql tarafında bir View oluşturulup, DB First ile oluşturulan bu View, projeye “DBSet<>” olarak generate edilebilir. Ve çekilmek istenen query, bu view üzerinden yapılabilir. Ama tabi ki bu da, hem Database katmanında, hem de kod tarafında ayrı ayrı çalışma yapılmasını gerektirmektedir. Eğer bir rapor projesi yazılıyor ise, herbir kompleks query için, ayrı ayrı View oluşturmak hem çok mantıklı değil hem de performans açısından tercih edilmeyebilir.

Geldik bir makalenin daha sonuna. Yeni bir makalede görüşmek üzere, hepinize hoşçakalın.

Source:

Etiketler: .Net Core 3.1 DBSet Entity Framework Core Raw Sql Query Repository service

Bunlar da hoşunuza gidebilir...

Serdar dedi ki:

18 Mayıs 2020, 22:54

var result = _customerOrderRepository.GetSql(sqlQuery);

bu satırın kodunu göremedim bu result nasul dönüyor

Cevapla
- borsoft dedi ki:
  
  28 Mayıs 2020, 16:29
  
  Öncelikle Selamlar,
  
  Methodun result tipi budur ==> public ServiceResponse GetCustomerOrderByRawSql()
  Ve aşağıda görüldüğü gibi “ServiceResponse” tipinde bir sonuş dönülmüştür. “List” ServiceResponse tipinin bir property’sidir ve result’ı almaktadır.
  Result tek başına değil response sınıfının bir property’si olarak geriye dönülmektedir.
  
  Iyi çalışmalar.
  
  var response = new ServiceResponse(null);
  response.List = result.ToList();
  return response;
  
  Cevapla
  - Caglar dedi ki:
    
    31 Mayıs 2020, 23:15
    
    Hocam öncelikle elinize sağlık
    
    _customerOrderRepository.GetSql metodu nun içeriğini paylaşırsanız daha açıklayıcı olacaktır.
    
    Teşekkürler
    
    Cevapla
    - borsoft dedi ki:
      
      24 Haziran 2020, 15:50
      
      Selamlar Çağrı,
      
      Teşekkürler. Bildiğin eklemeyi unutmuşum :)
      GeneralRepository() sınıfını da makale içine ekledim.
      
      İyi çalışmalar.
      
      Cevapla
eray dedi ki:

28 Haziran 2020, 16:37

selamlar, where condition ların olacağı bir blok eklemeyi düşünürsek bunun içerisinde parametrik bir şekilde condition lar eklenebilir mi? veya Expression<Func> gibi yapılar kullanabilir mi?

Teşekkürler

Cevapla
- borsoft dedi ki:
  
  28 Temmuz 2020, 10:45
  
  Selam Eray,
  Evet neden olmasın. SQL Raw zaten string bir query. Buna Func yazılarak conditionlar eklemek mümkün. Hali hazırda zaten şunun gibi kullanıyorum.
  
  Örnekler: “var result = _customMenuRepository.GetSql($”SELECT * FROM [dbo].[fn_GetMenu]({userId},{isAdmin}) ORDER BY OrderNumber”);”
  
  iyi çalışmalar.
  
  Cevapla
Serkan dedi ki:

5 Ağustos 2020, 20:38

işin ilginç tarafı aynı şekilde tanımlanmış şu _context.GetNotDeletedIds.FromSqlRaw(“call sp_notdeletedids({0}, {1})”, 1, 3).ToListAsync();
kod çalışmakta

Cevapla
Caglar dedi ki:

4 Eylül 2020, 15:36

Merhaba Hocam

protected virtual DbSet Entities => _entities ?? (_entities = _context.Set());
bunu neden kullanıyoruz ?
Zaten ctor da tabloyu _entities e eşleştirdik.

Bu kullanımın amacı nedir ?

Teşekkürler

Cevapla
- borsoft dedi ki:
  
  6 Eylül 2020, 01:02
  
  Selamlar,
  Tanımlanan _entites private. Sadece içerde kullanılan bir değişken. Ama Entities property. Dışarıya açık.
  
  İyi çalışmalar.
  
  Cevapla
  - Çağlar dedi ki:
    
    10 Eylül 2020, 06:24
    
    Teşekkürler. Elinize emeğinize sağlık.
    
    Cevapla
Turgay dedi ki:

14 Nisan 2021, 13:44

Merhaba Hocam
VIEW yazmak performansı neden/ne derece etkiler. Ayrıca bu sorgu müşteri bazında değişiyorsa (tablo yapısı aynı olmasına rağmen, tablo adları farklı ise) VIEW den yönetmek daha kolay olmaz mı? Bu durumnda sql string parametrik gönderilebilir, procedure veya function yazılabilir. Siz olsanız nasıl ilerlerdiniz?
Diğer bir soruda , sql sorgu gönderildiğinde entity ve buna bağlı prop lar dinamik olarak üretilebilir mi?
Özellikler bir rapor uygulamasında her müşterinin ihtiyacı farklı olduğu için dinamik bir yapıya ihtiyaç var.
Bu şekilde bir yapı kurmak mümkün mü?
Değerli bilgileriniz için teşekkürler.

Cevapla
Oğulcan dedi ki:

3 Aralık 2021, 18:19

Merhaba,

Bu şekilde iki tane dbcontext oluşturduğumuz zaman, uygulamamıza da iki tane context inject etmeliyiz değil mi ?

Cevapla
Murat Saygılı dedi ki:

6 Şubat 2022, 22:09

Merhaba hocam,

.FromSqlRaw(queryText).Where(burada bazı parametrik filtreler).Select(new object).Tolist() olarak deneme yaptım ve filtreler değiştikçe gelen verilerin de değiştiğini gördüm yani çalıştırabildim. Benim merak ettiğim acaba verileri dbden çektikten sonra mı filtreliyor yoksa benim where bloğunda belirttiğim filtrelerle beraber mi veritabanında sorgulama yapıyor biliyor musunuz?

Cevapla
- borsoft dedi ki:
  
  7 Şubat 2022, 01:03
  
  Selam,
  Hayır SQL Query bağlı olarak filitrelenen verileri DB’den çekiyor. Yani Önce hepsini çekip, sonra filitrelemiyor. Zaten böyle birşeyi asla düşünmeyin :)
  
  Cevapla
  - Murat Saygılı dedi ki:
    
    7 Şubat 2022, 12:56
    
    Teşekkürler bu kadar iyi çalışması şaşırttı beni o yüzden merak ettim elinize sağlık :)
    
    Cevapla
Selim dedi ki:

22 Haziran 2022, 11:46

Konu ile alakalı interneti arşınlarken, çözümü bir Türkçe kaynaktan bulmak gerçekten çok güzel oldu. Teşekkür ederim, elinize sağlık. Sadece bir eleştiri yapmak istiyorum. Örneğe, generic repository, service response gibi classlar ve metotlarını eklemenizin konuyu biraz karmaşık hale getirdiğini düşünüyorum.

DBContext kısmı sizin yaptığınız ile aynı olacak şekilde, amaca uygun bir clas yazmak ve sorgu sonucu dönecek sütunlara sınıfın propertlerine uygun alias vermek konuyu özetliyor aslında. Saygılar, iyi günler.

public class QueryModel
{
public string Result { get; set; }
}

List list = dbContext.QueryModel.FromSqlRaw(“SELECT name as Result FROM sys.databases WHERE HAS_DBACCESS(name) = 1”).ToList();

Cevapla
Samet dedi ki:

2 Haziran 2023, 10:53

Merhaba,

Yazdığınız yazıda bir DTO oluşturup o DTO’ya uygun şekilde sorgu yazıp veri çekmek gerekiyor.
DTO oluşturmadan object veya dynamic tipinde veri çekmek mümkün mü?

Cevapla
- borsoft dedi ki:
  
  5 Haziran 2023, 13:59
  
  .Net 8.0 ile artık mümkün olacak????
  
  Cevapla

Entity Framework Core Üzerinde DBSet Olmadan Raw SQL Query Yazma

Bunlar da hoşunuza gidebilir...

18 Cevaplar

Bir cevap yazın Cevabı iptal et

About

Son Yazılar

Kategoriler

Son Yorumlar

Arşivler

Entity Framework Core Üzerinde DBSet Olmadan Raw SQL Query Yazma

Bunlar da hoşunuza gidebilir...

Angular 6 ve SignalR 2 ile Web’de Mobil Etkileşimli Hafıza Oyunu Yapma Part 3

Angular 6 ve SignalR 2 ile Web’de Mobil Etkileşimli Hafıza Oyunu Yapma Part 1

Ionic, SignalR ve TypeScript ile Mobile Uygulama Geliştirme Bölüm 4

18 Cevaplar

Bir cevap yazın Cevabı iptal et

About

Son Yazılar

Kategoriler

Son Yorumlar

Arşivler