{"id":15918,"date":"2020-05-18T20:56:42","date_gmt":"2020-05-18T20:56:42","guid":{"rendered":"https:\/\/www.gencayyildiz.com\/blog\/?p=15918"},"modified":"2020-05-18T20:56:42","modified_gmt":"2020-05-18T20:56:42","slug":"asp-net-core-web-api-odata-query-options","status":"publish","type":"post","link":"https:\/\/www.gencayyildiz.com\/blog\/asp-net-core-web-api-odata-query-options\/","title":{"rendered":"Asp.NET Core WEB API – OData Query Options"},"content":{"rendered":"

<\/div>\n

Merhaba,<\/p>\n

Bir \u00f6nceki Asp.NET Core Web API Uygulamas\u0131na OData Implementasyonu<\/a> ba\u015fl\u0131kl\u0131 yaz\u0131m\u0131zda bir Asp.NET Core Web API uygulamas\u0131na OData implemantasyonunun nas\u0131l ger\u00e7ekle\u015ftirildi\u011fini incelemi\u015ftik. Bu i\u00e7eri\u011fimizde ise serverdan d\u00f6necek data miktar\u0131n\u0131 kontrol etmemize imkan verecek olan OData Query Options<\/em> yap\u0131lanmas\u0131n\u0131 inceleyece\u011fiz.<\/p>\n

OData Query Options Nedir?<\/h3>\n
OData Query Options temel olarak, kullan\u0131c\u0131 taraf\u0131ndan url \u00fczerinden yap\u0131lan veri sorgulamalar\u0131nda(delete hari\u00e7) \u00fcretilen datan\u0131n kullan\u0131c\u0131ya g\u00f6nderilmeden \u00f6nce filtrelenmesini, s\u0131ralanmas\u0131n\u0131 vs. gibi i\u015flemlere tabii tutularak yap\u0131land\u0131r\u0131lmas\u0131n\u0131 sa\u011flayan se\u00e7eneklerdir.<\/p>\n
OData Query Options; ‘System Query Options’, ‘Custom Query Options’ ve ‘Parameter Aliases’ olmak \u00fczere \u00fc\u00e7e ayr\u0131lmaktad\u0131r. Hangi kategoriden olursa olsun se\u00e7enekler a\u015fa\u011f\u0131daki prototipte oldu\u011fu gibi url \u00fczerinde sorguya eklenmektedir.<\/p>\n
\n`https:\/\/localhost:5001\/odata\/{controller_name}?${options}={value}<\/span><\/span><\/strong><\/code>\n<\/div>\n\u015eimdi s\u0131ras\u0131yla bu se\u00e7enekleri inceleyelim.<\/p>\n`System Query Options<\/h4>\n
URL \u00fczerinden yap\u0131lan sorgulama neticesinde d\u00f6nd\u00fcr\u00fclecek olan data \u00fczerinde filtreleme, se\u00e7me, s\u0131ralama gibi i\u015flemler yapan, eleman adedini hesaplama, belirtilen adet kadar\u0131n\u0131 ay\u0131klama yahut g\u00f6rmezden gelme gibi eylemlerde bulunan ve navigation propertyler \u00fczerinden fiziksel ili\u015fkiye sahip olan di\u011fer tablolar\u0131da sorgulayabilmemizi sa\u011flayan se\u00e7eneklerdir.<\/p>\n
\n
$filter<\/strong>
\nVerilerin filtrelenmesini sa\u011flar. ‘Kar\u015f\u0131la\u015ft\u0131rma’, ‘Mant\u0131ksal’, ‘Aritmatik’ ve ‘Fonksiyonel’ operat\u00f6rlerden olu\u015fmaktad\u0131r. Belirtilen ifadelerle yap\u0131lan i\u015flemler neticesinde sonu\u00e7 olarak true d\u00f6nen \u00f6\u011feler sonuca eklenecektir.<\/p>\n
Bu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n
\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.Filter();\r\n });\r\n<\/pre>\n
Kar\u015f\u0131la\u015ft\u0131rma Operat\u00f6rleri<\/strong><\/p>\n
\n
eq(Equal)<\/strong>
\n\u00dclkesi ‘USA’ olanlar\u0131 getirir.
\n`https:\/\/localhost:5001\/odata\/personel?$filter=ulke eq 'USA'<\/span><\/code>\n<\/li>\n`
`ne(Not Equal)<\/strong> \n\u00dclkesi ‘USA’ olmayanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=ulke ne 'USA'<\/span><\/code>\n<\/li>\n`
`gt(Greater Than)<\/strong> \nPersonelId de\u011feri 5’ten b\u00fcy\u00fck olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=personelid gt 5<\/span><\/code>\n<\/li>\n`
`ge(Greater Equal)<\/strong> \nPersonelId de\u011feri 5 ve 5’ten b\u00fcy\u00fck olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=personelid ge 5<\/span><\/code>\n<\/li>\n`
`lt(Less Than)<\/strong> \nPersonelId de\u011feri 5’ten k\u00fc\u00e7\u00fck olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=personelid lt 5<\/span><\/code>\n<\/li>\n`
`le(Less Equal)<\/strong> \nPersonelId de\u011feri 5’ten k\u00fc\u00e7\u00fck ve 5’e e\u015fit olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=personelid le 5<\/span><\/code>\n<\/li>\n<\/ul>\nMant\u0131ksal Operat\u00f6rler<\/strong><\/p>\n`
\n
and<\/strong>
\n\u00dclkesi ‘USA’ ve b\u00f6lgesi ‘WA’ olan personelleri getirir.
\n`https:\/\/localhost:5001\/odata\/personel?$filter=ulke eq 'USA' and bolge eq 'WA'<\/span><\/code>\n<\/li>\n`
or<\/strong> \n\u00dclkesi ‘USA’ veya b\u00f6lgesi ‘WA’ olan personelleri getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=ulke eq 'USA' or bolge eq 'WA'<\/span><\/code>\n<\/li>\n<\/ul>\nAritmatik Operat\u00f6rler<\/strong><\/p>\n \nadd(Addition)<\/strong> \nMaa\u015fa 500 eklendi\u011finde 1500’e e\u015fit olan personelleri getir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=maas add 500 eq 1500<\/span><\/code>\n<\/li>\n sub(Subtraction)<\/strong> \nMaa\u015f\u0131ndan 500 \u00e7\u0131kar\u0131ld\u0131\u011f\u0131nda 1500’e e\u015fit olan personelleri getir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=maas sub 500 eq 1500<\/span><\/code>\n<\/li>\n mul(Multiplication)<\/strong> \nMaa\u015f\u0131 1.5 ile \u00e7arp\u0131ld\u0131\u011f\u0131nda 1500’e e\u015fit olan personelleri getir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=maas mul 500 eq 1500<\/span><\/code>\n<\/li>\n div(Division)<\/strong> \nMaa\u015f\u0131 3’e b\u00f6l\u00fcnd\u00fc\u011f\u00fcnde 1500’e e\u015fit olan personelleri getir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=maas div 500 eq 1500<\/span><\/code>\n<\/li>\n<\/ul>\nFonksiyonel Operat\u00f6rler<\/strong><\/p>\n \nendswith<\/strong> \nAd\u0131 ‘y’ ile bitenleri getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=endswith(Adi, 'y')<\/span><\/code>\n<\/li>\n startswith<\/strong> \nAd\u0131 ‘y’ ile ba\u015flayanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=startswith(Adi, 'y')<\/span><\/code>\n<\/li>\n length<\/strong> \nAd\u0131n\u0131n karakter say\u0131s\u0131 2’den fazla olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=length(Adi) gt 2<\/span><\/code>\n<\/li>\n indexof<\/strong> \nAd\u0131 ‘r’ harfi ile ba\u015flayan personelleri getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=indexof(Adi, 'N') eq 0<\/span><\/code>\n<\/li>\n trim<\/strong> \nBo\u015fluklar\u0131 temizlenmi\u015f ad de\u011feri ‘Nancy’e e\u015fit olanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=trim(Adi) eq 'Nancy'<\/span><\/code>\n<\/li>\n year<\/strong> \n1992’de do\u011fanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=year(DogumTarihi) eq 1992<\/span><\/code>\n<\/li>\n month<\/strong> \n5. ayda do\u011fanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=month(DogumTarihi) eq 5<\/span><\/code>\n<\/li>\n day<\/strong> \n9. g\u00fcnde do\u011fanlar\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=day(DogumTarihi) eq 9<\/span><\/code>\n<\/li>\n<\/ul>\nDi\u011fer t\u00fcm $filter operat\u00f6rlerini g\u00f6rmek i\u00e7in buradaki<\/a> d\u00f6k\u00fcman\u0131 inceleyebilirsiniz.<\/p>\n<\/li>\n $select<\/strong> \nBir entityden d\u00f6necek olan propertylerin belirlenmesini sa\u011flar.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.Select();\r\n });\r\n<\/pre>\n\u00d6rnek olarak a\u015fa\u011f\u0131daki iste\u011fi ve \u00e7\u0131kt\u0131s\u0131n\u0131 inceleyiniz; \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi<\/span><\/code> \n<\/a> \nB\u00f6ylece client ile server aras\u0131nda transfer edilen data boyutu ve miktar\u0131 t\u00f6rp\u00fclenerek performansl\u0131 bir transfer s\u00f6z konusu olacakt\u0131r. \n \n<\/li>\n $orderby<\/strong> \nData \u00fczerinde s\u0131ralama yap\u0131lmas\u0131n\u0131 sa\u011flar.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.Select().OrderBy();\r\n });\r\n<\/pre>\n$orderby; k\u00fc\u00e7\u00fckten b\u00fcy\u00fc\u011fe ‘asc’ ve b\u00fcy\u00fckten k\u00fc\u00e7\u00fc\u011fe ‘desc’ olmak \u00fczere iki t\u00fcrl\u00fc s\u0131ralama ger\u00e7ekle\u015ftirmektedir. Bunun i\u00e7in a\u015fa\u011f\u0131daki \u00f6rnekleri inceleyebilirsiniz;<\/p>\n\nasc<\/strong> \nB\u00fcy\u00fckten k\u00fc\u00e7\u00fc\u011fe s\u0131ralar. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi&$orderby=adi asc<\/span><\/code>\n<\/li>\n desc<\/strong> \nK\u00fc\u00e7\u00fckten b\u00fcy\u00fc\u011fe s\u0131ralar. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi&$orderby=adi desc<\/span><\/code>\n<\/li>\n<\/ul>\n<\/li>\n$count<\/strong> \nGelen data miktar\u0131n\u0131n adedini verir.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.Filter().Count();\r\n });\r\n<\/pre>\n\u00d6rnek olarak a\u015fa\u011f\u0131daki sorgular\u0131 inceleyiniz; \nToplam kay\u0131t miktar\u0131n\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$count=true<\/span><\/code><\/p>\n \u015earta uygun kay\u0131t miktar\u0131n\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$filter=year(IseBaslamaTarihi) eq 1992&$count=true<\/span><\/code>\n<\/li>\n$top<\/strong> \n\u0130lk n adet veriyi getirir.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.OrderBy().Select().MaxTop(50); \/\/Maximum girilebilecek Top de\u011ferini belirtiyoruz.\r\n });\r\n<\/pre>\nPersonelId de\u011ferine g\u00f6re b\u00fcy\u00fckten k\u00fc\u00e7\u00fc\u011fe s\u0131ralanm\u0131\u015f listeden ilk 3 verinin ad\u0131n\u0131 ve soyad\u0131n\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi&$top=3&$orderby=personelid desc<\/span><\/code> \n<\/a>\n<\/li>\n $skip<\/strong> \nn adet veriyi atlar.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden herhangi bir metot eklemeye gerek yoktur. \u0130lgili operatif \u00f6zellik dahili olarak gelecektir.<\/p>\n PersonelId de\u011ferine g\u00f6re s\u0131ralanm\u0131\u015f personellerden 3 ve 4. personelleri getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi&$skip=2&$top=2<\/span><\/code><\/p>\n<\/li>\n $expand<\/strong> \nSorgulama yap\u0131lan entityde bulunan ve fiziksel olarak ili\u015fkiye sahip olan navigation propertylerin sorgulanmas\u0131n\u0131 ve b\u00f6ylece ili\u015fkisel verilerin elde edilmesini sa\u011flar.<\/p>\nBu se\u00e7ene\u011fi kullanabilmek i\u00e7in endpoint \u00fczerinden a\u015fa\u011f\u0131daki gibi tan\u0131mlanmas\u0131 gerekmektedir.<\/p>\n\r\n app.UseEndpoints(endpoints =>\r\n {\r\n endpoints.MapODataRoute("odata", "odata", builder.GetEdmModel());\r\n endpoints.Count().Expand();\r\n });\r\n<\/pre>\nA\u015fa\u011f\u0131daki \u00f6rnekleri inceleyiniz;<\/p>\n\n\nPersonel entitysi i\u00e7erisindeki ‘Satislar’ propertysi expand edilmekte ve ili\u015fkisel olarak personellerin yapt\u0131\u011f\u0131 t\u00fcm sat\u0131\u015flar getirtilmektedir. \nhttps:\/\/localhost:5001\/odata\/personel?$expand=satislar<\/span><\/code><\/p>\n<\/a>\n<\/li>\n \nPersonellerin yapt\u0131\u011f\u0131 t\u00fcm sat\u0131\u015flarla birlikte sadece ‘SevkAdi’ ve ‘MusteriId’si getirtilmektedir. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi, soyadi&$expand=satislar($select=SevkAdi, MusteriId)<\/span><\/code><\/p>\n<\/a>\n<\/li>\n \nPersonellerden ilkinin t\u00fcm sat\u0131\u015flar\u0131 getirtilmektedir. \nhttps:\/\/localhost:5001\/odata\/personel(1)<\/strong>?$select=adi, soyadi&$expand=satislar ($select=SevkAdi, MusteriId)&$count=true<\/span><\/code> \n<\/a>\n<\/li>\n \nPersonellerin yapt\u0131\u011f\u0131 sat\u0131\u015flar\u0131n miktar\u0131n\u0131 getirir. \nhttps:\/\/localhost:5001\/odata\/personel?$select=adi&$expand=satislar($count=true;$select=satisid)<\/span><\/code>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\nEndpoint Seviyesinde Operat\u00f6r Belirleme<\/h3>\nYukar\u0131daki operat\u00f6rleri incelerseniz e\u011fer her biri i\u00e7in ‘Startup.cs’ dosyas\u0131ndaki ‘Configure’ metodunda ayr\u0131 ayr\u0131 tan\u0131mlama yapmam\u0131z gerekti. Halbuki endpoint seviyesinde de hangi operat\u00f6r\u00fclerin kullan\u0131labilece\u011fini a\u015fa\u011f\u0131daki gibi belirtebilmekteyiz. \n<\/a> \nG\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere ‘EnableQuery’ attribute’unda ‘Allowed…’ ile ba\u015flayan t\u00fcm propertyler esas\u0131nda bu actionda aktif olacak operat\u00f6rleri belirlememizi sa\u011flamaktad\u0131rlar.<\/p>\n \u00d6rne\u011fin a\u015fa\u011f\u0131daki kod blo\u011funu incelerseniz e\u011fer;<\/p>\n \r\n [EnableQuery(AllowedFunctions = AllowedFunctions.All)]\r\n public IActionResult Get()\r\n {\r\n return Ok(_northwindContext.Kategoriler.ToList());\r\n }\r\n<\/pre>\n‘Get’ fonksiyonunda t\u00fcm fonksiyonel operat\u00f6rlerin \u00e7al\u0131\u015fmas\u0131na endpoint seviyesinde izin verildi\u011fini g\u00f6receksiniz.<\/p>\n \u0130lgilenenlerin faydalanmas\u0131 dile\u011fiyle… \nSonraki yaz\u0131lar\u0131mda g\u00f6r\u00fc\u015fmek \u00fczere… \n\u0130yi \u00e7al\u0131\u015fmalar…<\/p>\n","protected":false},"excerpt":{"rendered":" Merhaba, Bir \u00f6nceki Asp.NET Core Web API Uygulamas\u0131na OData Implementasyonu ba\u015fl\u0131kl\u0131 yaz\u0131m\u0131zda bir Asp.NET Core Web API uygulamas\u0131na OData implemantasyonunun nas\u0131l ger\u00e7ekle\u015ftirildi\u011fini incelemi\u015ftik. Bu i\u00e7eri\u011fimizde ise serverdan d\u00f6necek data miktar\u0131n\u0131 kontrol etmemize imkan verecek...<\/p>\n","protected":false},"author":1,"featured_media":15889,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3102,3103,3516],"tags":[3524,3514,3522,3525,3523],"class_list":["post-15918","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-asp-net-core","category-asp-net-core-3-1","category-odata","tag-custom-query-options","tag-odata","tag-odata-query-options","tag-parameter-aliases","tag-system-query-options"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/15918","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=15918"}],"version-history":[{"count":66,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/15918\/revisions"}],"predecessor-version":[{"id":15990,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/15918\/revisions\/15990"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media\/15889"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=15918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=15918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=15918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}