C# WebApi Odata之EF 不过滤

大家好!

sql server返回后的ODATA过滤，我想过滤之前只返回我的记录。

我有一个包含 + 100 万条记录的表。

我需要中止 OData 使用吗？ (我的天)...

我的系统开发已完成 60% 以上，并且全部使用 OData...(大约 300 小时...令人沮丧...)

这可能是一个错误，或者我在做一些疯狂的事情？

受影响的程序集

.NET 框架 4.5.2

Microsoft.Data.OData 5.6.0

系统.Web.Http.OData 5.3.1.0

Entity Framework 6.0.0.0

重现步骤

我有一个名为 Project 的实体，如下所示:

[Table("Project")] 
public partial class Project 
{ 
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] 
    public Project() 
    { 
        Batches = new HashSet<Batch>(); 
    } 
 
    [Key] 
    public int IdProject { get; set; } 
 
    public int IdCompany { get; set; } 
 
    [StringLength(100)] 
    public string Name { get; set; } 
 
    public virtual Company Company { get; set; } 
 
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] 
    public virtual ICollection<Batch> Batches { get; set; } 
} 

我正在使用以下方法过滤此 ODATA:

http://localhost:36983/odata/Project ?$filter=IdProject+eq+1527&$select=IdProject,名称

我的 Controller :

public class ProjectsController : ODataController 
{ 
    private IProtocolContext db = new IProtocolContext(); 
 
    public ProjectsController() 
    { 
        db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); 
    } 
 
    // GET: odata/Projects 
    [EnableQuery] 
    public PageResult<Project> GetProjects() 
    { 
        var properties = Request.ODataProperties(); 
        return new PageResult<Project>(db.Project, properties.NextLink, properties.TotalCount); 
    } 
 
    //... 
} 

ODATA 响应:

{ 
  "odata.metadata":"http://localhost:36983/odata/$metadata#Projects&$select=IdProject,Name","odata.count":"1","value":[ 
    { 
      "IdProject":1527,"Name":"Piloto - TSI 2006" 
    } 
  ] 
} 

预期结果

SELECT  
[Extent1].[IdProject] AS [IdProject],  
[Extent1].[Name] AS [Name] 
FROM  [dbo].[Project] AS [Extent1] 
WHERE IdProject = 1527 

实际结果

我的调试从 Entity Framework 返回了这个 sql:

SELECT  
[Extent1].[IdProject] AS [IdProject],  
[Extent1].[IdCompany] AS [IdCompany],  
[Extent1].[Name] AS [Name] 
FROM  [dbo].[Project] AS [Extent1] 

其他详细信息

这导致我出现性能问题

请您参考如下方法:

我的看起来像这样。请注意 ODataQueryOptions 的使用。

[HttpGet, Route("getPagedData")] 
public PageResult<MyEFClassToFilterOn> GetPagedData(ODataQueryOptions opts) 
{ 
     using (var db = new dbEntities()) 
     { 
        var myQueryable = _searchRepo.GetBatchQueryable(db); 
        var myFilteredQueryable = opts.ApplyTo(myQueryable.AsQueryable()) as IQueryable<MyEFClassToFilterOn>; 
        var result = myFilteredQueryable.ToList(); 
        var rowcount = result.Count(); 
        return new PageResult<MyEFClassToFilterOn>(emailBatch, null, rowcount); 
     } 
} 

我原来的 url 看起来像这样: http://localhost:59637/api/mycontroller/getPagedData/ ?$inlinecount=allpages&$filter=(substringof(%27test%27,tolower(FirstName)))%20or%20(substringof(%27test%27,tolower(LastName)))&$skip=0&$top=10

希望对您有所帮助!