一、基础模糊查询
1.判断是否为空或者null
string.IsNullOrEmpty(des.PlateNum)————————>sql server的PlateNum is null的判断
from des in db.ModelsVehicleRecognition where (!string.IsNullOrEmpty(des.PlateNum)) select new { plateMun = des.PlateNum }
等同于
SELECT PlateNum FROM VehicleRecognition WHERE PlateNum IS NOT NULL
2.以xxx开头
des.PlateNum.StartsWith("皖A8") ————————>sql server 的 like '皖A8%'
from des in db.ModelsVehicleRecognition where (des.PlateNum.StartsWith("皖A8")) select new { plateMun = des.PlateNum }
等同于
SELECT PlateNum FROM VehicleRecognition WHERE PlateNum like '皖A8%'
3.以xxx结尾
des.PlateNum.StartsWith("皖A8") ————————>sql server 的 like '皖A8%'
from des in db.ModelsVehicleRecognition where (des.PlateNum.StartsWith("皖A8")) select new { plateMun = des.PlateNum }
等同于
SELECT PlateNum FROM VehicleRecognition WHERE PlateNum like '皖A8%'
4.包含xxx
des.PlateNum.Contains("A3") ————————>sql server 的 like '%A3%'
from des in db.ModelsVehicleRecognition where (des.PlateNum.Contains("A3")) select new { plateMun = des.PlateNum }
等同于
SELECT PlateNum FROM VehicleRecognition WHERE PlateNum like '%A3%'
二、麻烦模糊查询
1.精确到字符串对应数字符位置 类比 PatIndex()
SqlFunctions.PatIndex("_a__3%", des.PlateNum) > 0————————>sql server 的 like '_a__3%'
from des in db.ModelsVehicleRecognition where (SqlFunctions.PatIndex("_a__3%", des.PlateNum) > 0) select new { plateMun = des.PlateNum }
等同于
SELECT PlateNum FROM VehicleRecognition WHERE PlateNum like '_a__3%'
2.查询字段中指定字符个数 相关 类比len()
//字符数
var query = _member.GetQueryable()
.Where(q => (q.RecPosition.Length - q.RecPosition.Replace("|", "").Length) == 1);
等同于
select code, len(RecPosition)-len(REPLACE(RecPosition,'|','')) ,RecPosition
from member_info
where len(RecPosition)-len(REPLACE(RecPosition,'|',''))=1;
3.查询字节数量类比datalength()
//字节数筛选
var query = _member.GetQueryable()
.Where(q => (SqlFunctions.DataLength(q.RecPosition) - SqlFunctions.DataLength(q.RecPosition.Replace("|", ""))) == 1);
原理同上
更多：