Sql语句、存储过程:
1、无参数查询
var model = db.Database.SqlQuery<UserInfo>("select* from UserInfoes ").ToList();
2、有参查询
var model = db.Database.SqlQuery<UserInfo>("select* from UserInfoes where id=@ID ",new SqlParameter("@ID",id)).ToList();
3、结合linq查询
var model = (from p in db.userinfo.SqlQuery("select * from UserInfoes where id=@ID",new SqlParameter("@ID", 1)) select p).ToList();
4、EF 执行delete删除语句
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@ID",id)
};
db.Database.ExecuteSqlCommand("delete UserInfoes where id=@ID", para);
5、EF执行存储过程删除,有几个参数,存储后面要带几个参数以逗号分隔
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@ID",id)
};
db.Database.ExecuteSqlCommand("sp_Userinfos_deleteByID @ID", para);
EF原始查询单独表
1、分页查询
db.userinfo.OrderByDescending(a => a.ID).Skip(10 * (当前页 - 1)).Take(10).ToList();
2、只搜索某些字段
from c in db.Company select new { c.ID, c.CompanyName, c.Email, c.HtmlUrl };
3、动态查询
public List<string> GetSignIDList(int yewuTypeID,string addFromDate, string addToDate)
{
using (BoFeiEntitie entity = new BoFeiEntitie())
{
List<string> list = new List<string>();
var query = from p in entity.YeWuRecord_ShouFei where p.YeWuTypeID == yewuTypeID select new { p.AddDate, p.SignID };
if (!string.IsNullOrEmpty(addFromDate))
{
DateTime from = ConvertHelper.GetDateTime(addFromDate);
query = query.Where(p => p.AddDate >= from);
}
if (!string.IsNullOrEmpty(addToDate))
{
DateTime to = ConvertHelper.GetDateTime(addToDate).AddDays();
query = query.Where(p => p.AddDate < to);
}
query.Select(p => p.SignID).ToList().ForEach(p =>
{
if (!list.Contains(p))
{
list.Add(p);
}
});
return list;
}
}