IT業界のすみっこ暮らし

ふと気がついたときの記録

個人的によく使うlinqサンプル

よく書いてる内容をまとめてみました。今後も随時追記予定。

Model

Sample Model

// List<TestModel> testList = new List<TestModel>();
public class TestModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public string Remarks { get; set; }
}

// List<TestSubModel> testSubList = new List<TestSubModel>();
public class TestSubModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Total { get; set; }
}

ForEach

testList.ForEach(x => x.Remarks = x.Remarks + "追記文" );
testList.ForEach(x => { if (x.Id == 1) { x.Name = "内容"; x.Remarks = "内容"; } });

OrderBy

testList.OrderByDescending(x => x.IsMainPackage).ThenBy(x => x.PackageId).ToList();

GroupBy

List<TestSubModel> testSubList = testList.GroupBy(x => x.Id).Select(m => new TestSubModel
{
    Id = m.First().Id,
    Name = m.First().Name,
    Total = m.Sum(x => x.Price * x.Quantity)
 }).ToList();

あいまい検索

var list = new string[]{"one", "two", "three"};
if(list.Contains("one"))
{
    // listにあるもの
}
if(!list.Contains("four"))
{
    // listにないもの
}

その他

複数GroupByほか

var list = from x in testList
           where x.Id == 13
           group x by new { x.Name, x.Quantity } into g
           select new
           {
               g.Key.Name,
               g.Key.Quantity,
               g.Key.Price
           };

Join

var list = (from ft in db.first_table
            join st in db.second_table on ft.Id equals st.Id
            join tt in db.third_table on ft.DetailId equals tt.DetailId into temp
            from tt in temp.DefaultIfEmpty() // LEFT OUTER JOIN 
            where ft.Id == 13
            select new Detail
            {
                DetailId = ft.DetailId,
                Account = st.Account,
                CustomerTel = tt.CustomerTel
            }).OrderByDescending(x=>x.DetailId).ThenBy(x=>x.Account).ToList();

複数条件でJoin

var list = (from ft in db.first_table
            join st in db.second_table on new { Id = ft.Id, Account = "Account" } equals new { Id = st.Id, Account = st.Account } // join by multiple values
            where ft.Id == 13
            select new Detail
            {
                DetailId = ft.DetailId,
                Account = st.Account,
                CustomerTel = tt.CustomerTel
            }).OrderByDescending(x=>x.DetailId).ThenBy(x=>x.Account).ToList();

サブクエリの利用

pie001.hatenablog.com

1、tb1から同じDetailIdを持つ複数のレコードの中で、一番最新の日付のものを抽出する

var list = (from t in db.tb1
           group t by t.DetailId into g
           select g.OrderByDescending(e => e.LastUpdatedAt).FirstOrDefault() into p
           select new 
           {
               Id = p.Id,
               DetailId = p.DetailId,
               LastUpdatedAt = p.LastUpdatedAt
           }).ToList();

2、tb1から同じDetailIdを持つ複数のレコードの中で、一番最新の日付のもの、且つステータスが10であるもので、tb2と紐付いてるNameも抽出する

var list = (from t in db.tb1
            group t by t.OrderDetailId into g
            select g.OrderByDescending(e => e.LastUpdatedAt).FirstOrDefault() into p
            join tt in db.tb2 on p.DetailId equals tt.DetailId
            where 
                p.Status == 10
           select new 
           {
               Id = p.Id,
               DetailId = p.DetailId,
               Name = tt.Name,
               LastUpdatedAt = p.LastUpdatedAt
           }).ToList();

3、個別のIQueryableクエリーをjoinしてサブクエリーとして使う

var userQuery = db.user.Where(x => x.delete_flg == false);
var items = (from t in db.item
    join uq in userQuery on t.user_id equals uq.user_id
     where t.category == 1
    select t).ToList();

SQL文から作成されたIQueryableをサブクエリーとして使うことは出来ないみたい。

[NotSupportedException]
Unable to create a constant value of type 'XXXXX'. Only primitive types or enumeration types are supported in this context.

pie001.hatenablog.com


List<string> から List<int>

var list1 = new List<string>();
list1.Add("1");
list1.Add("2");
list1.Add("3");
list1.Add("4");

List<int> list2 = new List<int>();
list2 = list1.Select(Int32.Parse).ToList();

List<int> から List<string>

var list1 = new List<int>();
list1.Add(1);
list1.Add(2);
list1.Add(3);
list1.Add(4);

List<string> list2 = new List<string>();
list2 = list1.Select(x=> x.ToString()).ToList();

object[] から List<string>

var obj = new object[] { "first", "second" };
var list = new List<object>(obj).Select(x => x.ToString()).ToList();