SQL对照Linq的基本语句

查询全表：

string sql = "SELECT * FROM UserInfo";
var lamAll = db.UserInfo;
var LinAll = from u in db.UserInfo select u;

条件查询

string sql2 = "SELECT * FROM UserInfo where UserId = 1";
var lamWhere = db.UserInfo.Where(u => u.UserId == 1);
var linWhere = from u in db.UserInfo where u.UserId == 1 select u;

Order by查询

string sql3 = "SELECT * FROM UserInfo where order by UserId desc";
var lamOrderby = db.UserInfo.OrderBy(u => u.UserId);
var linOrderby = from u in db.UserInfo orderby u.UserId descending select u;

带条件Order by

string sql4 = "SELECT * FROM UserInfo where UserId > 10 order by UserId desc/asc";
var lamOrderWhere = db.UserInfo.OrderByDescending(u => u.UserId).Where(u => u.UserId == 1);
var linOrderWhere = from u in db.UserInfo where u.UserId > 0 orderby u.UserId descending select u;

TOP语句

string sql5 = "SELECT TOP 10 * FROM UserInfo";
var lamTop = db.UserInfo.Take(10);
var linTop = (from u in db.UserInfo select u).Take(10);  //Take 从序列的开头返回指定元素

Like语句

string sql5 = "SELECT * FROM UserInfo where Name like '%张%'";
var lamLike = db.UserInfo.Where(u => u.Name.Contains("张"));
var linLike = from u in db.UserInfo where u.Name.Contains("王") select u;

表联合

string ss7 = "SELECT u.*,l.* FROM UserInfo as u inner join dbo.Logs as l on u.UserId = l.UserId";
var l7 = db.UserInfo.Join(db.Logs, u => u.UserId, l => l.UserId, (u, l) => new UserInfoJoinLogs { UserId = u.UserId, Name = u.Name, LName = l.LName });  //UserInfoJoinLogs为一个类，里边存放两表的数据
var s7 = from u in db.UserInfo join l in db.Logs on u.UserId equals l.UserId select new UserInfoJoinLogs { UserId = u.UserId, Name = u.Name, LName = l.LName };

in/not in语句

string UserId = "1,2,3,4";
int[] UserIdArr = { 1, 2, 3, 4 };
string ss8 = "SELECT * FROM UserInfo where UserId in(" + UserId + ")";
string sss8 = "SELECT * FROM UserInfo where UserId not in(" + UserId + ")";
var s8 = from u in db.UserInfo where (UserIdArr).Contains(u.UserId) select u;

简单函数的计算

 string max = "SELECT MAX(UserId) FROM UserInfo";
 string min = "SELECT min(UserId) FROM UserInfo";
 string count = "SELECT COUNT(UserId) FROM UserInfo";
 string sum = "SELECT SUM(UserId) FROM UserInfo";

 var lmax = db.UserInfo.Max(u => u.UserId);
 var lmin = db.UserInfo.Min(u => u.UserId);
 var lcount = db.UserInfo.Count();
 var lsum = db.UserInfo.Sum(u => u.UserId);


 var lqmax = (from u in db.UserInfo select u).Max(u => u.UserId);
 var lqmin= (from u in db.UserInfo select u).Min(u => u.UserId);
 var lqcount = (from u in db.UserInfo select u).Count();
 var lqsum = (from u in db.UserInfo select u).Sum(u => u.UserId);

跳过多少条数据查询剩余数据

 string ss9 = "SELECT * FROM  (SELECT ROW_NUMBER()over(order by UserId desc) as rowNum, * from [UserInfo]) as t where UserId>5";
 var l9 = db.UserInfo.OrderByDescending(u => u.UserId).Skip(10);
 var s9 = (from u in db.UserInfo orderby u.UserId descending select u).Skip(5);  //skip 跳过序列中指定的元素，返回剩余元素

And语句 Or语句 

string andsql = "SELECT * FROM UserInfo where UserId=1 and Name='张三'";
var andLab = db.UserInfo.Where(u => ((u.UserId == 1) && (u.Name == "张三")));
var orLab= db.UserInfo.Where(u => ((u.UserId == 1) || (u.Name == "张三")));
var andlinq = from u in db.UserInfo where u.UserId == 1 && u.Name == "张三" select u;
var orLinq = from u in db.UserInfo where u.UserId == 1 || u.Name == "张三" select u;

区间查询

string sectionSql = "SELECT * FROM UserInfo where UserId >=50 and UserId <=60 ";
var sectionLab = db.UserInfo.Where(e => (((Int32)(e.UserId) >= 2) && ((Int32)(e.UserId) <= 10)));
var sectionLinq = from u in db.UserInfo where u.UserId >= 50 && u.UserId <= 60 select u;

模糊查询加截取指定内容查询

string ss11 = "SELECT e.* FROM UserInfo AS e WHERE e.Name LIKE '张%' OR SUBSTRING(e.Name, 0, 2) = '张三'";
var sLab = db.UserInfo.Where(e => (e.Name.StartsWith("张") || (e.Name.Substring(0, 2) == "张")));
var s11 = from u in db.UserInfo where u.Name.StartsWith("张") || u.Name.Substring(0, 2) == "张三" select u;