none
Table query error

    Question

  • Hi Im using:

    https://www.nuget.org/packages/WindowsAzure.Storage/ (7.0.0)

    Like this i get some data from my table:

      var cloudTable = StorageContext.MyTable;// GetCloudTable();
      var query = cloudTable.CreateQuery<Myobject>().Where(d => d.customer == strCustomer && d.Timestamp >= dtmStart && d.Timestamp <= dtmEnd);
      List<Myobject> events = query.ToList();

    if (lstCategories.Count() > 0)
    {
           events = events.Where(item => lstCategories.Contains(item.category)).ToList();
    }


    But I cant seem to get it working in one call so i dont need to get all data first and look for items in my list<string> of categories?

    I try this with no succes:
    var query = cloudTable.CreateQuery<Myobject>().Where(d => d.customer == strCustomer && d.Timestamp >= dtmStart && d.Timestamp <= dtmEnd && lstCategories.Contains(d.category));

    It gives an exception
    The expression (((([10007].customer == "xx") And ([10007].Timestamp >= 09-06-2016 21:19:07 +02:00)) And ([10007].Timestamp <= 16-06-2016 21:19:07 +02:00)) And value(System.Collections.Generic.List`1[System.String]).Contains([10007].category)) is not supported.

    Can anyone help out?

    Thanks ;-)



    Thursday, June 16, 2016 7:22 PM

Answers

  • Hi Amigo,

    Thank you for posting here!

    When querying using Entity Framework there is an additional step to create a list of Ids as the .Contains() method needs to be called on a List of a primitive type (or you'll get an error along the lines of: Unable to create a constant value of type 'Model.Entities.MyEntity'. Only primitive types ('such as Int32, String, and Guid') are supported in this context).

    With WCF Data Services it is not possible to use .Contains() at all. This time the error returned will be something like: Pretty much the only option is to create WHERE predicate which checks for each Id in Ids:"The expression value(System.Collections.Generic.List`1[System.Int32]).Contains([10007].Id) is not supported".

    If you want to execute the code using LINQ support in Azure storage. We strongly recommend upgrading to Table Service Layer, because it is optimized for NoSQL scenarios and therefore provides much better performance.

    If you are querying with Linq or WCF support it will throw “not supported” exception.

    The ODataLib dependencies in the Storage Client Library for .NET are resolved through the ODataLib (version 5.0.2 and greater) packages available through NuGet, and not through WCF Data Services. The ODataLib libraries can be downloaded directly or referenced by your code project through NuGet. The specific ODataLib packages used by the Storage Client Library are ODataEdm, and Spatial. While these libraries are used by the Azure Table storage classes, they are required dependencies for programming with the Storage Client Library.

    For more information, kindly go through the links given below:

    https://azure.microsoft.com/en-in/documentation/articles/storage-dotnet-how-to-use-tables/

    https://msdn.microsoft.com/en-us/library/azure/dd894031.aspx

    Hope this helps.

    Regards,

    Pradeep

    If a post answers your question, please click Mark as Answer on that post and Vote as Helpful.

    Friday, June 17, 2016 6:06 AM
    Moderator

All replies

  • Hi Amigo,

    Thank you for posting here!

    When querying using Entity Framework there is an additional step to create a list of Ids as the .Contains() method needs to be called on a List of a primitive type (or you'll get an error along the lines of: Unable to create a constant value of type 'Model.Entities.MyEntity'. Only primitive types ('such as Int32, String, and Guid') are supported in this context).

    With WCF Data Services it is not possible to use .Contains() at all. This time the error returned will be something like: Pretty much the only option is to create WHERE predicate which checks for each Id in Ids:"The expression value(System.Collections.Generic.List`1[System.Int32]).Contains([10007].Id) is not supported".

    If you want to execute the code using LINQ support in Azure storage. We strongly recommend upgrading to Table Service Layer, because it is optimized for NoSQL scenarios and therefore provides much better performance.

    If you are querying with Linq or WCF support it will throw “not supported” exception.

    The ODataLib dependencies in the Storage Client Library for .NET are resolved through the ODataLib (version 5.0.2 and greater) packages available through NuGet, and not through WCF Data Services. The ODataLib libraries can be downloaded directly or referenced by your code project through NuGet. The specific ODataLib packages used by the Storage Client Library are ODataEdm, and Spatial. While these libraries are used by the Azure Table storage classes, they are required dependencies for programming with the Storage Client Library.

    For more information, kindly go through the links given below:

    https://azure.microsoft.com/en-in/documentation/articles/storage-dotnet-how-to-use-tables/

    https://msdn.microsoft.com/en-us/library/azure/dd894031.aspx

    Hope this helps.

    Regards,

    Pradeep

    If a post answers your question, please click Mark as Answer on that post and Vote as Helpful.

    Friday, June 17, 2016 6:06 AM
    Moderator
  • Thanks a lot Pradeep, I will look into it ;-)
    Sunday, June 19, 2016 7:43 PM