<\/div>\n

Merhaba,<\/p>\n

G\u00fcn\u00fcm\u00fczde bir\u00e7ok uygulamada veritaban\u0131 i\u015flemleri i\u00e7in yayg\u0131n olarak ORM(Object Relational Mapping) yakla\u015f\u0131m\u0131 tercih ediliyor olsa da bir\u00e7ok k\u00fc\u00e7\u00fck ve orta \u00f6l\u00e7ekli \u015firket taraf\u0131ndan ve hatta geleneksel olarak radikal de\u011fi\u015fiklikleri kald\u0131ramayacak kadar k\u00f6kl\u00fc framework’lere sahip olan b\u00fcy\u00fck \u015firketler taraf\u0131ndan da Ado.NET’e dayal\u0131 olarak uygulama geli\u015ftirildi\u011fine de \u015fahit olabilmekteyiz. Tecr\u00fcbeyle sabittir ki, Ado.NET ile ger\u00e7ekle\u015ftirilen veritaban\u0131 \u00e7al\u0131\u015fmalar\u0131, s\u00fcrekli de\u011fi\u015fiklik g\u00f6steren proje ihtiya\u00e7lar\u0131 neticesinde ka\u00e7\u0131n\u0131lmaz olarak karma\u015f\u0131k hale gelebilmekte ve bu karma\u015f\u0131kl\u0131\u011f\u0131 y\u00f6netmek ve hatta de\u011fi\u015fiklikler i\u00e7in programc\u0131n\u0131n dikkat becerisine g\u00fcvenmek gerekmektedir. Halbuki bu karma\u015f\u0131kl\u0131\u011f\u0131 m\u00fcmk\u00fcn mertebe ORM gibi dinamik bir \u00e7\u00f6z\u00fcmle y\u00f6netebilmek ve olu\u015fturulacak sorgular\u0131 programc\u0131n\u0131n dikkatinden soyutlayabilmek s\u00fcre\u00e7 a\u00e7\u0131s\u0131ndan \u00e7\u00f6z\u00fcmlerin en idealidir kanaatindeyiz. \u0130\u015fte b\u00f6yle bir durumda veritaban\u0131 sorgular\u0131n\u0131n bir nesne \u00fczerinden temsil edilerek geli\u015ftiriciden ba\u011f\u0131ms\u0131z bir \u015fekilde y\u00f6netilebilmesini sa\u011flayan Query Object Tasar\u0131m Deseni<\/em>ni kullanabilir ve ideal \u00e7\u00f6z\u00fcme bir ad\u0131m daha yak\u0131nla\u015farak \u00e7al\u0131\u015fmalar\u0131m\u0131za devam edebiliriz.<\/p>\n

Neden Query Object Design Pattern’\u0131 Kullanmal\u0131y\u0131z?<\/h3>\n
Yukar\u0131da giri\u015f paragraf\u0131nda bahsedildi\u011fi \u00fczere uygulama i\u00e7erisinde ORM kullan\u0131lm\u0131yorsa ve kod i\u00e7erisinde SQL sorgular\u0131 olu\u015fturulacaksa Query Object Design Pattern ideal bir \u00e7\u00f6z\u00fcm sa\u011flamaktad\u0131r. \u00c7\u00fcnk\u00fc, geli\u015ftiriciler kullan\u0131lacak veritaban\u0131na ve o veritaban\u0131n\u0131n sorgulama diline tam hakim olamayabilirler. Yahut veritaban\u0131na hakim olsalar bile uygulaman\u0131n veritaban\u0131 \u015femas\u0131n\u0131\/mant\u0131\u011f\u0131n\u0131 bilmeleri\/\u00e7\u00f6zmeleri gerekmektedir. Hangi tabloda hangi verilerin tutuldu\u011fu, uygulama konfig\u00fcrasyonlar\u0131n\u0131n nerede tan\u0131mland\u0131\u011f\u0131 veya tablolar aras\u0131 ili\u015fkilerin nas\u0131l olmas\u0131 gerekti\u011fi vs. gibi bir\u00e7ok durum geli\u015ftiricileri ilgilendirecektir. Oysa bir geli\u015ftiricinin i\u015fi m\u00fcmk\u00fcn mertebe elindeki malzemelerle ortaya i\u015f koyabilmek ve bunu yay\u0131na s\u00fcrebilmektir. Bu kadar bilgiyi \u00f6\u011frenmek i\u00e7in verece\u011fi vakit \u015firket i\u00e7in olduk\u00e7a maliyetli bir netice ortaya koyabilir. \u0130\u015fte, Quert Object tasar\u0131m deseni bu ve bunlara benzer durumlar\u0131n minimum bilgi gereksinimi ile \u00e7\u00f6z\u00fclmesini sa\u011flayacak ve geli\u015ftiricinin direkt olarak i\u015fe odaklanmas\u0131n\u0131 sa\u011flayacakt\u0131r.<\/p>\n
Quert Object, bir veritaban\u0131 sorgusunu temsil eden nesne olarak anla\u015f\u0131labilir.<\/em><\/p><\/blockquote>\n
Quert Object tasar\u0131m deseni sayesinde nesne olarak geli\u015ftirilen sorgular ihtiya\u00e7 dahilinde kullan\u0131ld\u0131ktan sonra tekrar ihtiyaca binaen depolanabilmektedir. Yap\u0131n\u0131n g\u00fczel yanlar\u0131ndan biriside buras\u0131d\u0131r. Ayr\u0131ca bu modellemenin ana yararlar\u0131ndan birisinin uygulamay\u0131 veritaban\u0131 sorgu dilinden tamamen soyutlamas\u0131d\u0131r diyebiliriz. Bunu yaz\u0131m\u0131z\u0131n devam\u0131nda daha net bir \u015fekilde inceliyor olaca\u011f\u0131z.<\/p>\n
Query Object Design Pattern Diyagram\u0131n\u0131 \u0130nceleyelim<\/h5>\n
$\"Query$ <\/a>Yukar\u0131daki diyagram \u015femas\u0131na g\u00f6z atarsan\u0131z e\u011fer ihtiya\u00e7 duyulan SQL sorgusunu ve o sorguda kullan\u0131lacak kriterleri temsil eden nesnelerin kullan\u0131ld\u0131\u011f\u0131 bir tasar\u0131m g\u00f6rmekteyiz. Modelleme, tasar\u0131m a\u00e7\u0131s\u0131ndan bu kadar basittir.<\/p>\n
Bu tasar\u0131m neticesinde olu\u015fturulan Query Object pattern sayesinde, nesnelerin yorumlanarak SQL \u00e7\u0131kt\u0131s\u0131 olu\u015fturan bir model ortaya koyulmaktad\u0131r. E\u011fer ki burada nesne yerine bir veriyi yorumlayarak ba\u015fka bir \u00e7\u0131kt\u0131 olu\u015fturuyor olsayd\u0131k bu Interpreter tasar\u0131m deseni olacakt\u0131. Bu benzerlikten dolay\u0131 kimi programc\u0131lar, Query Object pattern’\u0131n ger\u00e7ek bir tasar\u0131m deseni olmad\u0131\u011f\u0131, Interpreter pattern’\u0131n sadece farkl\u0131 bir modellemesi oldu\u011fu yorumunda bulunmaktad\u0131rlar.<\/u><\/em> Do\u011frusu bu yorumda hakl\u0131l\u0131k paylar\u0131 yokta de\u011fildir! \u00c7\u00fcnk\u00fc Query Object tasar\u0131m deseni sayesinde verilere g\u00f6re de uygun yorumlamalar yaparak \u00e7e\u015fitli SQL \u00e7\u0131kt\u0131lar\u0131 da sunulabilmektedir.<\/p>\n
Quert Object, bir yorumlay\u0131c\u0131d\u0131r, yani kendisini bir SQL sorgusuna d\u00f6n\u00fc\u015ft\u00fcrebilen bir nesne yap\u0131s\u0131d\u0131r.<\/p>\n
\nMartin Fowler\n<\/div>\n
<\/em>\n<\/p><\/blockquote>\n
Query Object nesneleri tasarlan\u0131rken esnek yap\u0131lar kurar ve b\u00f6ylece veritaban\u0131 sistemlerinin her \u00f6zelliklerini kullan\u0131labilir hale getiririz. B\u00f6ylece efektif kullan\u0131m sa\u011flayabilir ve sorgulardaki komplekslik oran\u0131n\u0131 artt\u0131rabiliriz. Ayr\u0131ca, Query Object’ler ile olu\u015fturulacak SQL \u00e7\u0131kt\u0131lar\u0131n\u0131, \u00e7al\u0131\u015f\u0131lacak veritaban\u0131 sistemine g\u00f6re farkl\u0131 \u015fekilde elde edebiliriz. Bu farkl\u0131l\u0131klar sayesinde birden fazla veritaban\u0131 sistemi ile \u00e7al\u0131\u015fabiliriz.<\/p>\n
\u00d6zellikle karma\u015f\u0131k sorgulama s\u00fcre\u00e7lerinde bu pattern sayesinde olduk\u00e7a performansl\u0131 bir \u00e7al\u0131\u015fma sergilenmi\u015f olacakt\u0131r. Nihayetinde koda g\u00f6m\u00fcl\u00fc kompleks bir sorgunun s\u00fcre\u00e7teki ihtiya\u00e7lara istinaden modifiye edilmesi ve bak\u0131m\u0131 olduk\u00e7a maliyetli olacakt\u0131r. Lakin ihtiya\u00e7 do\u011frultusunda olas\u0131 de\u011fi\u015fiklikleri en ba\u015fta \u00f6ng\u00f6rerek bu tasar\u0131m\u0131n temellerinin at\u0131lmas\u0131 b\u00fcy\u00fck avantaj sa\u011flayacakt\u0131r.<\/p>\n
Ne Zaman Kullan\u0131lmaz!<\/h3>\n
Bir olgunun art\u0131s\u0131 oldu\u011fu kadar eksisi de olabilmektedir. Dolay\u0131s\u0131yla Query Object nesnelerinin getirisi kadar g\u00f6t\u00fcr\u00fcs\u00fc de mevcuttur. Buradaki g\u00f6t\u00fcr\u00fc ilgili nesnelerin olu\u015fturulma maliyeti, bir ba\u015fka deyi\u015fle zahmetidir. Evet, bir Query Object olu\u015fturmak\/tasarlamak olduk\u00e7a zahmetlidir. Bundan dolay\u0131; e\u011fer ki \u00e7al\u0131\u015f\u0131lan projede herhangi bir ORM teknolojisi kullan\u0131l\u0131yorsa bu pattern’\u0131n uygulanmas\u0131 kesinlikle tavsiye edilmemektedir. Ha nerede ve ne zaman kullan\u0131laca\u011f\u0131n\u0131 soruyorsan\u0131z e\u011fer bu sorunun cevab\u0131n\u0131 yukar\u0131daki sat\u0131rlarda verdi\u011fim kanaatindeyim \ud83d\ude42<\/p>\n
Avantajlar\u0131<\/h3>\n
\n
\u0130htiya\u00e7 duyulan SQL sorgusunu parametreli fonksiyonaliteler e\u015fli\u011finde bar\u0131nd\u0131r\u0131r. B\u00f6ylece gelen parametrelere g\u00f6re dinamik olarak SQL’i olu\u015fturabilir.<\/li>\n
Veritaban\u0131 sorgu dilini geli\u015ftiriciden tamamen soyutlar, ba\u011f\u0131ms\u0131z hale getirir.<\/li>\n
Bir\u00e7ok veritaban\u0131n\u0131 desteklemektedir.<\/li>\n<\/ul>\n
\u00d6rnek Tasar\u0131m Modellemesi & Kullan\u0131m<\/h3>\n
Bu pattern’\u0131 net bir \u015fekilde \u00f6rneklendirebilmek i\u00e7in \u00f6ncelikle bir developer’\u0131n Ado.NET ile koda g\u00f6m\u00fcl\u00fc sorgularla \u00e7al\u0131\u015f\u0131rken ne gibi bir atmosferde oldu\u011funu sim\u00fcle ederek bu yakla\u015f\u0131m\u0131n gereklili\u011fini ortaya koymaya \u00e7al\u0131\u015fal\u0131m. Bunun i\u00e7in a\u015fa\u011f\u0131daki gibi tasarlanarak Ado.NET temelleri at\u0131lan bir s\u0131n\u0131f \u00fczerinden ihtiya\u00e7lar\u0131n giderildi\u011fini varsayarak ba\u015flayal\u0131m:<\/p>\n
\n
\r\n public class DALContext\r\n {\r\n readonly static SqlConnection _connection;\r\n readonly static object _object = new();\r\n static DALContext()\r\n {\r\n lock (_object)\r\n _connection = new("Server=localhost, 1433;Database=QueryObjectDesignPatternDB;User ID=SA;Password=***");\r\n }\r\n async Task<SqlConnection> GetOpenConnectionAsync()\r\n {\r\n if (_connection.State == ConnectionState.Closed)\r\n await _connection.OpenAsync();\r\n\r\n return _connection;\r\n }\r\n public async Task CloseConnectionAsync()\r\n {\r\n if (_connection.State == ConnectionState.Open)\r\n await _connection.CloseAsync();\r\n }\r\n public async Task<SqlDataReader> ExecuteQueryAsync(string query)\r\n {\r\n using SqlCommand command = new(query, await GetOpenConnectionAsync());\r\n SqlDataReader dr = await command.ExecuteReaderAsync();\r\n return dr;\r\n }\r\n }\r\n<\/pre>\n<\/div>\n
G\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere Ado.NET mimarisi e\u015fli\u011finde veritaban\u0131n\u0131 sorgulamam\u0131z\u0131 sa\u011flayacak olan temel i\u015flemlerin icra edildi\u011fi ‘DALContext’ isimli bir s\u0131n\u0131f\u0131m\u0131z mevcuttur. Bu s\u0131n\u0131f\u0131 kullanarak a\u015fa\u011f\u0131daki gibi veritaban\u0131ndaki ‘Products’ tablosuna sorgular g\u00f6nderilebildi\u011fini varsayal\u0131m.
$\"Query$ <\/a>
‘Products’ tablosu…<\/em><\/span><\/p><\/div><\/p>\n
\n
\r\nDALContext context = new();\r\nSqlDataReader dr = await context.ExecuteQueryAsync("SELECT * FROM Products");\r\nwhile (await dr.ReadAsync())\r\n Console.WriteLine($"Id : {dr["Id"]}\\tName : {dr["Name"]}\\tStock : {dr["Stock"]}\\tPrice : {dr["Price"]}");\r\nawait context.CloseConnectionAsync();\r\n<\/pre>\n<\/div>\n
Yukar\u0131daki kodu derleyip \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131m\u0131zda a\u015fa\u011f\u0131daki gibi veritaban\u0131ndan istenilen verilerin elde edildi\u011fini g\u00f6r\u00fcyor olaca\u011f\u0131z. $\"Query$ <\/a>\u015eimdi bir developer bu ve benzeri bir sorguyu yazarken insani halden \u00f6t\u00fcr\u00fc a\u015fa\u011f\u0131daki gibi hata(lar) yapma ihtimali olas\u0131 m\u0131d\u0131r?
\nSELECT ,<\/del> FROM Products<\/strong><\/code> \nSELECT Name, Fiyat<\/del> FROM Products<\/strong><\/code> \nSELECT Name, Fiyat<\/del> FROM Urunler<\/del><\/strong><\/code> \nTabi ki de olas\u0131d\u0131r \ud83d\ude42 Bu hatalar\u0131n olas\u0131 olmalar\u0131n\u0131n nedenleri \u00fcst paragraflarda da bahsedildi\u011fi gibi k\u00e2h developer’\u0131n anl\u0131k bilinciyle ilgili dikkat da\u011f\u0131n\u0131kl\u0131\u011f\u0131 k\u00e2h ilgili veritaban\u0131 diline hakim olamamas\u0131 gibi t\u00fcrl\u00fc sebepler olabilir. Peki, sorgular\u0131m\u0131z bu kadar m\u0131?<\/em> Tabi ki de hay\u0131r! Temel d\u00fczeyde bir uygulamada bile gelecek verilerin filtrelendi\u011fi \u015fartl\u0131 sorgular ya da farkl\u0131 tablolar\u0131n birle\u015ftirildi\u011fi join’li sorgular ka\u00e7\u0131n\u0131lmaz olarak kullan\u0131lmaktad\u0131r. Dolay\u0131s\u0131yla bu tarz sorgular\u0131 developer’\u0131n sorumlulu\u011funa b\u0131rak\u0131rsak komplekslik artt\u0131k\u00e7a olas\u0131 hata oranlar\u0131 da muhtemeldir ki artacakt\u0131r.<\/p>\n
`\u0130\u015fte b\u00f6yle bir durumda sorgular\u0131 developer odakl\u0131 hatalardan ar\u0131nd\u0131rabilmek i\u00e7in olu\u015fturulma sorumluluklar\u0131n\u0131 developerlardan soyutlayarak otomatize hale getirebilmek i\u00e7in Query Object pattern’\u0131n\u0131 uygulamam\u0131z gerekmektedir. \u015eimdi gelin ad\u0131m ad\u0131m bu pattern’\u0131n uygulanmas\u0131n\u0131 inceleyelim.<\/p>\n`
`\n`Ad\u0131m 1<\/em><\/strong> \n\u0130lk olarak bir sorgudaki kriterleri\/\u015fartlar\u0131(where) tarif edecek olan s\u0131n\u0131f\u0131 in\u015fa edelim.<\/p>\n\n\r\n public class Criteria<TModel>\r\n {\r\n string @operator;\r\n string field;\r\n object value;\r\n QueryLogicalOperator queryOperator;\r\n\r\n public QueryLogicalOperator QueryOperator => queryOperator;\r\n\r\n public Criteria(string @operator, string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n this.@operator = @operator;\r\n this.field = field;\r\n this.value = value;\r\n this.queryOperator = queryOperator;\r\n }\r\n\r\n static string DebugField<TKey>(Expression<Func<TModel, TKey>> method)\r\n {\r\n string field = method.Body.ToString();\r\n field = field.Remove(0, field.IndexOf(".") + 1);\r\n return field;\r\n }\r\n\r\n public static Criteria<TModel> GreaterThan(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new(">", field, value, queryOperator);\r\n public static Criteria<TModel> GreaterThan<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new(">", field, value, queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> GreaterThanOrEqual(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new(">=", field, value, queryOperator);\r\n public static Criteria<TModel> GreaterThanOrEqual<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new(">=", field, value, queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> LessThan(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("<", field, value, queryOperator);\r\n public static Criteria<TModel> LessThan<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new("<", field, value, queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> LessThanOrEqual(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("<=", field, value, queryOperator);\r\n public static Criteria<TModel> LessThanOrEqual<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new("<=", field, value, queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> Equal(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("=", field, value, queryOperator);\r\n public static Criteria<TModel> Equal<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new("=", field, value, queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> Contains(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("Like", field, $"%{value}%", queryOperator);\r\n public static Criteria<TModel> Contains<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new("Like", field, $"%{value}%", queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> StartsWith(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("Like", field, $"{value}%", queryOperator);\r\n public static Criteria<TModel> StartsWith<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = DebugField(method);\r\n return new("Like", field, $"{value}%", queryOperator);\r\n }\r\n\r\n public static Criteria<TModel> EndsWith(string field, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n => new("Like", field, $"%{value}", queryOperator);\r\n public static Criteria<TModel> EndsWith<TKey>(Expression<Func<TModel, TKey>> method, object value, QueryLogicalOperator queryOperator = QueryLogicalOperator.None)\r\n {\r\n string field = method.Body.ToString();\r\n return new("Like", field, $"%{value}", queryOperator);\r\n }\r\n\r\n public string GenerateSql()\r\n => $"{field} {@operator} {(value is int or long or float or decimal ? value : $"'{value}'")}";\r\n }\r\n<\/pre>\n<\/div>\nYukar\u0131daki kod blo\u011funu iyice analiz edersek e\u011fer; 3<\/em><\/strong> ile 6.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131nda bir sorguda bulunacak kriterlerin temel par\u00e7alar\u0131n\u0131 tutacak field’lar olu\u015fturulmu\u015ftur. ‘@operator’, kriterin i\u015flevsel(b\u00fcy\u00fck, k\u00fc\u00e7\u00fck vs. gibi) operat\u00f6r\u00fcn\u00fc ifade ederken, ‘queryOperator’ birden fazla kriter aras\u0131ndaki ve\/veya mant\u0131\u011f\u0131ndaki operat\u00f6r\u00fc ifade eder. ‘field’ sorguda hangi kolona kriter uyguland\u0131\u011f\u0131n\u0131 ifade ederken, kriterin de\u011ferini de ‘value’ ifade etmektedir.<\/p>\n 8.<\/em><\/strong> sat\u0131rdaki ‘QueryOperator’ property’si ise bir sonraki ad\u0131mda olu\u015fturaca\u011f\u0131m\u0131z ve bu ‘Criteria<T>’ s\u0131n\u0131f\u0131n\u0131 kullanaca\u011f\u0131m\u0131z ‘Query’ s\u0131n\u0131f\u0131 taraf\u0131ndan sorguya kriterleri hangi mant\u0131kta ekleyece\u011fimizi \u00f6\u011frenmemizi sa\u011flayacak olan property’dir.<\/p>\n 25<\/em><\/strong> ile 31.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131ndaki static olarak tan\u0131mlanm\u0131\u015f ‘GreaterThan’ isimli metot overload’lar\u0131 olu\u015fturulacak sorguya ‘b\u00fcy\u00fckt\u00fcr’ mahiyetinde bir \u015fart eklemektedirler. Overload’lara bakarsan\u0131z e\u011fer biri manuel olarak parametrelerden ‘field’ ve ‘value’ de\u011ferlerini al\u0131rken di\u011feri ise ‘Func<T>’ arac\u0131l\u0131\u011f\u0131yla bu i\u015flemi ger\u00e7ekle\u015ftirmektedir. Benzer mant\u0131kla; 33<\/em><\/strong> – 39<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131ndaki metotlar ‘b\u00fcy\u00fck e\u015fittir’, 41<\/em><\/strong> – 47<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131ndakiler ‘k\u00fc\u00e7\u00fckt\u00fcr’, 49<\/em><\/strong> – 55<\/em><\/strong> ‘k\u00fc\u00e7\u00fck e\u015fittir’, 57<\/em><\/strong> – 63<\/em><\/strong> ‘e\u015fittir’ \u015fartlar\u0131n\u0131 uygulamaktad\u0131rlar.<\/p>\n 65<\/em><\/strong> – 71<\/em><\/strong>, 73<\/em><\/strong> – 79<\/em><\/strong> ve 81<\/em><\/strong> – 87.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131ndaki metotlar s\u0131ras\u0131yla ‘%..%’, ‘..%’ ve ‘%..’ mant\u0131\u011f\u0131ndaki LIKE \u015fartlar\u0131n\u0131 uygulamaktad\u0131rlar.<\/p>\n 18<\/em><\/strong> ile 23.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131ndaki ‘DebugField’ isimli generic metot ise t\u00fcm ‘Func<T>’ parametreli \u015fart metotlar\u0131nda lambda ile se\u00e7ilen property’inin kolon ad\u0131 olarak kullan\u0131labilmesi i\u00e7in string olarak ay\u0131rt edilebilmesi i\u015flevini ger\u00e7ekle\u015ftirmektedir.<\/p>\n Ve son olarak 89<\/em><\/strong> – 90<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131nda bulunan ‘GenerateSql’ metodu ise ilgili kriterin sorguya eklenebilmesi i\u00e7in uygun formatta olu\u015fturulmas\u0131ndan sorumludur.<\/p>\n Tabi bu kod blo\u011funda kullan\u0131lan ‘QueryLogicalOperator’ enum’\u0131 da a\u015fa\u011f\u0131daki gibi bir i\u00e7eri\u011fe sahiptir.<\/p>\n\n\r\n public enum QueryLogicalOperator\r\n {\r\n And, Or, None\r\n }\r\n<\/pre>\n<\/div>\n<\/li>\nAd\u0131m 2<\/em><\/strong> \nS\u0131rada, art\u0131k olu\u015fturulan kriterlerin kullan\u0131laca\u011f\u0131 esas sorguyu temsil edecek olan ‘Query’ s\u0131n\u0131f\u0131n\u0131n olu\u015fturulmas\u0131 vard\u0131r.<\/p>\n\n\r\n public class Query<T>\r\n {\r\n string baseQuery = "SELECT {0} FROM {1}";\r\n\r\n List<Criteria<T>> criterias;\r\n public Query(string tableNames) : this("", tableNames)\r\n { }\r\n\r\n public Query(List<Criteria<T>> criterias, string tableNames) : this("", tableNames)\r\n => this.criterias = criterias;\r\n\r\n public Query(string columnNames, string tableNames)\r\n {\r\n this.criterias = new();\r\n baseQuery = String.Format(baseQuery, columnNames, tableNames);\r\n }\r\n\r\n public Query(Expression<Func<T, object>> method, string tableNames)\r\n {\r\n this.criterias = new();\r\n NewExpression expression = method.Body as NewExpression;\r\n List<Expression> arguments = expression.Arguments.ToList();\r\n\r\n StringBuilder columns = new();\r\n for (int i = 0; i < arguments.Count; i++)\r\n {\r\n Expression argument = arguments[i];\r\n columns.Append(argument.ToString().Remove(0, argument.ToString().IndexOf(".") + 1));\r\n if (i != arguments.Count - 1)\r\n columns.Append(", ");\r\n }\r\n baseQuery = String.Format(baseQuery, columns, tableNames);\r\n }\r\n\r\n public void Add(Criteria<T> criteria)\r\n => this.criterias.Add(criteria);\r\n\r\n public string GenerateWhereClause()\r\n {\r\n StringBuilder whereClause = new();\r\n whereClause.Append("Where ");\r\n for (int i = 0; i < criterias.Count; i++)\r\n {\r\n Criteria<T> criteria = criterias[i];\r\n whereClause.Append(criteria.GenerateSql());\r\n if (i != criterias.Count - 1)\r\n whereClause.Append(criteria.QueryOperator switch { QueryLogicalOperator.Or => " OR ", QueryLogicalOperator.And => " AND " });\r\n }\r\n return $"{baseQuery} {whereClause.ToString()}";\r\n }\r\n }\r\n<\/pre>\n<\/div>\nBu s\u0131n\u0131f\u0131 da incelersek e\u011fer; 3.<\/em><\/strong> sat\u0131rda olu\u015fturulacak base query format\u0131n\u0131 g\u00f6rmekteyiz. Bu query s\u00fcre\u00e7te gelecek olan kolon ve tablo bilgisine g\u00f6re \u015fekilleniyor olacakt\u0131r.<\/p>\n 5.<\/em><\/strong> sat\u0131rda, olu\u015fturulacak query’de kullan\u0131lacak olan kriterlerin tutulaca\u011f\u0131 koleksiyon referans\u0131 mevcuttur.<\/p>\n 6<\/em><\/strong> ile 33.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131na bakarsan\u0131z e\u011fer constructor metotlar\u0131n overload’lar\u0131 mevcuttur. Bu overload’lar\u0131 incelersek sorgulama s\u00fcrecinde kullan\u0131lacak kolon ve tablo isimlerini t\u00fcrl\u00fc \u015fekilde almakta ve ‘baseQuery’e basmaktad\u0131rlar. Bunlar\u0131n aras\u0131ndan tek dikkat edilmesi gereken 18<\/em><\/strong> ile 33.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131nda constructor overload’\u0131d\u0131r. Bu overload sorguda kullan\u0131lacak olan kolonlar\u0131 ‘Func<T>’ parametresi ile lambda e\u015fli\u011finde belirleyebilmemizi sa\u011flamaktad\u0131r. Yap\u0131sal olarak lambda ile anonymous bir nesne olarak bildirilen property’ler ilgili overload i\u00e7erisinde kolon ad\u0131 olarak alg\u0131lanarak ay\u0131klanmakta ve virg\u00fclle yanyana birle\u015ftirilip ‘baseQuery’de ilgili alana yaz\u0131lmaktad\u0131rlar.<\/p>\n 35.<\/em><\/strong> sat\u0131rda ise generate edilecek sorguya ihtiya\u00e7 do\u011frultusunda kriterlerin eklenmesini sa\u011flayan ‘Add’ fonksiyonu mevcuttur.<\/p>\n Ve 38<\/em><\/strong> ile 50.<\/em><\/strong> sat\u0131r aral\u0131\u011f\u0131nda ise olu\u015fturulan ‘baseQuery’ e\u015fli\u011finde kriterlerin harmanland\u0131\u011f\u0131 sorguyu olu\u015fturacak olan ‘GenerateWhereClause’ metodu mevcuttur. Bu metot, kriterlerin ‘GenerateSql’ metodunu tetikleyerek sorguya eklenebilir halde olu\u015fturulmalar\u0131n\u0131 ve ‘baseQuery’ \u00fczerinde ‘Where’ \u015fart\u0131 olarak eklenmelerini sa\u011flamakta ve nihai olarak sorguyu bizlere return etmektedir.\n<\/li>\n Ad\u0131m 3<\/em><\/strong> \nSon olarak query’i olu\u015fturma s\u00fcrecinde ‘Criteria’ ve ‘Query’ s\u0131n\u0131flar\u0131nda ‘Func<T>’ parametreli fonksiyonlar\u0131 kullanabilmek i\u00e7in bir t\u00fcre ihtiyac\u0131m\u0131z olacakt\u0131r. Bu t\u00fcr veritaban\u0131ndaki tabloya kar\u015f\u0131l\u0131k gelecek olan entity modelidir. \u00c7\u00fcnk\u00fc ancak bir entity \u00fczerinden g\u00f6n\u00fcl rahatl\u0131\u011f\u0131yla tip g\u00fcvenli sorgulama, kolon yahut \u015fart belirleme i\u015flemlerini tutarl\u0131 bir \u015fekilde ger\u00e7ekle\u015ftirebiliriz. Dolay\u0131s\u0131yla a\u015fa\u011f\u0131daki gibi ‘Products’ tablosunu modelleyen ‘Product’ s\u0131n\u0131f\u0131n\u0131 in\u015fa edelim.<\/p>\n\n\r\n public class Product\r\n {\r\n public int Id { get; set; }\r\n public string Name { get; set; }\r\n public int Stock { get; set; }\r\n public float Price { get; set; }\r\n }\r\n<\/pre>\n<\/div>\n<\/li>\nAd\u0131m 4<\/em><\/strong> \nArt\u0131k sorgular\u0131m\u0131z\u0131 olu\u015fturabilir ve kriterlerimizi belirleyebiliriz.<\/p>\nMisal, manuel parametreli fonksiyonlar\u0131 kullanarak ‘Name’ i\u00e7erisinde ‘i’ harfi ge\u00e7en ve ‘Stock’ bilgisi 100’den b\u00fcy\u00fck olan \u00fcr\u00fcnleri getirecek olan sorguyu olu\u015ftural\u0131m.<\/p>\n \n\r\nQuery<Product> query = new("Products");\r\nquery.Add(Criteria<Product>.Contains("Name", "i", QueryLogicalOperator.And));\r\nquery.Add(Criteria<Product>.GreaterThan("Stock", 100));\r\nstring _query = query.GenerateWhereClause();\r\n\r\nConsole.WriteLine(_query);\r\n<\/pre>\n<\/div>\nBu \u00e7al\u0131\u015fma neticesinde a\u015fa\u011f\u0131daki sorgu \u00fcretilecektir. \nSELECT FROM Products Where Name Like '%i%' AND Stock > 100<\/strong><\/code><\/p>\n Ya da ‘Price’\u0131 10’dan b\u00fcy\u00fck e\u015fit olan veya ‘Name’ de\u011feri ‘\u00c7anta’ olan \u00fcr\u00fcnlerin sadece ‘Id’ ve ‘Name’ bilgilerini getirecek sorguyu ‘Func<T>’ e\u015fli\u011finde olu\u015ftural\u0131m.<\/p>\n \n\r\nQuery<Product> query = new(p => new\r\n{\r\n p.Id,\r\n p.Name\r\n}, "Products");\r\nquery.Add(Criteria<Product>.GreaterThanOrEqual(p => p.Price, 10, QueryLogicalOperator.Or));\r\nquery.Add(Criteria<Product>.Equal(p => p.Name, "\u00c7anta", QueryLogicalOperator.None));\r\nstring _query = query.GenerateWhereClause();\r\n<\/pre>\n<\/div>\nBu \u00e7al\u0131\u015fman\u0131n da \u00e7\u0131kt\u0131s\u0131 a\u015fa\u011f\u0131daki gibi olacakt\u0131r. \nSELECT Id, Name FROM Products Where Price >= 10 OR Name = '\u00c7anta'<\/strong><\/code>\n<\/li>\n<\/ul>\n \u0130\u015fte, g\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere Query Object pattern her bir veritaban\u0131 sorgusunu, temsil eden bir generic nesne olarak tasarlamam\u0131z\u0131 ve ihtiyaca d\u00f6n\u00fck sorgular\u0131 tip g\u00fcvenli bir \u015fekilde fonksiyonaliteler e\u015fli\u011finde olu\u015fturmam\u0131z\u0131 sa\u011flamakta ve b\u00f6ylece geli\u015ftiricinin sorgu s\u00fcre\u00e7lerinde y\u00fck\u00fcn\u00fc minimuma \u00e7ekip olas\u0131 maliyetlerden geli\u015ftirme s\u00fcrecini ar\u0131nd\u0131rmaktad\u0131r.<\/p>\n Tabi ki de bu pattern’\u0131 in\u015fa ederken genel stratejisinin haricinde geri kalan \u00f6zellik ve fonksiyonlar\u0131 kendi ihtiya\u00e7 ve d\u00fc\u015f\u00fcncenize g\u00f6re tasarlayabilir ve \u00e7ok farkl\u0131 niteliklerde i\u015flevsellikler ekleyerek generate edilecek sorgu \u00e7e\u015fitlili\u011fini azami miktarda detayland\u0131rabilir ve artt\u0131rabilirsiniz.<\/p>\n \u0130lgilenenlerin faydalanmas\u0131 dile\u011fiyle… \nSonraki yaz\u0131lar\u0131mda g\u00f6r\u00fc\u015fmek \u00fczere… \n\u0130yi \u00e7al\u0131\u015fmalar…<\/p>\n Not : \u00d6rnek projeyi a\u015fa\u011f\u0131daki github adresinden edinebilirsiniz. \nhttps:\/\/github.com\/gncyyldz\/QueryObjectDesignPatternExample<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" Merhaba, G\u00fcn\u00fcm\u00fczde bir\u00e7ok uygulamada veritaban\u0131 i\u015flemleri i\u00e7in yayg\u0131n olarak ORM(Object Relational Mapping) yakla\u015f\u0131m\u0131 tercih ediliyor olsa da bir\u00e7ok k\u00fc\u00e7\u00fck ve orta \u00f6l\u00e7ekli \u015firket taraf\u0131ndan ve hatta geleneksel olarak radikal de\u011fi\u015fiklikleri kald\u0131ramayacak kadar k\u00f6kl\u00fc framework’lere...<\/p>\n","protected":false},"author":1,"featured_media":24883,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[153],"tags":[4568,4569,4567,4566,4571,4570],"class_list":["post-24873","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-design-pattern","tag-query-object","tag-query-object-d-p","tag-query-object-design-pattern","tag-query-object-tasarim-deseni","tag-sorgu-nesnesi","tag-sorgu-nesnesi-tasarim-deseni"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24873","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=24873"}],"version-history":[{"count":66,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24873\/revisions"}],"predecessor-version":[{"id":24943,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24873\/revisions\/24943"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media\/24883"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=24873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=24873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=24873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}