<\/div>\n
Merhaba,<\/p>\n
SQL Server 2016’da tan\u0131t\u0131lm\u0131\u015f olan Temporal Tables<\/a> kavram\u0131, veri de\u011fi\u015fikli\u011fi s\u00fcre\u00e7lerinde kay\u0131tlar\u0131 depolayan ve zaman i\u00e7inde farkl\u0131 noktalardaki tablo verilerinin analizi i\u00e7in kullan\u0131lan ve sistem taraf\u0131ndan y\u00f6netilen tablolar\u0131 ifade etmektedir. EF Core, 6.0 s\u00fcr\u00fcm\u00fcyle birlikte temporal table’lar\u0131 destekleyici nitelik kazanm\u0131\u015ft\u0131r. Bu i\u00e7eri\u011fimizde, Code First<\/a> yakla\u015f\u0131m\u0131 ile Temporal Tables’lar\u0131n nas\u0131l kullan\u0131labilece\u011fini inceleyecek ve LINQ e\u015fli\u011finde ge\u00e7mi\u015f verileri nas\u0131l sorgulayabilece\u011fimize dair \u00e7al\u0131\u015fma sergileyece\u011fiz.<\/p>\n
\u0130lk olarak Temporal Table’lar\u0131 inceleyebilmek i\u00e7in temel bir EF Core yap\u0131lanmas\u0131n\u0131 herhangi bir console uygulamas\u0131 \u00fczerinden aya\u011fa kald\u0131ral\u0131m. Haliyle \u00f6ncelikle a\u015fa\u011f\u0131daki k\u00fct\u00fcphaneleri kurarak ba\u015flayal\u0131m :<\/p>\n
Microsoft.EntityFrameworkCore.SqlServer<\/a><\/li>\n
Microsoft.EntityFrameworkCore.Design<\/a><\/li>\n
Microsoft.EntityFrameworkCore.Tools<\/a><\/li>\n<\/ul>\n
Ard\u0131ndan basitlik ilkesi gere\u011fi \u00f6rneklendirmeyi yal\u0131n bir \u015fekilde sunabilmek i\u00e7in sadece a\u015fa\u011f\u0131daki modeli \u00fcretmemiz yeterli olacakt\u0131r.<\/p>\n
\r\nclass Post\r\n{\r\n    public int Id { get; set; }\r\n    public string Title { get; set; }\r\n    public string Content { get; set; }\r\n    public PostType? PostType { get; set; }\r\n}\r\n\r\nenum PostType\r\n{\r\n    Talk,\r\n    Side,\r\n    Citation,\r\n    Unknown\r\n}\r\n<\/pre>\n<\/div>\n\u015eimdi yap\u0131lmas\u0131 gereken, bir context nesnesi olu\u015fturmak ve yukar\u0131da olu\u015fturdu\u011fumuz modeli DbSet olarak context’te tan\u0131mlamak.<\/p>\n\n\r\nclass TemporalExampleDb : DbContext\r\n{\r\n    public DbSet<Post> Posts { get; set; }\r\n    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\r\n    {\r\n        optionsBuilder.UseSqlServer("Server=***;Database=TemporalExampleDb;User Id=***;Password=***");\r\n    }\r\n}\r\n<\/pre>\n<\/div>\nVe son olarak yapmam\u0131z gereken a\u015fa\u011f\u0131daki migration kodlar\u0131n\u0131 s\u0131ras\u0131yla tetikleyerek olu\u015fturdu\u011fumuz modelde bir veritaban\u0131n\u0131 sunucuya yans\u0131tmakt\u0131r.
\ndotnet ef migrations add mig_1<\/code>
\ndotnet ef database update<\/code><\/p>\n
Evet, art\u0131k temporal table’lar\u0131 inceleyece\u011fimiz altyap\u0131m\u0131z haz\u0131r. \u015eimdi konuya giri\u015f yapabiliriz.<\/p>\nBir Tabloyu Temporal Table Yapma<\/h4>\nEF Core ile bir tabloyu temporal table yapabilmek i\u00e7in a\u015fa\u011f\u0131daki gibi olduk\u00e7a basit bir tan\u0131mlama yap\u0131lmas\u0131 gerekmektedir.<\/p>\n\n\r\nclass TemporalExampleDb : DbContext\r\n{\r\n    public DbSet<Post> Posts { get; set; }\r\n    .\r\n    .\r\n    .\r\n    protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n    {\r\n        modelBuilder.Entity<Post>()\r\n            .ToTable("Posts", t => t.IsTemporal());\r\n    }\r\n}\r\n<\/pre>\n<\/div>\nYukar\u0131daki kod blo\u011funu incelerseniz e\u011fer override edilmi\u015f olan ‘OnModelCreating’ i\u00e7erisinde ilgili model \u00fczerinde ‘ToTable’ fonksiyonu e\u015fli\u011finde ‘IsTemporal’ fonksiyonunu tetikleyerek bu tablonun bir temporal table’\u0131n\u0131n olaca\u011f\u0131 bildirilmektedir. Bu de\u011fi\u015fiklikten sonra yeni bir migration bas\u0131ld\u0131\u011f\u0131 taktirde i\u00e7eri\u011fi a\u015fa\u011f\u0131daki gibi olacakt\u0131r.
\n<\/a>
\nYukar\u0131daki g\u00f6rseli yorumlarsak e\u011fer; sar\u0131 ile vurgulanm\u0131\u015f k\u0131s\u0131mda ‘Posts’ tablosu bir temporal table olarak ayarlanmakta ve gerekli a\u00e7\u0131klamalar yap\u0131lmaktad\u0131r. Ye\u015fil ve mor k\u0131s\u0131mlarda ise yap\u0131lan verisel de\u011fi\u015fikliklerin hangi zamanlarda oldu\u011funa dair kay\u0131tlar\u0131 tutacak ‘PeriodStart’ ve ‘PeriodEnd’ kolonlar\u0131 eklenmektedir. Bu kolonlar\u0131n i\u015flevsel mant\u0131\u011f\u0131n\u0131 daha net anlayabilmek i\u00e7in makalemizin giri\u015f paragraf\u0131nda referans etti\u011fim SQL Server 2016 \u2013 Temporal Tables<\/a> ba\u015fl\u0131kl\u0131 makaleye g\u00f6z atman\u0131z\u0131 tavsiye ederim.<\/p>\n
Bu vaziyette olu\u015fturulmu\u015f migration’\u0131 migrate edersek e\u011fer ilgili tablo a\u015fa\u011f\u0131daki gibi sunucuya yans\u0131yacakt\u0131r.
\n<\/a><\/p>\n
Temporal Table’\u0131 Test Edelim<\/h4>\n\u015eimdi, EF Core \u00fczerinden birka\u00e7 veri ekleyelim ve ard\u0131ndan temporal table’\u0131n \u00e7al\u0131\u015f\u0131p \u00e7al\u0131\u015fmad\u0131\u011f\u0131n\u0131 test edelim.<\/p>\n\n\r\nPost p1 = new() { Content = "A Post Content", PostType = PostType.Talk, Title = "A Post Title" };\r\nPost p2 = new() { Content = "B Post Content", PostType = PostType.Talk, Title = "B Post Title" };\r\nPost p3 = new() { Content = "C Post Content", PostType = PostType.Talk, Title = "C Post Title" };\r\n\r\nTemporalExampleDb context = new();\r\nawait context.AddRangeAsync(p1, p2, p3);\r\nawait context.SaveChangesAsync();\r\n<\/pre>\n<\/div>\nEklenen bu veriler \u00fczerinde rastgele update ve delete operasyonlar\u0131 ger\u00e7ekle\u015ftirelim.<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nPost post = await context.Posts.FindAsync(2);\r\npost.PostType = PostType.Side;\r\npost.Title = "B Post Title - Updated";\r\n\r\nawait context.SaveChangesAsync();\r\n<\/pre>\n<\/div>\nYukar\u0131da ‘2’ id de\u011ferine sahip veri \u00fczerinde bir update i\u015flemi ger\u00e7ekle\u015ftirilmi\u015ftir. Bu i\u015flem neticesinde veritaban\u0131nda ilgili tablo ile temporal halini sorgularsak e\u011fer a\u015fa\u011f\u0131daki gibi bir sonu\u00e7la kar\u015f\u0131la\u015f\u0131lmaktad\u0131r.
\n<\/a>G\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere ‘2’ id de\u011ferine sahip verinin \u00f6nceki kayd\u0131 ve hangi zaman aral\u0131\u011f\u0131nda bu de\u011fi\u015fikli\u011fin yap\u0131ld\u0131\u011f\u0131 temporal table’a kaydedilmi\u015ftir. \u015eimdi ilgili veri \u00fczerinde bir g\u00fcncelleme i\u015flemi daha ger\u00e7ekle\u015ftirip, inceleyelim.<\/p>\n
\n\r\nTemporalExampleDb context = new();\r\n\r\nPost post = await context.Posts.FindAsync(2);\r\npost.PostType = PostType.Side;\r\npost.Title = "B Post Title - Updated - 2";\r\n\r\nawait context.SaveChangesAsync();\r\n<\/pre>\n<\/div>\n<\/a><\/p>\n
\u015eimdi ise delete operasyonu \u00e7ekelim.<\/p>\n
\n\r\nTemporalExampleDb context = new();\r\n\r\nPost post = await context.Posts.FindAsync(2);\r\ncontext.Posts.Remove(post);\r\n\r\nawait context.SaveChangesAsync();\r\n<\/pre>\n<\/div>\n<\/a>Dikkat ederseniz, ilgili veri silinirken son hali temporal table’a kopyalan\u0131r ve ard\u0131ndan kald\u0131r\u0131l\u0131r.<\/p>\n
Evet, anla\u015f\u0131lan EF Core e\u015fli\u011finde temporal table gayet sa\u011fl\u0131kl\u0131 bir \u015fekilde \u00e7al\u0131\u015fmaktad\u0131r \ud83d\ude42<\/p>\n
\u015eimdi en \u00f6nemli noktaya geldik diyebiliriz. Temporal table’lar\u0131n sorgulanmas\u0131na…<\/p>\n
EF Core ile Temporal Table’\u0131 Sorgulama<\/h4>\nZamansal de\u011fi\u015fim verilerini sorgulayabilmek i\u00e7in EF Core \u00e7e\u015fitli extension fonksiyonlar sunmaktad\u0131r. Bu fonksiyonlar TemporalAsOf<\/code>, TemporalAll<\/code>, TemporalFromTo<\/code>, TemporalBetween<\/code> ve TemporalContainedIn<\/code> olmak \u00fczere be\u015f tanedir.<\/p>\n\nTemporalAsOf<\/em><\/strong>
\nBelirli bir zaman i\u00e7in de\u011fi\u015fikli\u011fe u\u011frayan t\u00fcm \u00f6\u011feleri d\u00f6nd\u00fcr\u00fcr.<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nvar posts = await context.Posts.TemporalAsOf(new DateTime(2022, 01, 23, 08, 49, 45)).Select(data => new\r\n{\r\n    Post = data,\r\n    PeriodStart = EF.Property<DateTime>(data, "PeriodStart"),\r\n    PeriodEnd = EF.Property<DateTime>(data, "PeriodEnd"),\r\n}).ToListAsync();\r\n\r\nforeach (var post in posts)\r\n{\r\n    Console.WriteLine($"Id\\t\\t: {post.Post.Id}");\r\n    Console.WriteLine($"Title\\t\\t: {post.Post.Title}");\r\n    Console.WriteLine($"Content\\t\\t: {post.Post.Content}");\r\n    Console.WriteLine($"PeriodStart\\t: {post.PeriodStart}");\r\n    Console.WriteLine($"PeriodEnd\\t: {post.PeriodEnd}");\r\n    Console.WriteLine("**********");\r\n}\r\n<\/pre>\n<\/div>\n<\/a>\n<\/li>\n
TemporalAll<\/em><\/strong>
\nMevcudiyette bulunan, g\u00fcncellenmi\u015f yahut silinmi\u015f olan t\u00fcm verilerin ge\u00e7mi\u015f s\u00fcr\u00fcmlerini ve ge\u00e7erli durumlar\u0131n\u0131 d\u00f6nd\u00fcr\u00fcr.<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nvar posts = await context.Posts.TemporalAll().Select(data => new\r\n{\r\n    Post = data,\r\n    PeriodStart = EF.Property<DateTime>(data, "PeriodStart"),\r\n    PeriodEnd = EF.Property<DateTime>(data, "PeriodEnd"),\r\n}).ToListAsync();\r\n\r\nforeach (var post in posts)\r\n{\r\n    Console.WriteLine($"Id\\t\\t: {post.Post.Id}");\r\n    Console.WriteLine($"Title\\t\\t: {post.Post.Title}");\r\n    Console.WriteLine($"Content\\t\\t: {post.Post.Content}");\r\n    Console.WriteLine($"PeriodStart\\t: {post.PeriodStart}");\r\n    Console.WriteLine($"PeriodEnd\\t: {post.PeriodEnd}");\r\n    Console.WriteLine("**********");\r\n}\r\n<\/pre>\n<\/div>\n<\/a>\n<\/li>\n
TemporalFromTo<\/em><\/strong>
\nBelirli bir zaman aral\u0131\u011f\u0131 i\u00e7erisindeki verileri d\u00f6nd\u00fcr\u00fcr. Ba\u015flang\u0131\u00e7 ve biti\u015f zaman\u0131 dahil de\u011fildir!<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nvar posts = await context.Posts.TemporalFromTo(new DateTime(2022, 01, 23, 14, 55, 59), new DateTime(2022, 01, 23, 15, 00, 04)).Select(data => new\r\n{\r\n    Post = data,\r\n    PeriodStart = EF.Property<DateTime>(data, "PeriodStart"),\r\n    PeriodEnd = EF.Property<DateTime>(data, "PeriodEnd"),\r\n}).ToListAsync();\r\n\r\n.\r\n.\r\n.\r\n<\/pre>\n<\/div>\n<\/li>\nTemporalBetween<\/em><\/strong>
\nBelirli bir zaman aral\u0131\u011f\u0131 i\u00e7erisindeki verileri d\u00f6nd\u00fcr\u00fcr. Ba\u015flang\u0131\u00e7 dahil de\u011filken ve biti\u015f zaman\u0131 dahildir!<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nvar posts = await context.Posts.TemporalBetween(new DateTime(2022, 01, 23, 14, 55, 59), new DateTime(2022, 01, 23, 15, 00, 04)).Select(data => new\r\n{\r\n    Post = data,\r\n    PeriodStart = EF.Property<DateTime>(data, "PeriodStart"),\r\n    PeriodEnd = EF.Property<DateTime>(data, "PeriodEnd"),\r\n}).ToListAsync();\r\n.\r\n.\r\n.\r\n<\/pre>\n<\/div>\n<\/li>\nTemporalContainedIn<\/em><\/strong>
\nBelirli bir zaman aral\u0131\u011f\u0131 i\u00e7erisindeki verileri d\u00f6nd\u00fcr\u00fcr. Ba\u015flang\u0131\u00e7 ve biti\u015f zaman\u0131 dahildir!<\/p>\n\n\r\nTemporalExampleDb context = new();\r\n\r\nvar posts = await context.Posts.TemporalContainedIn(new DateTime(2022, 01, 23, 14, 55, 59), new DateTime(2022, 01, 23, 15, 00, 04)).Select(data => new\r\n{\r\n    Post = data,\r\n    PeriodStart = EF.Property<DateTime>(data, "PeriodStart"),\r\n    PeriodEnd = EF.Property<DateTime>(data, "PeriodEnd"),\r\n}).ToListAsync();\r\n.\r\n.\r\n.\r\n<\/pre>\n<\/div>\n<\/li>\n<\/ul>\nSilinmi\u015f Bir Veriyi Temporal Table’dan Geri Getirme<\/h4>\nSilinmi\u015f bir veriyi temporal table’dan bulup geri getirmek istiyorsak \u00f6ncelikle yap\u0131lmas\u0131 gereken ilgili verinin silindi\u011fi tarihi bulmam\u0131z gerekti\u011fidir. Ard\u0131ndan TemporalAsOf<\/code> fonksiyonu arac\u0131l\u0131\u011f\u0131yla silinen verinin ge\u00e7mi\u015f de\u011feri elde edilebilir ve fiziksel tabloya ta\u015f\u0131nabilir. Tabi b\u00f6yle bir durumda, ilgili verinin id s\u00fctununa kay\u0131t i\u015fleyebilmek i\u00e7in fiziksel tabloya ta\u015f\u0131ma i\u015fleminden \u00f6nce SET IDENTITY_INSERT<\/code> komutu \u00e7al\u0131\u015ft\u0131r\u0131lmal\u0131d\u0131r.<\/p>\n
Misal olarak, a\u015fa\u011f\u0131daki \u00f6rnekte \u00f6nceden sildi\u011fimiz ‘2’ id’sine sahip olan veri temporal table’dan bulunup geri fiziksel tabloya y\u00fcklenmektedir.<\/p>\n
\n\r\nTemporalExampleDb context = new();\r\n\r\nDateTime dateOfDelete = await context.Posts.TemporalAll()\r\n    .Where(p => p.Id == 2)\r\n    .OrderBy(p => EF.Property<DateTime>(p, "PeriodEnd"))\r\n    \/\/ En sonuncu i\u015flem yap\u0131lan tarihi al\u0131yoruz.\r\n    \/\/ \u00c7\u00fcnk\u00fc bu veri silindiyse muhtemelen en sonuncu\r\n    \/\/ i\u015flem yap\u0131lan tarihte silinmi\u015ftir.\r\n    .Select(data => EF.Property<DateTime>(data, "PeriodEnd"))\r\n    .LastAsync();\r\n\r\n\/\/Silinmeden \u00f6nceki en son kay\u0131t elde ediliyor.\r\nPost deletedPost = await context.Posts.TemporalAsOf(dateOfDelete.AddMilliseconds(-1))\r\n    .SingleAsync(data => data.Id == 2);\r\n\r\n\/\/Fiziksel tabloya ekleniyor.\r\nawait context.AddAsync(deletedPost);\r\n\r\n\/\/A\u015fa\u011f\u0131daki ExecuteSqlInterpolatedAsync fonksiyonu ile SET IDENTITY_INSERT komutunu tetikleyebilmek i\u00e7in connection'\u0131n a\u00e7\u0131lmas\u0131 gerekmektedir.\r\nawait context.Database.OpenConnectionAsync();\r\n\r\n\/\/SET IDENTITY_INSERT komutu \u00e7al\u0131\u015ft\u0131r\u0131l\u0131yor\r\nawait context.Database.ExecuteSqlInterpolatedAsync($"SET IDENTITY_INSERT dbo.Posts ON");\r\n\/\/Sorgu execute ediliyor.\r\nawait context.SaveChangesAsync();\r\n\/\/SET IDENTITY_INSERT komutu kapat\u0131l\u0131yor.\r\nawait context.Database.ExecuteSqlInterpolatedAsync($"SET IDENTITY_INSERT dbo.Posts OFF");\r\n<\/pre>\n<\/div>\nNihai olarak;
\nEF Core’un temporal table’lar\u0131 desteklemesi olduk\u00e7a ho\u015f ve g\u00fc\u00e7l\u00fc bir \u00f6zellik olman\u0131n yan\u0131nda, biz geli\u015ftiriciler i\u00e7in zamandan tasarruflu bir \u015fekilde verilerimiz \u00fczerinde ge\u00e7mi\u015fe dair analitik ve istatistiksel \u00e7al\u0131\u015fmalar konusunda b\u00fcy\u00fck \u00f6l\u00e7\u00fcde yard\u0131mc\u0131 bir niteliktir.<\/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, SQL Server 2016’da tan\u0131t\u0131lm\u0131\u015f olan Temporal Tables kavram\u0131, veri de\u011fi\u015fikli\u011fi s\u00fcre\u00e7lerinde kay\u0131tlar\u0131 depolayan ve zaman i\u00e7inde farkl\u0131 noktalardaki tablo verilerinin analizi i\u00e7in kullan\u0131lan ve sistem taraf\u0131ndan y\u00f6netilen tablolar\u0131 ifade etmektedir. EF Core, 6.0...<\/p>\n","protected":false},"author":1,"featured_media":11538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2914],"tags":[2851,4515,2850,4514,1421],"class_list":["post-24368","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-entity-framework-core","tag-ef-core","tag-ef-core-temporal-tables","tag-entity-framework-core","tag-entity-framework-core-temporal-tables","tag-temporal-tables"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24368","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=24368"}],"version-history":[{"count":29,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24368\/revisions"}],"predecessor-version":[{"id":24407,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/24368\/revisions\/24407"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media\/11538"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=24368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=24368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=24368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}