search

OData / WebApi

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

$filter nullable integer in odata url #1068

Closed a-elnajjar closed 5 years ago

a-elnajjar commented 7 years ago

I am trying to set odata URL $filter, but I have some problem with and "Status eq -1" not working because Status property is nullable integer int? in Assessment Class I have this exception the url

localhost/Assessments?$expand=Client&$filter=CatalogId%20eq%20'PVAT'%20and%20Status%20eq%20-1&$select=FirstName%2CLastName { "error": { "code": "", "message": "An error has occurred.", "innererror": { "message": "The binary operator AndAlso is not defined for the types 'System.Nullable1[System.Boolean]' and 'System.Boolean'.", "type": "System.InvalidOperationException", "stacktrace": " at System.Linq.Expressions.Expression.AndAlso(Expression left, Expression right, MethodInfo method)\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression)\r\n at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)\r\n at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.ProcessComparisonExpression(BinaryExpression binaryExpression)\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression)\r\n at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)\r\n at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)\r\n at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)\r\n at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection1 bodyClauses, QueryModel queryModel)\r\n at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)\r\n at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)\r\n at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)\r\n at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, ILogger logger, Type contextType)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_01.<CompileQuery>b__0()\r\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQuery[TResult](Expression query)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\r\n at Remotion.Linq.QueryableBase1.System.Collections.IEnumerable.GetEnumerator()\r\n at System.Web.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)\r\n at System.Web.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)\r\n at System.Web.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)\r\n at System.Web.OData.Formatter.ODataMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content, HttpContentHeaders contentHeaders)\r\n at System.Web.OData.Formatter.ODataMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Net.Http.HttpContent.d49.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Owin.HttpMessageHandlerAdapter.d13.MoveNext()" } } }

fenixil commented 7 years ago

Hi @a-elnajjar , As you could see from the stack trace, exception is thrown from EntityFrameworkCore when it tried to build the query. OData supports nullable types pretty fine, so I suspect that you have conversion issue in your EF entities. For example, if your column is type of bit then, you might get such error as -1 could not be converted to boolean.

If you could provide more details, perhaps I could help you.

a-elnajjar commented 7 years ago

public class Client : BaseEntity { public Client() { Assessments = new HashSet(); AssessmentScoreHistory = new HashSet(); ClientTags = new HashSet(); Notifications = new HashSet(); ReportNotifications = new HashSet(); ReportHistory = new HashSet(); CreatedDate = DateTime.UtcNow; } public Guid Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public string ParticipantId { get; set; } public DateTime? BirthDate { get; set; } public int Gender { get; set; } public byte? Age { get; set; } public int Status { get; set; } public string Notes { get; set; } public virtual Language DefaultLanguage { get; set; } public virtual User User { get; set; } public virtual ICollection Tags { get; set; } public virtual ICollection Notifications { get; set; } public virtual ICollection ReportNotifications { get; set; } public virtual ICollection Assessments { get; set; } public virtual ICollection AssessmentScoreHistory { get; set; } public virtual ICollection ReportHistory { get; set; } public HashSet ClientTags { get; set; } }

public class Assessment : BaseEntity
{

    public Assessment(){}
    public Guid Id { get; set; }
    public int? Status { get; set; }
    public int AssessmentNumber { get; set; }
    public virtual Language Language { get; set; }
    public virtual AssessmentType AssessmentType { get; set; }
    public User User { get; set; }
    public Guid? ClientId { get; set; }
    public virtual Client Client { get; set; }
    public String ClientName { get; set; }
    public DateTime? AssessmentDate { get; set; }
    public virtual Catalog Catalog { get; set; }
    public string CatalogId { get; set; }
    public virtual Rater Rater { get; set; }
    public virtual AssessmentData AssessmentData { get; set; }
    public virtual AssessmentResume AssessmentResume { get; set; }
    public virtual AssessmentScoreHistory AssessmentScoreHistory { get; set; }
    public string JSONData { get; set; }
    //Ignore in Database
    public Client GetClient { get; set; }

} from DbContaxt //client modelBuilder.Entity().ToTable("Clients"); modelBuilder.Entity().HasKey(x => x.Id); modelBuilder.Entity().Property(x => x.Id).ForSqlServerHasDefaultValueSql("newid()"); //<-- this line should create new Guid in insert modelBuilder.Entity().Ignore(x => x.DisplayName); modelBuilder.Entity().Ignore(x => x.DisplayAgeInfo); modelBuilder.Entity().Property(x => x.FirstName).HasMaxLength(50); modelBuilder.Entity().Property(x => x.LastName).HasMaxLength(50); modelBuilder.Entity().Property(x => x.ParticipantId).HasMaxLength(50); modelBuilder.Entity().Property(x => x.Email).HasMaxLength(100); modelBuilder.Entity().HasOne(x => x.DefaultLanguage).WithMany(x => x.Clients).HasForeignKey("DefaultLanguageId"); modelBuilder.Entity().HasOne(x => x.User).WithMany(x => x.Clients).HasForeignKey("UserId"); modelBuilder.Entity().HasMany(c => c.ClientTags).WithOne(c => c.Client).HasForeignKey("ClientId"); modelBuilder.Entity().HasMany(c => c.Assessments).WithOne(c => c.Client).HasForeignKey("ClientId"); //2-Assessment modelBuilder.Entity().ToTable("Assessments"); modelBuilder.Entity().HasKey(x => x.Id); modelBuilder.Entity().Property(x => x.Id).ForSqlServerHasDefaultValueSql("newid()"); modelBuilder.Entity().Property(x => x.AssessmentNumber).ValueGeneratedOnAdd(); modelBuilder.Entity().Ignore(x => x.GetClient); modelBuilder.Entity().Property(x => x.JSONData); modelBuilder.Entity().Property(x => x.AssessmentDate).HasColumnType("datetime"); modelBuilder.Entity().HasOne(x => x.Language).WithMany(x => x.Assessments).HasForeignKey("LanguageId"); modelBuilder.Entity().HasOne(x => x.AssessmentData).WithOne(x => x.Assessment).HasForeignKey(x => x.Id); modelBuilder.Entity().HasOne(x => x.AssessmentResume).WithOne(x => x.Assessment).HasForeignKey(x => x.Id); modelBuilder.Entity().HasOne(x => x.AssessmentScoreHistory).WithOne(x => x.Assessment).HasForeignKey(x => x.Id); modelBuilder.Entity().HasOne(x => x.Catalog).WithMany(x => x.Assessments).HasForeignKey("CatalogId"); modelBuilder.Entity().HasOne(x => x.User).WithMany(x => x.Assessments).HasForeignKey("UserId"); modelBuilder.Entity().HasOne(x => x.Client).WithMany(x => x.Assessments).HasForeignKey("ClientId"); ApplyJsonPropertyConfig(modelBuilder);

Assessments DML [Id] [uniqueidentifier] NOT NULL, [Status] [int] NULL, [AssessmentNumber] [int] IDENTITY(1,1) NOT NULL, [ClientId] [uniqueidentifier] NULL, [ClientName] nvarchar NULL, [AssessmentDate] [datetime] NULL, [JSONData] nvarchar NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedDate] [datetime] NULL, [DeletedDate] [datetime] NULL, [IsDeleted] [bit] NOT NULL, [AssessmentTypeId] [uniqueidentifier] NULL, [CatalogId] nvarchar NULL, [LanguageId] [int] NULL, [UserId] [uniqueidentifier] NULL, CONSTRAINT [PK_dbo.Assessments] PRIMARY KEY CLUSTERED Clients DML [Id] [uniqueidentifier] NOT NULL, [FirstName] nvarchar NULL, [LastName] nvarchar NULL, [Email] nvarchar NULL, [ParticipantId] nvarchar NULL, [BirthDate] [datetime] NULL, [Gender] [int] NOT NULL, [Age] [tinyint] NULL, [Status] [int] NOT NULL, [Notes] nvarchar NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedDate] [datetime] NULL, [DeletedDate] [datetime] NULL, [IsDeleted] [bit] NOT NULL, [DefaultLanguageId] [int] NULL, [UserId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_dbo.Clients] PRIMARY KEY CLUSTERED

we are using Entity framework core1.2.1, Microsoft.AspNet.OData 6.0.0 Microsoft.OData.Core 7.3.1 Microsoft.OData.Edm 7.3.1

kferstl commented 7 years ago

same problem here, using odata3, i think this is an odata issue, would be great if you could assist :-)

Stracktrace

Exception has been thrown by the target of an invocation.","type":"System.Reflection.TargetInvocationException","stacktrace":"   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Web.Http.OData.ExpressionHelpers.Count(IQueryable query, Type type)
   at System.Web.Http.OData.Query.InlineCountQueryOption.GetEntityCount(IQueryable query)
   at System.Web.Http.OData.Query.ODataQueryOptions.ApplyTo(IQueryable query, ODataQuerySettings querySettings)
   at System.Web.Http.OData.EnableQueryAttribute.ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
   at System.Web.Http.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor)
   at System.Web.Http.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
   at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()","internalexception":{
        "message":"The binary operator AndAlso is not defined for the types 'System.Boolean' and 'System.Nullable`1[System.Boolean]'.","type":"System.InvalidOperationException","stacktrace":"   at System.Linq.Expressions.Expression.AndAlso(Expression left, Expression right, MethodInfo method)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.ProcessComparisonExpression(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.LongCount[TSource](IQueryable`1 source)"
      }

URL Called (decoded) http://localhost:9002/odata/Products?$filter=(ProjectId eq guid'e4b44742-0cf7-479d-be41-054603b3d6cc') and (ProductListId eq guid'9e6110e1-a8bf-48a0-9ff6-b53a68e7a292')&$orderby=Name&$top=15&$inlinecount=allpages

Relevant Properties

public class Product ... {
  public Guid? ProjectId { get;set; }
  public Guid ProductListId { get;set; }
}

looks like the this the nullable ProjectId check results in a nullable boolean, instead of the expected false for all null values. Removing the ProjectId Filter from the URL solves the problem, but that of course is not an option ;)

the same code using entity framework core directly works as expected:

public class ProductsController : ODataController{
  [HttpGet]
  [EnableQuery]
  [Authorize]
  public IHttpActionResult Get() {
    var projectId = Guid.Parse("e4b44742-0cf7-479d-be41-054603b3d6cc");
    var productListId = Guid.Parse("9e6110e1-a8bf-48a0-9ff6-b53a68e7a292");
    var db = new AppDbContext();
    return Ok(db.Products.Where(i => i.ProjectId == projectId && i.ProductListId == productListId));
  }
}