Entity Framework 小技巧—— 在EF中运行SQL命令

1. 使用ExecuteStoreCommand：执行Update,Insert，Delete语句（返回受影响行数）

using (SzmbEntities entity = new SzmbEntities())
{
    var item = entity.Weatherwarnings.OrderByDescending(x => x.Id)
                .Where(x => x.PublishTime < now.AddDays(-14))
                .FirstOrDefault();
    if (item != null)
    {
        string sql = "Delete FROM  [Weatherwarning] where Id < @ID";
        var args = new DbParameter[] { 
        new SqlParameter { ParameterName = "ID", Value = item.Id} 
        };
        entity.ExecuteStoreCommand(sql, args);
    }
}

using (var ctx = new MyObjectContext())
{
    ctx.ExecuteStoreCommand("UPDATE Person SET Name = 'Michael' WHERE PersonID = 1");
}

2. 查询一行一列信息

var singleResult = db.ExecuteStoreQuery<string>("select count(*) as counts from userinfo");
string result = string.Empty;
foreach (var item in singleResult)
{
    result = item;
}

3. 使用ExecuteStoreQuery<T> ：通过sql查询返回object实体

using (var ctx = new MyObjectContext())
{
    var peopleViews = ctx.ExecuteStoreQuery<PersonView>("SELECT PersonID, Name FROM Person");
}

public class PersonView
{
    public int PersonID { get; set; }
    public string Name { get; set; }
}