EF Linq字符串模糊查询整理

本是古典何须时尚 2022-06-06 11:13 442阅读 0赞

### **一、基础模糊查询** ###

**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结尾**

**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);
    原理同上

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

[EF Attatch()方法附加对象][EF Attatch]

[EF批量操作数据之EntityFramework.Extended][EF_EntityFramework.Extended]

[SqlServer]: http://blog.csdn.net/u011127019/article/details/74011508
[EF Attatch]: http://blog.csdn.net/u011127019/article/details/73381460
[EF_EntityFramework.Extended]: http://blog.csdn.net/u011127019/article/details/71538350