EF Linq字符串模糊查询整理

一、基础模糊查询

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,'|','')) ,RecPositionfrom member_infowhere len(RecPosition)-len(REPLACE(RecPosition,'|',''))=1;

3.查询字节数量类比datalength()

//字节数筛选var query = _member.GetQueryable().Where(q => (SqlFunctions.DataLength(q.RecPosition) - SqlFunctions.DataLength(q.RecPosition.Replace("|", ""))) == 1);原理同上 

更多：

SqlServer 大量数据热汇总优化整理

EF Attatch()方法附加对象

EF批量操作数据之EntityFramework.Extended