CODEWITHSUNDEEP
entity-frameworklinqwcfsql-server-2012odata
Mathematics
Mathematics

Reputation: 7628

OData is generating wrong URL or is it just me using wrong keyword

I have a query like this,

https://example.com/_vti_bin/exampleService/exampleService.svc/Categories?
$filter=Products/any(x:x/Status eq toupper('DELETED'))&
$select=ID,Products/Status,Products/Title&
$expand=Products

but it's not filtering dataset based on status = deleted and returns products which has status not deleted etc..

I looked at SQL trace and it is generating something like this,

exec sp_executesql N'SELECT 
    [Project2].[C1] AS [C1], 
    [Project2].[C2] AS [C2], 
    [Project2].[C3] AS [C3], 
    [Project2].[ID] AS [ID], 
    [Project2].[C4] AS [C4], 
    [Project2].[C5] AS [C5], 
    [Project2].[C8] AS [C6], 
    [Project2].[ID1] AS [ID1], 
    [Project2].[C6] AS [C7], 
    [Project2].[C7] AS [C8], 
    [Project2].[Title] AS [Title], 
    [Project2].[Status] AS [Status]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        1 AS [C1], 
        N''DataAccess.Product'' AS [C2], 
        N''ID'' AS [C3], 
        N''Products'' AS [C4], 
        N'''' AS [C5], 
        [Extent2].[ID] AS [ID1], 
        [Extent2].[Title] AS [Title], 
        [Extent2].[Status] AS [Status], 
        CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N''DataAccess.Product'' END AS [C6], 
        CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N''Title,Status,ID'' END AS [C7], 
        CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C8]
        FROM  [dbo].[Categories] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ProductID]
        WHERE ([Extent1].[ClientID] = @p__linq__0) AND ( EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Products] AS [Extent3]
            WHERE ([Extent1].[ID] = [Extent3].[ProductID]) AND (([Extent3].[Status] = (UPPER(N''DELETED''))) OR (([Extent3].[Status] IS NULL) AND (UPPER(N''DELETED'') IS NULL)))
        ))
    )  AS [Project2]
    ORDER BY [Project2].[ID] ASC, [Project2].[C8] ASC',N'@p__linq__0 int',@p__linq__0=23

Is it correct to use "eq" if I only want products whose status is "deleted" and nothing else ?

Edit

I am using OData V3, using WCF Data services with EF

Upvotes: 1

Views: 134

Answers (1)

War
War

Reputation: 8628

I believe the problem is in the query. Form the url you are saying something like ...

// get me categories
https://example.com/_vti_bin/exampleService/exampleService.svc/Categories?

// where any product is deleted
$filter=Products/any(x:x/Status eq toupper('DELETED'))&

// return the category id, product status and title
$select=ID,Products/Status,Products/Title&
$expand=Products

In other words you are filtering categories on the deleted status not products within them. You could add a second filter to handle the product filtering and only return categories and their filtered set of products.

Try something like this instead ...

https://example.com/_vti_bin/exampleService/exampleService.svc/Categories?
$filter=Products/any(x:x/Status eq toupper('DELETED'))&
$select=ID,Products/Status,Products/Title&
$expand=Products/any(p:p/Status eq toupper('DELETED'))

Depending on your situation it may be best turn the query around ...

https://example.com/_vti_bin/exampleService/exampleService.svc/Products?
$filter=Status eq toupper('DELETED')&
$select=Category/ID,Status,Title

... by pulling a set of products and their related category Id's you get the same result but gain the ability to filter those products directly on the base query instead of a more complex child collection filter.

As discussed in chat though, this does require a valid OData model where the relationship between Products and Categories is properly defined.

Upvotes: 1

Related Questions