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.
T-SQL - BETWEENのメモ
BETWEENは自分ではあまり使わないのでたまに見かけるとあれ？含むの？含まないの？どっちだっけ？ってなります。
カップル専用アプリのことではないです。
between A and B
A以上かつB以下。AもBも含む。
select * from (values(1), (2), (3), (4), (5)) as t(c) where c between 2 and 4; -- 結果 /* c ----------- 2 3 4 */
not between A and B
Aより小さいまたはBより大きい。
select * from (values(1), (2), (3), (4), (5)) as t(c) where c not between 2 and 4; -- 結果 /* c ----------- 1 5 */
一度試しておくと記憶に残るかな？と。まあ忘れてたらこれを見る。
SQL Server - xmlデータ型のメソッド（query、value、nodes）を試す
そういえばT-SQLでxmlを扱ったことがないなーと思ったので、とりあえず基本の基本を押さえるために簡単なサンプルを書いてみましたというお話です。
まずxmlデータ型のメソッドはこれだけあります。
そのうちよく使うんじゃないかなと勝手に思うqueryメソッド、valueメソッド、nodesメソッドを使ってみます。
| 構文 | 簡単な説明 | 詳細 |
|---|---|---|
query('XQuery') |
xmlからxmlを取得する | query() メソッド (xml データ型) |
value('XQuery', 'SQLType') |
xmlからスカラー値を取得する | value() メソッド (xml データ型) |
nodes('XQuery') as Table(Column) |
xmlからxmlの行セットを取得する | nodes() メソッド (xml データ型) |
それぞれのメソッドの引数にはXQueryというxmlに問い合わせる文字列を渡します。この文字列は要素の位置をさすパスになることが多いのかなと思います。またXQueryのパスには省略構文があります。省略構文はよく使うのかなと思います。
queryメソッド、valueメソッド、nodesメソッドを使ってみる
ドキュメントを読むよりクエリを見た方がわかりやすいので早速見ていきましょう。
-- xmlを変数に用意 declare @xml xml = N' <root> <item id="1">abc</item> <item id="3">efg</item> <item id="2">xyz</item> <item id="5">lmn</item> </root>'; -- 変数@xmlに各メソッドを使ってみる select item.query('.') as Item, -- item要素自身をxmlとして取得 item.value('@id', 'tinyint') as Id, -- item要素のid属性の値をtinyintとして取得 item.value('.', 'nvarchar(3)') as Content -- item要素の中身をnvarchar(3)として取得 from -- root要素の子のitem要素をt(item)というテーブル(カラム)の行セットとして取得 @xml.nodes('/root/item') as t(item);
メソッド引数の「.」がカレント要素、「@」が属性の省略構文ですね。
このクエリを実行すると次の結果になります。
cross applyでnodesメソッド
上記ではxml型の変数に対してnodesメソッドを呼び出しました。テーブルのカラムにxml型のデータがある場合はどうしたらいいかというと、from句でcross applyを使ってnodesメソッドを呼び出すといいみたいです。
-- from句のcross applyでnodesメソッドを呼び出す select item.query('.') as Item, item.value('@id', 'tinyint') as Id, item.value('.', 'nvarchar(3)') as Content from (values(@xml)) as src(xml) -- srcテーブルのxmlカラムにxmlデータがあるとして cross apply xml.nodes('/root/item') as t(item);
このクエリを実行すると1つめのクエリと同じ結果が得られます。
これでとりあえずとっかかりができたかなと思います。
