Odata Query can't be translated to Linq

Programming
, , ,
1

I am having difficulty getting the OData query to work on .Net Core 3.1 after upgrading from .Net Core 2.1. I receive the following error:

System.InvalidOperationException: The LINQ expression ‘$it’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

I have two tables defined as follows:

    public class WorkItem
    {
        [Key]
        [Required]
        public Guid Id { get; set; }

        [Required]
        [MaxLength(128)]
        public string Name { get; set; }

        public IEnumerable<Resource> Resources { get; set; }

        public WorkItem(string name)
        {      
            Name = name;
        }
    }

public class Resource
    {
        [Key]
        [Required]
        public Guid Id { get; set; }

        [Required]
        public Guid WorkItemId { get; set; }

        [Required]
        [MaxLength(128)]
        public string Name { get; set; }

        [Required]
        public Guid StorageId { get; set; }

        public DateTime CreateDate { get; set; }

        public ContentItem ContentItem { get; set; }

        public AppItem AppItem { get; set; }

        public Resource(Guid workItemId, string name, Guid storageId)
        {
            WorkItemId = workItemId;
            Name = name;
            StorageId = storageId;
        }
    }

The request URI is as follows:

_appItemsBaseUri/response.Id?$expand=Resources($filter=Name eq ‘test2.pdf’)";

It successfully hits the API, filters the data, and creates the OData SingleResult. If I expand it, I can see that there is a WorkItem and the Resource is there.

var dto = dataset.Where(workItemId==responseId);
var res = SingleResult.Create(dto);

However, it does not return from the API when I do the following:

// This results in the error mentioned above.
return Ok(res);

The only way to get it to work is to use it like this:

var dto = dataset.Where(workItemId==responseId);
var res = SingleResult.Create(dto).Queryable.FirstOrDefault();
return Ok(res)

I am using the following package versions:

  • Microsoft.AspNetCore.Odata 7.3.0
  • Microsoft.EntityFrameworkCore 3.1.0

Please explain why the code needs to do a client evaluation, and how I can return the data without having to do a client evaluation.

2

The error message indicates that the LINQ expression used in the OData query cannot be translated to SQL by the Entity Framework. This means that the query is being evaluated on the client side instead of the database server.

To avoid the client evaluation, you can try modifying the query to use a supported LINQ expression that can be translated to SQL by the Entity Framework. For example, instead of using $it in the OData query, you can use a property name that is defined in the database.

_appItemsBaseUri/_response.Id_?$expand=Resources($filter=Name eq 'test2.pdf';$select=Id,Name,StorageId)

This query uses $select to specify which properties to include in the result set. By selecting only the properties that are required, the query can be translated to SQL by the Entity Framework and executed on the database server.

Alternatively, you can try upgrading to a newer version of the Microsoft.AspNetCore.OData package, as newer versions may have better support for translating LINQ expressions to SQL.