6.クエリーサンプル

１．クエリー式

using (var db = new Model1())
{
    var query = from kokyaku in db.m_kokyaku
                where kokyaku.id >= 10 && kokyaku.id <= 20
                orderby kokyaku.id descending
                select kokyaku;

    foreach (var k in query)
    {
        Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1);
    }
}

 

２．ラムダ式　パターン１

using (var db = new Model1())
{
    var query = db.m_kokyaku
                .Where(x => x.id >= 10 && x.id <= 20)
                .OrderByDescending(x => x.id)
                .ToList();

    foreach (var k in query)
    {
        Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1);
    }
}

 

３．ラムダ式　パターン２

using (var db = new Model1())
{
    var query = db.m_kokyaku
                .Where(x => x.id >= 10 && x.id <= 20)
                .OrderByDescending(x => x.id)
                .Select(x => new
                {
                    x.id,
                    x.name,
                    x.tel1
                });

    foreach (var k in query)
    {
        Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1);
    }
}

 

４．テーブル連結

using (var db = new Model1())
{
    var query = from t in db.taiou_rireki
                join k in db.m_kokyaku on t.id equals k.id
                where k.sex == "1"
                select new
                {
                    t.id,
                    t.taiou_bi,
                    t.kenmei,
                    k.name,
                    k.corp_name
                };

    foreach (var k in query)
    {
        Console.WriteLine("id={0}, taiou_bi={1}, kenmei={2}, name={3}, corp_name={4}"
                            , k.id, k.taiou_bi, k.kenmei, k.name, k.corp_name);
    }

 

５．INSERT　単一行

using (var db = new Model1())
{
    var sts = new m_status
    {
        id = 5, status = "打合せ"
    };

    db.m_status.Add(sts);
    db.SaveChanges();
}

 

６．INSERT　複数行

using (var db = new Model1())
{
    m_status[] sts = new m_status[]
    {
        new m_status {id = 6, status = "会議" },
        new m_status {id = 7, status = "出張" },
    };

    db.m_status.AddRange(sts);
    db.SaveChanges();
}

 

７．DELETE　単一行

using (var db = new Model1())
{
    var sts = (from s in db.m_status
                where s.id == 4
                select s).Single();

    db.m_status.Remove(sts);
    db.SaveChanges();
}

 

８．DELETE　複数行

using (var db = new Model1())
{
    var sts = from s in db.m_status
                where s.id >= 5 && s.id <= 7
                select s;

    db.m_status.RemoveRange(sts);
    db.SaveChanges();
}

 

９．UPDATE　単一行１

using (var db = new Model1())
{
    var sts = db.m_status.Single(x => x.id == 3);

    sts.status = "保留";

    db.SaveChanges();
}

 

１０．UPDATE　単一行２

using (var db = new Model1())
{
    var sts = (from s in db.m_status
                where s.id == 3
                select s).FirstOrDefault();

    sts.status = "保留";

    db.SaveChanges();
}

 

１１．UPDATE　単一行３

using (var db = new Model1())
{
    var sts = new m_status()
    {
        id = 3,
        status = "保留"
    };

    db.m_status.Attach(sts);
    db.Entry(sts).State = System.Data.Entity.EntityState.Modified;
    db.SaveChanges();
}

 

１２．トランザクション処理

using (var db = new Model1())
{
    ((IObjectContextAdapter)db).ObjectContext.Connection.Open();

    using (var tx = new TransactionScope())
    {
        var sts = new m_status
        {
            id = 5,
            status = "打合せ"
        };

        db.m_status.Add(sts);
        db.SaveChanges();


        m_status[] sts2 = new m_status[]
        {
            new m_status {id = 6, status = "会議" },
            new m_status {id = 7, status = "出張" },
        };

        db.m_status.AddRange(sts2);
        db.SaveChanges();

        tx.Complete();
    }
}

 

１３．SQL文を直接発行

using (var db = new Model1())
{
    var q = db.Database.ExecuteSqlCommand("UPDATE m_status SET status = '出張' WHERE id = 7;");
}

 

１４．パラメータークエリー

using (var db = new Model1())
{
    var sql = @"SELECT * FROM m_status WHERE id = @id";
    var param = new SqlParameter("@id", 3);
    var sts = db.Database.SqlQuery<m_status>(sql, param);

    foreach (var st in sts)
    {
        Console.WriteLine("id={0}, status={1}", st.id, st.status);
    }
}