数値や文字列を右詰して前を「0（ゼロ）」で埋める（T-SQL）
T-SQLの小ネタです。
数値や文字列を右詰して前を「0（ゼロ）」で埋めた文字列を作ります。
数値の右詰と0埋め
数値の場合はSQL Server 2012から追加されているFORMAT関数を使います。C#と同じような書式を指定できます。
-- 3桁の数値を右詰と0埋めして5桁に declare @number int = 123; print format(@number, N'd5'); print format(@number, N'00000'); -- またはこれ -- 実行結果 -- 00123 -- 00123
SQL Server 2012以前は省略（・・・調べていません）。
参考
文字列の右詰と0埋め
文字列の場合はRIGHT関数を使って次のようにするといいみたいです。
-- 3桁の文字列を右詰と0埋めして5桁に declare @text varchar(3) = N'123'; print right('00000' + @text, 5); -- 実行結果 -- 00123
参考
数値や文字列を右詰して前を「0（ゼロ）」で埋める（C#）
C#の小ネタです。まぁいつも小ネタですが。
数値や文字列を右詰（右寄せ？）して前を「0（ゼロ）」で埋めた文字列を作ります。
数値の右詰と0埋め
数値の場合はstring.FormatメソッドかToStringメソッドで標準の10進数"D"数値書式指定を使います。"0"カスタム指定子を使ってもできますが標準のほうでいいのかな？と。
// 3桁の数値を右詰と0埋めして5桁に var number = 123; Console.WriteLine(string.Format($"{number:d5}")); // 標準の数値書式指定 Console.WriteLine(number.ToString("d5")); // ToStringを使うと Console.WriteLine(string.Format($"{number:00000}")); // カスタム数値書式指定 // 実行結果 // 00123 // 00123 // 00123
参考
文字列の右詰と0埋め
文字列の場合はstring.PadLeftメソッドを使います。
// 3桁の文字列を右詰と0埋めして5桁に var text = "123"; Console.WriteLine(text.PadLeft(5, '0')); // 実行結果 // 00123
参考
Entity Frameworkでenumのビット演算
FlagsAttributeを適用したenum（「ビットフラグ列挙体」と言うんですかね）に対して、Entity Frameworkでビット演算してみました。
ビット演算というよりHasFlagsメソッドを使えるよね？ということを確認した感じです。
エンティティとDBコンテキスト
いつものごとくエンティティとDBコンテキストを作ります。
// フラグ [Flags] public enum ItemFlags { Apple = 0x01, // 0000 0001 Pineapple = 0x02, // 0000 0010 Pen = 0x04, // 0000 0100 } // エンティティ public class Item { public int Id { get; set; } // フラグを持つ public ItemFlags Flags { get; set; } } // DBコンテキスト public class AppDbContext : DbContext { public IDbSet<Item> Items { get; set; } // 色々省略 }
データも用意
dbo.Itemテーブルを作ってデータを用意しておきます。
-- こんな感じ select * from dbo.Item; /* Id Flags ----------- ----------- 1 5 -- 0000 0101 2 1 -- 0000 0001 3 6 -- 0000 0110 */
取得してみる
HasFlagメソッドを使って取得してみます。
// _connectionStringとか色々省略 using (var dbContext = new AppDbContext(_connectionString)) { var items = dbContext.Items .Where(item => item.Flags.HasFlag(ItemFlags.Pen)) .ToList(); foreach (var item in items) { Console.WriteLine($"Id = {item.Id}, Flags = {item.Flags}"); } } // 実行結果 /* Id = 1, Flags = Apple, Pen Id = 3, Flags = Pineapple, Pen */
クエリはこんな感じです。
SELECT [Extent1].[Id] AS [Id], [Extent1].[Flags] AS [Flags] FROM [dbo].[Item] AS [Extent1] WHERE (( CAST( [Extent1].[Flags] AS int)) & ( CAST( 4 AS int))) = 4
HasFlagを使わない場合も。
using (var dbContext = new AppDbContext(_connectionString)) { var items = dbContext.Items .Where(item => (item.Flags & ItemFlags.Pen) == ItemFlags.Pen) .ToList(); foreach (var item in items) { Console.WriteLine($"Id = {item.Id}, Flags = {item.Flags}"); } } // 実行結果 /* Id = 1, Flags = Apple, Pen Id = 3, Flags = Pineapple, Pen */
クエリもこんな感じ。
SELECT [Extent1].[Id] AS [Id], [Extent1].[Flags] AS [Flags] FROM [dbo].[Item] AS [Extent1] WHERE 4 = (( CAST( [Extent1].[Flags] AS int)) & (4))
Entity FrameworkでTPT（Table Per Type）
今ごろ感ありますがEntity FrameworkでTPT（Table Per Type）を試しました。
データを追加する方法や取得する方法、どんなクエリになるのか、最終的には派生クラスではなく基本クラスだけを取得できるのかといったことを確認することが目的でした。
クエリはちょっとしたことで変わる気もするので参考程度といった感じです。またEntity Frameworkで基本クラスだけを取得する方法はわからなくて結局はDapperを使いましたが、試したサンプルを残しておきます。
あとEntity Framework Coreではないです。Entity Framework 6.1.3で試しています。
モデルとかDBコンテキストとか
次の継承関係があるモデルをDBで管理することを考えます。RectとCircleはShapeを派生している、あるあるな感じのモデルです。とりあえずShapeは抽象クラスにしてみます。
// 図形 public abstract class Shape { // ID [DatabaseGenerated(DatabaseGeneratedOption.None)] public int Id { get; set; } public string Stroke { get; set; } // 線の色（#000000） public override string ToString() { return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke} }}"; } } // 矩形 public class Rect : Shape { public double X { get; set; } public double Y { get; set; } public double Width { get; set; } public double Height { get; set; } public override string ToString() { return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke}, " + $"{nameof(X)} = {X}, {nameof(Y)} = {Y}, " + $"{nameof(Width)} = {Width}, {nameof(Height)} = {Height} }}"; } } // 円 public class Circle : Shape { public double X { get; set; } public double Y { get; set; } public double Radius { get; set; } public override string ToString() { return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke}, " + $"{nameof(X)} = {X}, {nameof(Y)} = {Y}, {nameof(Radius)} = {Radius} }}"; } }
それぞれのテーブルを作るクエリもぺたっと。
drop table if exists Circle; drop table if exists Rect; drop table if exists Shape; create table Shape( Id int not null, Stroke varchar(6) not null, constraint PK_Shape primary key(Id)); create table Rect( Id int not null, X float not null, Y float not null, Width float not null, Height float not null, constraint PK_Rect primary key(Id), constraint FK_Rect_Shape foreign key(Id) references Shape(Id)); create table Circle( Id int not null, X float not null, Y float not null, Radius float not null, constraint PK_Circle primary key(Id), constraint FK_Circle_Shape foreign key(Id) references Shape(Id));
DBコンテキストを用意します。IDbSetのプロパティはShapeクラスだけでいいっぽいです。RectやCircleの取得や追加などもShapesプロパティを使います。
// DBコンテキスト public class AppDbContext : DbContext { public AppDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { Database.Log = message => Debug.WriteLine(message); } // RectもCircleもこのプロパティを使う public IDbSet<Shape> Shapes { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); // テーブルは単数形 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); // モデルのマッピング modelBuilder.Entity<Circle>().ToTable("Circle"); modelBuilder.Entity<Rect>().ToTable("Rect"); } }
データの投入
まずはRectを1つ投入してみます。
// Rectを1つ投入 using(var dbContext = new AppDbContext(_connectionString)) { dbContext.Shapes.Add(new Rect { Id = 1, Stroke = "ff0000", X = 1.0, Y = 2.0, Width = 3.0, Height = 2.0, }); dbContext.SaveChanges(); }
ログでクエリを確認すると一度にShapeとRectの2つのテーブルにinsertしていることが確認できます。
-- 実行されたクエリ INSERT [dbo].[Shape]([Id], [Stroke]) VALUES (@0, @1) -- @0: '1' (Type = Int32) -- @1: 'ff0000' (Type = String, Size = -1) -- Executing at 2016/10/06 17:49:08 +09:00 -- Completed in 8 ms with result: 1 INSERT [dbo].[Rect]([Id], [X], [Y], [Width], [Height]) VALUES (@0, @1, @2, @3, @4) -- @0: '1' (Type = Int32) -- @1: '1' (Type = Double) -- @2: '2' (Type = Double) -- @3: '3' (Type = Double) -- @4: '2' (Type = Double) -- Executing at 2016/10/06 17:49:08 +09:00 -- Completed in 5 ms with result: 1
この後取得するためにRectとCircleをもう少しinsertしておきます。
// Cicleを2つとRectを1つ投入 using (var dbContext = new AppDbContext(_connectionString)) { dbContext.Shapes.Add(new Circle { Id = 2, Stroke = "0000ff", X = 0.0, Y = 1.0, Radius = 2.0, }); dbContext.Shapes.Add(new Circle { Id = 3, Stroke = "0000ff", X = 0.0, Y = 0.0, Radius = 3.0, }); dbContext.Shapes.Add(new Rect { Id = 4, Stroke = "00ff00", X = 2.0, Y = 2.0, Width = 4.0, Height = 4.0, }); dbContext.SaveChanges(); }
データを取得
すべて取得してみます。ShapesプロパティからCircleもRectも取得できます。
// すべての図形を取得 using(var dbContext = new AppDbContext(_connectionString)) { foreach (var shape in dbContext.Shapes.ToList()) { Console.WriteLine($"{shape.GetType()}: {shape}"); }; } // 結果 /* ConsoleApp.Circle: { Id = 2, Stroke = #0000ff, X = 0, Y = 1, Radius = 2 } ConsoleApp.Circle: { Id = 3, Stroke = #0000ff, X = 0, Y = 0, Radius = 3 } ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 } ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 } */
クエリはleft outer joinを想像しましたがunion all。
-- 実行されたクエリ SELECT CASE WHEN ([UnionAll1].[C5] = 1) THEN '0X0X' ELSE '0X1X' END AS [C1], [UnionAll1].[Id] AS [C2], [Extent3].[Stroke] AS [Stroke], CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[X] END AS [C3], CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[Y] END AS [C4], CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[Radius] END AS [C5], CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C1] END AS [C6], CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C2] END AS [C7], CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C3] END AS [C8], CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C4] END AS [C9] FROM (SELECT [Extent1].[Id] AS [Id], [Extent1].[X] AS [X], [Extent1].[Y] AS [Y], [Extent1].[Radius] AS [Radius], CAST(NULL AS float) AS [C1], CAST(NULL AS float) AS [C2], CAST(NULL AS float) AS [C3], CAST(NULL AS float) AS [C4], cast(1 as bit) AS [C5] FROM [dbo].[Circle] AS [Extent1] UNION ALL SELECT [Extent2].[Id] AS [Id], CAST(NULL AS float) AS [C1], CAST(NULL AS float) AS [C2], CAST(NULL AS float) AS [C3], [Extent2].[X] AS [X], [Extent2].[Y] AS [Y], [Extent2].[Width] AS [Width], [Extent2].[Height] AS [Height], cast(0 as bit) AS [C4] FROM [dbo].[Rect] AS [Extent2]) AS [UnionAll1] INNER JOIN [dbo].[Shape] AS [Extent3] ON [UnionAll1].[Id] = [Extent3].[Id]
派生クラスだけを取得する
Rectクラスだけを取得してみます。OfTypeメソッドを使います。
// Rectをすべて取得 using(var dbContext = new AppDbContext(_connectionString)) { foreach (var shape in dbContext.Shapes.OfType<Rect>().ToList()) { Console.WriteLine($"{shape.GetType()}: {shape}"); }; } // 結果 /* ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 } ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 } */
クエリはinner joinでした。想像したとおり。
-- 実行されたクエリ SELECT '0X0X' AS [C1], [Extent1].[Id] AS [Id], [Extent1].[Stroke] AS [Stroke], [Extent2].[X] AS [X], [Extent2].[Y] AS [Y], [Extent2].[Width] AS [Width], [Extent2].[Height] AS [Height] FROM [dbo].[Shape] AS [Extent1] INNER JOIN [dbo].[Rect] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
基本クラスだけを取得する
今回のようにテーブルを分ける理由の1つとして「基本クラスだけの情報を取得することがある」のかなと思います。なのでこれが知りたかったのですが分からずでした・・・。
試したコードはこちらです。まずOfType
// CircleもRectも取れる using(var dbContext = new AppDbContext(_connectionString)) { foreach (var shape in dbContext.Shapes.OfType<Shape>().ToList()) { Console.WriteLine($"{shape.GetType()}: {shape}"); }; } // 結果 /* ConsoleApp.Circle: { Id = 2, Stroke = #0000ff, X = 0, Y = 1, Radius = 2 } ConsoleApp.Circle: { Id = 3, Stroke = #0000ff, X = 0, Y = 0, Radius = 3 } ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 } ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 } */
ということで結局はDapperでした。（Shapeのabstractをやめてからの）
// DapperでShapeとして取得 using (var dbContext = new AppDbContext(_connectionString)) { var sql = "select * from dbo.Shape;"; foreach (var shape in dbContext.Database.Connection.Query<Shape>(sql)) { Console.WriteLine($"{shape.GetType()}: {shape}"); } } // 結果 /* ConsoleApp.Shape: { Id = 1, Stroke = #ff0000 } ConsoleApp.Shape: { Id = 2, Stroke = #0000ff } ConsoleApp.Shape: { Id = 3, Stroke = #0000ff } ConsoleApp.Shape: { Id = 4, Stroke = #00ff00 } */
DateTime.AddMonthsとDateTime.AddYearsのメモ
ちょっとした発見。
DateTime.AddMonthsメソッドは、結果の月にその日が存在しない場合はその月の末日に調整してくれます。
文章にするといまいちなのでコードで例を。
// 2016/08/31 + 1ヶ月 // 2016年9月は30日までなので結果は2016/09/30になる new DateTime(2016, 8, 31).AddMonths(1); // 結果 // 2016/09/30 0:00:00
DateTime.AddYearsも同じ動き。
// 2016/02/29 + 1年 // 2017年2月は28日までなので結果は2017/02/08になる new DateTime(2016, 2, 29).AddYears(1); // 結果 // 2017/02/28 0:00:00
知らなかった。
MSDNにも書いてありました。
結果として得られる日が有効な結果として得られる、月 1 日でないと、結果として得られる月の最終有効日が使用されます。例では、3 月 31 日 + 1 か月 = 4 月 30 日です。
ちょっと訳が微妙な部分もあるので英語のほうがわかりやすいかなって気もします。
If the resulting day is not a valid day in the resulting month, the last valid day of the resulting month is used. For example, March 31st + 1 month = April 30th, and March 31st - 1 month = February 28 for a non-leap year and February 29 for a leap year.
