C# WebApi Odata - EF Not Filtering

1
votes

Hello all!

ODATA filtering after sql server returned, i want to filter before and return only my records.

I have a table with + 1Milion records.

I will need to abort OData usage? (OMG)...

More than 60% of my system development is done and all using OData... (something like 300 hours... its frustrating..)

This could be a bug or i am doing something crazy?

Assemblies affected

.NET Framework 4.5.2

Microsoft.Data.OData 5.6.0

System.Web.Http.OData 5.3.1.0

EntityFramework 6.0.0.0

Reproduce steps

I have a entity named as Project like this:

[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; }
}

I'm filtering this ODATA using:

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

My 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 Response:

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

Expected result

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

Actual result

My debug returned this sql from entity framework:

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

Additional details

This is causing me performance issues

1
c#entity-frameworkasp.net-web-apiodata

1 Answers

3
votes

Mine looks like this. Note the use of 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);
     }
}

My original url looks something like this: 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

Hope that helps!