Can ServiceStack do a query by System.DateTime value?
I am evaluating ServiceStack to figure out if it works for general purpose REST server building purposes, and I'm trying to extend the Northwind demo, which I have updated locally to use 4.0.44 of ServiceStack. The code with my tweaks is on Bitbucket here, dumped from git to hg, then with my own changes added.
The difference between my code and the upstream serviceStack.Examples northwind demo is the following additions:
A .../date/ Route was added to the ServiceModel.Operations object for Orders:
namespace ServiceStack.Northwind.ServiceModel.Operations
{
...
[Route("/orders")]
[Route("/orders/date/{ByDate}")] // ADDED!
[Route("/orders/page/{Page}")]
[Route("/customers/{CustomerId}/orders")]
public class Orders
{
public int? Page { get; set; }
public DateTime? ByDate { get; set; } // ADDED!
public string CustomerId { get; set; }
}
Then a handler for this case was added to the service, which blows up at runtime with a strange LINQ failure: variable 'o' of type 'ServiceStack.Northwind.ServiceModel.Types.Order' referenced from scope '', but it is not defined...
Source = System.Core
namespace ServiceStack.Northwind.ServiceInterface
{
public class OrdersService : Service
{
private const int PageCount = 20;
public object Get(Orders request)
{
List<Order> orders = null;
if (request.ByDate.HasValue)
{ // date provided
// broken LINQ #1 -- EXCEPTION HERE AT RUNTIME!
orders = Db.Select<Order>(order => order.Where<Order>(o => o.OrderDate.Value.Date == request.ByDate.Value.Date ));
/*
broken LINQ #2
orders =
Db.Select(
Db.From<Order>().
Where(o => (o != null) && o.OrderDate.HasValue && (request.ByDate.Value.Date == o.OrderDate.Value.Date)
)
);
*/
}
else if (request.CustomerId.IsNullOrEmpty())
{
orders = Db.Select<Order>(order => order.OrderByDescending(o => o.OrderDate))
.Skip((request.Page.GetValueOrDefault(1) - 1) * PageCount)
.Take(PageCount)
.ToList();
}
else
{
orders = Db.Select<Order>(order => order.Where(o => o.CustomerId == request.CustomerId));
}
if (orders.Count == 0)
The exception either indicates that I don't know how to write a LINQ expression to query by dates, or it may indicate an internal bug in the ORM for SQLite, or a bug in the overall framework, or that I am doing something wrong.
Similar runtime crashes also reproduce for the Firebird provider, when I try to query by DateTime values, so it may be a bug in both the SQLite and Firebird ORMLite providers.
System.InvalidOperationException was unhandled by user code
HResult = -2146233079
Message = variable 'o' of type 'ServiceStack.Northwind.ServiceModel.Types.Order' referenced from scope '', but it is not defined
Source = System.Core
StackTrace:
at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.Compiler.VariableBinder.VisitUnary(UnaryExpression node)
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.Compiler.VariableBinder.Bind(LambdaExpression lambda)
at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
at ServiceStack.OrmLite.SqlExpression`1.VisitMemberAccess(MemberExpression m)
at ServiceStack.OrmLite.SqlExpression`1.Visit(Expression exp)
at ServiceStack.OrmLite.SqlExpression`1.VisitBinary(BinaryExpression b)
at ServiceStack.OrmLite.SqlExpression`1.Visit(Expression exp)
at ServiceStack.OrmLite.SqlExpression`1.VisitBinary(BinaryExpression b)
at ServiceStack.OrmLite.SqlExpression`1.Visit(Expression exp)
at ServiceStack.OrmLite.SqlExpression`1.VisitLambda(LambdaExpression lambda)
at ServiceStack.OrmLite.SqlExpression`1.Visit(Expression exp)
at ServiceStack.OrmLite.SqlExpression`1.AppendToWhere(String condition, Expression predicate)
at ServiceStack.OrmLite.SqlExpression`1.Where(Expression`1 predicate)
at ServiceStack.Northwind.ServiceInterface.OrdersService.Get(Orders request) in C:devServiceStack.ExamplessrcServiceStack.NorthwindServiceStack.Northwind.ServiceInterfaceOrdersService.cs:line 19
at lambda_method(Closure, Object, Object)
at ServiceStack.Host.ServiceRunner`1.Execute(IRequest request, Object instance, TRequest requestDto)
InnerException:
Workaround:
If I just write an SQL where clause statement myself, it works:
if (request.ByDate.HasValue)
{ // date provided
string condition = string.Format("OrderDate = '{0}'", request.ByDate.Value.Date.ToString("yyyy-MM-dd") );
orders = Db.Select<Order>(condition);
ServiceStack can certainly query by DateTime. But in your case you are trying to query by date part only, ignoring time (something like date(OrderDate) = date(ByDate)). Thing is - expression you use for that (o.OrderDate.Value.Date == request.ByDate.Value.Date) might look simple, but it is really represented by sometimes complex expression tree. This expression tree is explored by ORM and converted to raw sql query. Now, expressions can be arbitrary complex and of course not all of them can be converted to sql queries. Developer of ORM should explicitly think about what expressions might be useful to implement. In this case, most likely, developer of ORM did not handle the case when you access .Date property of DateTime instance, or maybe it's even not possible to handle such case for some reason. You expect this would be converted to something like date(OrderDate) sql, but it is not. Other ORMs (entity framework as example) has special functions, which you call inside expression (like this: o => DbFunctions.Date(o.OrderDate)), but ServiceStack does not have such function for date-only comparision, as far as I know.
Long story short - I'd say it's not a bug but just not supported feature. Of course, they might handle that and throw better exception, but it is as it is.
Finally to just query by DateTime, as your question states, just do something like:
var byDate = request.ByDate.Value.Date;
orders = Db.Select<Order>(order => order.OrderDate == byDate);
上一篇: ORMLite SQL Server更新