在EF4(.NET 4)中,我们有了全新的API:ObjectContext.ExecuteStoreCommand(...)和 ObjectContext.ExecuteStoreQuery<T>(...)。从函数名不难知道前者是为了执行某一并无返回集的SQL 命令,例如UPDATE,DELETE操作;后者是执行某一个查询,并可以将返回集转换为某一对象。
using (var db = new CentaStaffEntities())
{
//查询 自定义实体
var presons = db.Database.SqlQuery<Person>("SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK)");
Console.WriteLine(presons.FirstOrDefault().CnName);
Console.WriteLine(presons.Count());
//查询 EF生成的实体
var staffs = db.Database.SqlQuery<Staff>("SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK)");
Console.WriteLine(staffs.FirstOrDefault().CnName);
Console.WriteLine(staffs.Count());
//多表查询 自定义实体
var tables = db.Database.SqlQuery<Tables>("SELECT TOP 10 SA.[AwardID],S.[StaffID],S.[CnName] FROM [CentaStaff].[CentaStaff].[Staff] S WITH(NOLOCK) INNER JOIN [CentaStaff].[CentaStaff].[StaffAward] SA WITH(NOLOCK) ON S.[StaffID]=SA.[StaffID]");
Console.WriteLine(tables.FirstOrDefault().CnName);
Console.WriteLine(tables.Count());
//修改和添加
//var execute = db.Database.ExecuteSqlCommand("UPDATE Person SET Name = 'Michael' WHERE PersonID = 1");
//if (execute > 0)
//{
// Console.Write("execute success");
//}
//查询 参数传递
string sql = "SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK) WHERE 1=1 ";
sql += " AND CnName=@CnName";
List<SqlParameter> sqlparameters = new List<SqlParameter>();
sqlparameters.Add(new SqlParameter("@CnName", "张三"));
var parameters = db.Database.SqlQuery<Staff>(sql, sqlparameters.ToArray());
Console.WriteLine(parameters.FirstOrDefault().CnName);
Console.WriteLine(parameters.Count());
}
//自定义实体类
public class Person
{
public string CnName
{ get; set; }
public string Gender
{ get; set; }
}
//多表查询
public class Tables
{
public Guid AwardID
{ get; set; }
public Guid StaffID
{ get; set; }
public string CnName
{ get; set; }
}