SQL对照LinQ的基本语句
查询全表：
string sql = "SELECT * FROM UserInfo"; var lamAll = db.UserInfo; var LinAll = from u in db.UserInfo select u;
条件查询
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。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;
更多精彩
