Microsoft.Azure.Cosmos.Table doesnt support filterting

[IdeaHunter]

I have tried to use filtering on PartitionKey and it not working

void Main()
{
    var acc = Microsoft.Azure.Cosmos.Table.CloudStorageAccount.Parse("UseDevelopmentStorage=true;DevelopmentStorageProxyUri=http://ipv4.fiddler");
    var tables = acc.CreateCloudTableClient();
    var table = tables.GetTableReference("Tasks"); 
    table.DeleteIfExists();
    table.CreateIfNotExists();
    var entry = new AuditEntity("1", "some",new Dictionary<string,object>(),new Dictionary<string,Type>());
    TableOperation insert = TableOperation.Insert(entry);
    table.Execute(insert);
    entry = new AuditEntity("2", "some",new Dictionary<string,object>(),new Dictionary<string,Type>());
    insert = TableOperation.Insert(entry);
    table.Execute(insert);
    var subitemQuery = table.CreateQuery<AuditEntity>();
    subitemQuery.FilterString = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal,Convert.ToString(1));
    subitemQuery.TakeCount = 1000;
    subitemQuery.FilterString.Dump();
    table.ExecuteQuery(subitemQuery).Count().Dump();
}
    public class AuditEntity : ITableEntity
    {
        public AuditEntity()
        {

        }
        public AuditEntity(string pk, string rk,Dictionary<string,object> props,Dictionary<string,Type> types)
        {
            PartitionKey = pk;
            RowKey = rk;
            Props = props;
            Types = types;
        }

        public string PartitionKey { get; set; }
        public string RowKey { get; set; }
        public Dictionary<string, object> Props { get; private set; }
        public Dictionary<string, Type> Types { get; private set; }
        public DateTimeOffset Timestamp { get; set; }
        public string ETag { get; set; }

        public void ReadEntity(IDictionary<string, EntityProperty> properties, OperationContext operationContext)
        {
            Props = properties.ToDictionary(p => p.Key, p => p.Value.PropertyAsObject);
            Types = properties.ToDictionary(p => p.Key, p => ToClr(p.Value.PropertyType));

        }

        private Type ToClr(EdmType propertyType)
        {
            switch (propertyType)
            {
                case EdmType.Binary:
                    return typeof(byte[]);
                case EdmType.Boolean:
                    return typeof(bool?);
                case EdmType.DateTime:
                    return typeof(DateTime?);
                case EdmType.Double:
                    return typeof(double?);
                case EdmType.Int32:
                    return typeof(int?);
                case EdmType.Int64:
                    return typeof(long?);
                case EdmType.String:
                    return typeof(string);
                case EdmType.Guid:
                    return typeof(Guid?);
                default:
                    throw new NotImplementedException();
            }
        }

        public IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
        {
            return Props
                .ToDictionary(kv => kv.Key,kv=> ToAzureProp(kv.Value, kv.Key));
        }

        private EntityProperty ToAzureProp(object arg,string key)
        {
            switch (arg)
            {
                case bool b:
                    return new EntityProperty(b);
                case double d:
                    return new EntityProperty(d);
                case string s:
                    return new EntityProperty(s);
                case byte bt:
                    return new EntityProperty((int)bt);
                case int i:
                    return new EntityProperty(i);
                case long l:
                    return new EntityProperty(l);
                case byte[] bArr:
                    return new EntityProperty(bArr);
                case DateTime dt:
                    return new EntityProperty(dt);
                case decimal m:
                    return new EntityProperty(Convert.ToString(m));
                case null:
                    var type = Types[key];
                    if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        var subtype = type.GetGenericArguments().Single();
                        switch (subtype.Name)
                        {
                            case nameof(Decimal):
                                return EntityProperty.GeneratePropertyForString(null);
                            case nameof(Double):
                                return EntityProperty.GeneratePropertyForDouble(null);
                            case nameof(Int32):
                                return EntityProperty.GeneratePropertyForInt(null);
                            case nameof(Int64):
                                return EntityProperty.GeneratePropertyForLong(null);
                            case nameof(Boolean):
                                return EntityProperty.GeneratePropertyForBool(null);
                            case nameof(DateTime):
                                return new EntityProperty((DateTime?)null);
                            case nameof(DateTimeOffset):
                                return EntityProperty.GeneratePropertyForLong(null);
                            default:
                                if(subtype.IsEnum)
                                    return EntityProperty.GeneratePropertyForInt(null);

                                throw new NotImplementedException(subtype.Name);
                        }
                    }
                    else
                    {
                        switch (type.Name)
                        {
                            case nameof(String):
                                return EntityProperty.GeneratePropertyForString(null);
                            default:
                                throw new NotImplementedException();
                        }
                    }
                default:
                    if (arg.GetType().IsEnum)
                        return EntityProperty.GeneratePropertyForInt((int)(byte)arg);
                    throw new NotImplementedException();
            }
        }
    }

subitemQuery loads 2 items whenever it should load single item also i used fiddler to check what kind of request it made and i expected something like ?$filter=PartitionKey eq '1' in a request url but it simply isnt there

Library version: 1.0.1

[donghexu]

Hi @IdeaHunter, Thanks for reporting this. I also notice this won't work either with the legacy storage sdk as well. I'll see if this is by design or an actual bug.

Alternatively, you can still use the following for Query: subitemQuery = new TableQuery(); subitemQuery.Where( TableQuery.GenerateFilterCondition( "PartitionKey", QueryComparisons.Equal, Convert.ToString(1)));

Thanks,

[IdeaHunter]

@donghexu it is look by a design, however i have found a way to do to queries, but API is very unusual

tables.GetTableReference($"{tableNamePrefix}{subtable}");
                    var subitems = table.CreateQuery<AuditEntity>()
                        .AsQueryable<AuditEntity>()
                        .Where(e=>e.ParentPartitionKey == Convert.ToString(itemId) && e.ParentRowKey == version)
                        .ToList();

First it understands Linq, second after the query had been build , ToList should be called on query and not ExecuteQuery on table. Calling ToList is the only way to apply query. Also the fact that you can set FilterString is not supported(not sure why it is possible via api). With how filter works now FilterString setter should be internal and not exposed and causing confusion

[donghexu]

Hi @IdeaHunter , thanks very much for the information. As you mentioned, the API is unusual, so my recommendation is to use the common pattern as mentioned above. The way you pasted seems only work for Quaryable case that understanding Linq, I'll explore it in a later manner. I will evaluate the FilterString access level. But since it is public since the beginning, I doubt we should now make the setter internal. I'll see what I dig out.

[wmengmsft]

Closing this thread. Original issue is due to a mix up of Linq based query API with OData based query APIs.