Linq備忘録

UNPIVOT

SQLのUnpivot

    var items = new List<Item>
    {
        new Item {Id = "1",  Flg1 = false, Flg2 = false, Flg3 = true, Flg4 = true },
        new Item {Id = "2",  Flg1 = false, Flg2 = true, Flg3 = true, Flg4 = true },
    };
    var results = items.SelectMany(x => new[]
    {
        new { Id = x.Id, Code = 1, Flg = x.Flg1 },
        new { Id = x.Id, Code = 2, Flg = x.Flg2 },
        new { Id = x.Id, Code = 3, Flg = x.Flg3 },
        new { Id = x.Id, Code = 4, Flg = x.Flg4 },
    });

差分

リストの差分取得

    var listLeft = new List<Item>
    {
        new Item { Id = 1, Name = "a" },
        new Item { Id = 2, Name = "b" },
        new Item { Id = 3, Name = "c" },
    };
    var listRight = new List<Item>
    {
        new Item { Id = 1, Name = "a" },
        new Item { Id = 3, Name = "c" },
        new Item { Id = 4, Name = "d" },
    };

    //両方（1, 3）
    var common = listLeft.Where(l => listRight.Any(r => r.Id == l.Id));

    //Leftのみ（2）
    var left = listLeft.Where(l => !listRight.Any(r => r.Id == l.Id));

    //Rightのみ（4）
    var right = listRight.Where(r => !listLeft.Any(l => l.Id == r.Id));

Containsの代わり

Containsの代わりにAnyを使う

var authors = new List<string>
{
    "川端康成",
    "夏目漱石"
};

var result = db.Authors.Where(x => authors.Any(xx => xx == x.Name)).ToList();
//こんなSQL（Containsと同じ）
//SELECT [a].[Id], [a].[Name]
//FROM [Authors] AS [a]
//WHERE [a].[Name] IN (N'川端康成', N'夏目漱石')