EntityFramework常用查询

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(1);
                    query = query.Where(p => p.AddDate < to);
                }
                query.Select(p => p.SignID).ToList().ForEach(p =>
                 {
                     if (!list.Contains(p))
                     {
                         list.Add(p);
                     }
                 });
                return list;
            }
        }