search

OData / WebApi

OData Web API: A server library built upon ODataLib and WebApi
https://docs.microsoft.com/odata
Other
857 stars 473 forks source link

Odata Count Incorrect values on Odata Function [.Net Core] #1565

Open albertoVieiraNeto opened 6 years ago

albertoVieiraNeto commented 6 years ago

Short Description

After creating a custom Function on odata if one the navigation properties is expanded and a count added inside this expand, the numbers returned are completely wrong.

Assemblies affected

Version affected/Tested : Microsoft.AspNetCore.OData @7.0.1

Reproduce steps

register a custom function on the model builder:

   builder.EntitySet<Post>("Post");
            builder
                .EntityType<Post>()
                .Collection
                .Function("Mine")
                .ReturnsCollectionFromEntitySet<Post>(nameof(Post));

Create a function on the controller:

        [EnableQuery]
        [HttpGet]
        public IQueryable<Post> Mine(/*int? group = null*/)
        {

            var SubjectId = User.Claims.GetSubjectId();
            IQueryable<Post> result;

            result = (from postSubs in _DbContext.PostGroupSubscribe
                      join post in _DbContext.Post on postSubs.PostGroup.Id equals post.Group.Id
                      where postSubs.User.Id == SubjectId
                      select post);

            return result;
        }

Models Used

    public class Post : ModelBaseWithId
    {
        [StringLength(4000)]
        public string Content { get; set; }

        [StringLength(4000)]
        public string NormalizedText { get; set; }

        public DateTimeOffset ExpireDate { get; set; } = DateTimeOffset.Now;

        public virtual PostGroupCategory Category { get; set; }

        public virtual ICollection<PostLikes> Likes { get; set; }

        public virtual UserAccount Owner { get; set; }

        public virtual Post Parent { get; set; }

        public virtual ICollection<Post> Children { get; set; }

        public virtual ICollection<PostFiles> Files { get; set; }

        public virtual PostGroup Group { get; set; }

    }
    public class PostGroupSubscribe: ModelBaseWithId
    {
        public int PostGroupId { get; set; }
        public virtual PostGroup PostGroup { get; set; }

        public Guid UserId { get; set; }
        public virtual UserAccount User { get; set; }
    }
    public abstract class ModelBaseWithId
    {
        [Required]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public virtual Guid Guid { get; set; }

        [Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTimeOffset CreatedUtc { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; }
    }

Url that causes the error

http:////Post/Mine?$expand=children($count=true), Group, Likes($count=true)&$count=true&$filter=parent eq null

Expected result

Each expanded item should show the correct number of item for this collection.

{
    "@odata.context": "http://localhost:5010/Tenant/odata/v1/$metadata#Post",
    "@odata.count": 1,
    "value": [
        {
            "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjlzPSc=\"",
            "content": "teste",
            "normalizedText": "teste",
            "expireDate": "2018-08-08T13:50:23.5566667Z",
            "id": 1,
            "guid": "0fef1e92-ad0d-4cdf-8fb5-d57b9392dd95",
            "createdUtc": "2018-08-08T13:50:23.5566667Z",
            "rowVersion": "AAAAAAAAB9s=",
            "children@odata.count": 2,  //correct number
            "children": [
                {
                    "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjl3PSc=\"",
                    "content": "teste 2",
                    "normalizedText": "teste 2",
                    "expireDate": "2018-08-08T13:50:50.9833333Z",
                    "id": 2,
                    "guid": "8732c390-a248-4e74-8c74-b627834ad267",
                    "createdUtc": "2018-08-08T13:50:50.9833333Z",
                    "rowVersion": "AAAAAAAAB9w="
                },
                {
                    "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjkwPSc=\"",
                    "content": "teste 3",
                    "normalizedText": "teste 3",
                    "expireDate": "2018-08-08T13:50:59.1666667Z",
                    "id": 3,
                    "guid": "49b9f4af-2c90-4f19-86da-96660909cab6",
                    "createdUtc": "2018-08-08T13:50:59.1666667Z",
                    "rowVersion": "AAAAAAAAB90="
                }
            ],
            "group": {
                "name": "Empresa",
                "description": "empresa",
                "id": 1,
                "guid": "f7fec876-f09f-4c91-a716-625beb2991fe",
                "createdUtc": "2018-08-08T13:40:07.73Z",
                "rowVersion": "AAAAAAAAB9E="
            },
            "likes@odata.count": 1, //correct number
            "likes": [
                {
                    "id": 1,
                    "guid": "3c949e0d-9e5a-4336-b8fb-2269a9d3e855",
                    "createdUtc": "2018-08-08T15:56:00.07Z",
                    "rowVersion": "AAAAAAAAB9k="
                }
            ]
        }
    ]
}

Actual result

Each expanded item shows a completely wrong number even though the query seem correct when analised using the sql server profiler.

{
    "@odata.context": "http://localhost:5010/Tenant/odata/v1/$metadata#Post",
    "@odata.count": 1,
    "value": [
        {
            "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjlzPSc=\"",
            "content": "teste",
            "normalizedText": "teste",
            "expireDate": "2018-08-08T13:50:23.5566667Z",
            "id": 1,
            "guid": "0fef1e92-ad0d-4cdf-8fb5-d57b9392dd95",
            "createdUtc": "2018-08-08T13:50:23.5566667Z",
            "rowVersion": "AAAAAAAAB9s=",
            "children@odata.count": 1046935999568,
            "children": [
                {
                    "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjl3PSc=\"",
                    "content": "teste 2",
                    "normalizedText": "teste 2",
                    "expireDate": "2018-08-08T13:50:50.9833333Z",
                    "id": 2,
                    "guid": "8732c390-a248-4e74-8c74-b627834ad267",
                    "createdUtc": "2018-08-08T13:50:50.9833333Z",
                    "rowVersion": "AAAAAAAAB9w="
                },
                {
                    "@odata.etag": "W/\"YmluYXJ5J0FBQUFBQUFBQjkwPSc=\"",
                    "content": "teste 3",
                    "normalizedText": "teste 3",
                    "expireDate": "2018-08-08T13:50:59.1666667Z",
                    "id": 3,
                    "guid": "49b9f4af-2c90-4f19-86da-96660909cab6",
                    "createdUtc": "2018-08-08T13:50:59.1666667Z",
                    "rowVersion": "AAAAAAAAB90="
                }
            ],
            "group": {
                "name": "Empresa",
                "description": "empresa",
                "id": 1,
                "guid": "f7fec876-f09f-4c91-a716-625beb2991fe",
                "createdUtc": "2018-08-08T13:40:07.73Z",
                "rowVersion": "AAAAAAAAB9E="
            },
            "likes@odata.count": 1046935999568,
            "likes": [
                {
                    "id": 1,
                    "guid": "3c949e0d-9e5a-4336-b8fb-2269a9d3e855",
                    "createdUtc": "2018-08-08T15:56:00.07Z",
                    "rowVersion": "AAAAAAAAB9k="
                }
            ]
        }
    ]
}

Querys genereated

i got the querys this function generates using the profiler, they seem correct:

exec sp_executesql N'SELECT COUNT_BIG(*)
FROM [cosmos].[Post] AS [p3]
WHERE @_outer_Id2 = [p3].[ParentId]',N'@_outer_Id2 int',@_outer_Id2=1
go
exec sp_executesql N'SELECT COUNT_BIG(*)
FROM [cosmos].[PostLikes] AS [p4]
WHERE @_outer_Id3 = [p4].[NewsId]',N'@_outer_Id3 int',@_outer_Id3=1
go
exec sp_executesql N'SELECT [p].[Id], [p].[CategoryId], [p].[Content], [p].[CreatedUtc], [p].[ExpireDate], [p].[GroupId], [p].[Guid], [p].[NormalizedText], [p].[OwnerId], [p].[ParentId], [p].[RowVersion]
FROM [cosmos].[Post] AS [p]
WHERE @_outer_Id = [p].[ParentId]',N'@_outer_Id int',@_outer_Id=1
go
exec sp_executesql N'SELECT [p1].[Id], [p1].[CreatedUtc], [p1].[Guid], [p1].[NewsId], [p1].[RowVersion], [p1].[UserId]
FROM [cosmos].[PostLikes] AS [p1]
WHERE @_outer_Id1 = [p1].[NewsId]',N'@_outer_Id1 int',@_outer_Id1=1
go
biaol-odata commented 6 years ago

maybe related to https://github.com/OData/WebApi/issues/1564, since both are related to $count with expand.

albertoVieiraNeto commented 6 years ago

indeed they are related, I've found both of these issues when working on the same task when used with a function the counts works in this crazy way and when used on a default function it throws a error like the other issue.