locked
[BUG] QueryProvider seems not to build the query string correctly when using datetimes. RRS feed

  • Question

  • Using the example below I have found that the QueryProvider is not encasing string values or datetime field inside single quotes.

    The Url being generated is as follow

    "http://localhost:8888/myService.svc/GetClientsByDate()?clientName=hello bear juice company&dateString=12/12/2000 00:00:00"

    Which Produces the error below
    <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> 
      <code></code> 
      <message xml:lang="en-GB">Bad Request - Error in query syntax.</message> 
    </error>


    To reproduce the error you can use the following code.

    class Client
        {
            public string ClientName
            {
                get;
                set;
            }
    
            public DateTime Date
            {
                get;
                set;
            }
        }
    
        class Program
        {
            static void Main(string[] args)
            {
                string clientName = "hello bear juice company";
                
                string DateString = "12/12/2000";
                DateTime date = DateTime.Parse(DateString);
    
                DataServiceContext ctx = new DataServiceContext(new Uri("http://localhost:8888/myService.svc"));
                DataServiceQuery allClients = ctx.CreateQuery<Client>("GetClientsByDate")
                                 .AddQueryOption("clientName", clientName)
                                 .AddQueryOption("dateString", date);
    
                Console.WriteLine(allClients.ToString());
            }

    At the ".AddQueryOption("dateString", date);" alternate from dateString to date fields so you can see both behaviors.



    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post is answered your question please mark as the answer and if it is helpful do like wise.
    Wednesday, May 13, 2009 10:25 AM

Answers

  • Since your service operation expects a string, you should ensure that the string is formatted in a manner that your service operation can interpret, but need not use the standard format I posted earlier.

    Also, due to the standard DateTime format including several characters that are significant to or not allowed in URLs, it may be safer to encode the datetime in some other way, such as base-64 encoding or a custom scheme. For instance, there are several known issues with slashes in URIs (due to security concerns, or so I'm told).

    I realize that this solution is non-ideal, but unfortunately the System.Uri class was not originally built to handle arbitrary data in the URI, and is not something that can be readily updated to meet our needs (due to the number of other systems depending on its current behavior).



    By the way, from the MSDN documentation for AddQueryOption:
    The query options are added to the resultant URI using ?name=value&name2=value2 … syntax where the name maps directly to the name parameter and the value is obtained by calling ToString on the value parameter.
    Which explains why you are seeing the default format for your DateTime.

    The ADO.NET Data Services client was shipped without built-in support for service operations, so AddQueryOption is not expected to be called with service operation parameters of particular types.

    Matt Meehan, ADO.NET Data Services (Astoria)
    Wednesday, May 13, 2009 6:55 PM
    Moderator

All replies

  • Clearly, something is not happening correctly, as a datetime in a data-services URI should be formatted like:

    datetime'yyyy-mm-ddThh:mm[:ss[.fffffff]]'

    NOTE: Spaces are not allowed between datetime and quoted portion. datetime is case-insensitive.The format actually allows anything from the DateTimeKind.RoundtripKind, which may preserve timezone information.

    As seen in 'Using ADO.NET Data Services' by Mike Flasko, found here under the heading 'Data Type Literal Representations'.

    Can you try manually putting the datetime into this format, using the overloaded DateTime.ToString that takes a specific format, before passing it to AddQueryOption?

    I will also check with the team and find out if this is a known behavior, and whether or not it has been changed for the upcoming v1.5 release.

    Matt Meehan, ADO.NET Data Services (Astoria)
    Wednesday, May 13, 2009 4:52 PM
    Moderator
  • Another question: is the service operation you're accessing expecting a DateTime object or a string?

    If it is the latter, then this problem may be the same as the other one you posted about, where the client library is not url-encoding the data that it puts into the URI. In this case, I again recommend trying the recent CTP, as it may resolve this problem.
    Matt Meehan, ADO.NET Data Services (Astoria)
    Wednesday, May 13, 2009 4:53 PM
    Moderator
  • It is the later, I havent downloaded the ctp yet as i cant apply to the current application, i have to download it later on separate machine etc. but i will try it. However as seen on the example on the original post, the datetime object (set as DateTime.Today) is being formatted as dd/mm/ccyy hh:mm:ss.
    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post is answered your question please mark as the answer and if it is helpful do like wise.
    Wednesday, May 13, 2009 6:29 PM
  • Since your service operation expects a string, you should ensure that the string is formatted in a manner that your service operation can interpret, but need not use the standard format I posted earlier.

    Also, due to the standard DateTime format including several characters that are significant to or not allowed in URLs, it may be safer to encode the datetime in some other way, such as base-64 encoding or a custom scheme. For instance, there are several known issues with slashes in URIs (due to security concerns, or so I'm told).

    I realize that this solution is non-ideal, but unfortunately the System.Uri class was not originally built to handle arbitrary data in the URI, and is not something that can be readily updated to meet our needs (due to the number of other systems depending on its current behavior).



    By the way, from the MSDN documentation for AddQueryOption:
    The query options are added to the resultant URI using ?name=value&name2=value2 … syntax where the name maps directly to the name parameter and the value is obtained by calling ToString on the value parameter.
    Which explains why you are seeing the default format for your DateTime.

    The ADO.NET Data Services client was shipped without built-in support for service operations, so AddQueryOption is not expected to be called with service operation parameters of particular types.

    Matt Meehan, ADO.NET Data Services (Astoria)
    Wednesday, May 13, 2009 6:55 PM
    Moderator
  • Ah ok I understand where you are coming from and I will definately build some work around this problem. I just thought something if the AddQueryOption uses the ToString on the value parameter can it instead be something like using the string.Format method to format the value string part of the query string option.

    See code snippet below

    public void AddQueryOption(string name, string value, string format)
    {
    	string valueFormattedString = string.Format((string.IsNullOrEmpty(format)) ? "{0}" : format, value);
    
    	/* code for astoria */	
    }

    I found that if you add the single quoutes " ' " to the value option of the string the example works.

    anyhow thank you for your time looking at this Matt. I have marked your reply as the answer now.


    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post is answered your question please mark as the answer and if it is helpful do like wise.
    Friday, May 15, 2009 10:28 AM
  • UPDATE

    I have found that by using the DateTime.Parse method to create effectively DateTime.Today values date services query provider seems to be able to format the text correctly incassing inside single quoutes. See the example below

    class ClientAcquisition
        {
            public string ClientName
            {
                get;
                set;
            }
    
            public DateTime AcquisitionDate
            {
                get;
                set;
            }
    
            public override string ToString()
            {
                return string.Format("Company='{0}', AcquisitionDate='{1}'", this.ClientName, this.AcquisitionDate);
            }
        }
    
        class Program
        {
            static void Main(string[] args)
            {
                string clientName = "hello bear juice company";
                
                string DateString = "12/12/2000";
                DateTime date = DateTime.Parse(DateString);
    
                DataServiceContext ctx = new DataServiceContext(new Uri("http://localhost:50625/myService.svc"));
    
                IQueryable<ClientAcquisition> Clients = from c in ctx.CreateQuery<ClientAcquisition>("Acquisitions")
                                                        where c.AcquisitionDate > DateTime.Parse(DateTime.Today.Day + "/" + DateTime.Today.Month + "/" + DateTime.Today.Year)
                                                        select c;
    
                string a = Clients.ToString();
    
                List<ClientAcquisition> acquisitions = Clients.ToList();
    
                Console.WriteLine(Clients.ToString());
            }

    It produces the URL as follow.

    http://localhost:50625/myService.svc/Acquisitions()?$filter=AcquisitionDate%20gt%20datetime'2009-05-19T00:00:00'
    

    However if I just use DateTime.Today as part of the query we see the URl as follow.

    http://localhost:50625/myService.svc/Acquisitions()?$filter=AcquisitionDate%20gt%20datetime'2009-05-19T00:00:00+01:00'

    See that I am in the UK where we are in the british summer time which is + 1hour.

    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post is answered your question please mark as the answer and if it is helpful do like wise.
    Tuesday, May 19, 2009 12:31 PM