Feature request: Add support for CosmosDB

It would be nice to have support for working directly with the Cosmos DB LINQ provider as opposed to working with the EF provider as EF does not generate valid SQL in many situations.

The one thing I'm unsure about is how to handle the $expand option here. What makes sense from a users perspective.

Ignore the expand option
Generate a SelectMany call when $expand is specified (I don't think the user would expect this)
Throw an InvalidOperationException
For each query generate an implicit Select call which includes all properties except for collection properties on the runtime generated type. If $expand is specified then "include" the specified collection property in the runtime generated type.

It depends on whether the concept of navigation properties (both entities and collections) exists in Cosmos DB. If it does not then I would throw the exception. If it does exist then how is it represented as a LINQ query i.e. do what you would do manually.

Maybe it would help to observe the existing code with regard to select and expand, and what happens when none, all or a partial set have been specified.

In this case there is no concept of a navigation property in Cosmos DB. You can have collections (primitives or objects) but they're not navigation properties. Nothing officially connects one document to another.

You can of course embed an ID of one document into another:

{
    "id": "some user id",
    "name": "bill",
    "posts": [ 
        "id": "some post id",
        "userId": "some user id"
    ] 
}

But you cannot "join" those documents together (either with the C# SDK or Cosmos DB SQL API).

E.g., Users.Include(u => u.Posts)
Is not valid. Two separate queries would have to be made in this case, one to get the users and one to get the posts of a particular user.

First: Users.Where(u => u.Name.Contains("bill");
Followed by: Posts.Where(p => p.UserId == user.Id);

@BlaiseD

@BlaiseD Do you know off the top of your head why GetAsync doesn't call GetQueryAsync and then just call ToListAsync directly on the returned IQueryable<T>?

Get, GetAsync were the initial implementation, used joins (includes), call Map and return a collection. GetQuery, GetQueryAsync came later, use projection and return an IQueryable.

Just doing manual testing at the moment, everything looks good.
One question I do have is what Odata query options are currently supported?

I know that the following are supported:

$select
$filter
$orderby
$count
$top
$skip
$expand

I don't believe transformations are currently supported, e.g., $apply correct?

Maybe after this PR I'll look into adding transformations, but it'll probably require creating a dynamic runtime type (which I believe I could use this for). Also, returning an IQueryable<TModel> wouldn't be possible. But that's a problem for another day.

I believe that's all for options. FYI - there's a discussion here about configuring the EnableQueryAttribute to ignore the supported options and handle the apply piece - as a workaround.

Some dedicated classes may be worth considering for transforms. There are scenarios e.g. iOS Xamarin/Maui where anonymous types won't work.

It looks as if I've found a bit of a bug in how we handle expansions. Currently in order to expand a complex type the following syntax is used $expand=MyComplexType\SomeNavigationProperty which we don't seem to currently handle correctly. I need to take a closer look at the LinqExtensions to get a better idea of how we're handling the segments.

I know we're assuming the following expansion syntax: $expand=MyType($expand=SomeNavigationProperty) but this of course implies that MyType is an entity and not a complex type.

This may be a specific CosmosDB concern as I don't believe a collection of complex types makes sense for a relational DB.

Ok, so it looks like everything is working as expected (at least when tested manually).
Now comes the harder portion, testing...

For other projects I've used Testcontainers to spin up a docker image running the Cosmos DB emulator.

I was thinking of doing the same here for integration testing. Thoughts?

Don't think I've used one. The only restriction is that anyone should be able to clone the repository build and test. The tests e.g. AutoMapper.OData.EF6.Tests should also work in the CI and release builds - not the web tests we switch those off.

BTW adding this Cosmos DB piece to your repo for your own NuGet library is also an option - adding it here is great too.

Using Docker containers (using the Testcontainers library) for testing I believe requires Docker to be installed. Unfortunately there is no in-memory Cosmos database, only the Cosmos DB emulator. My biggest concern was the CI.

Originally I was planning on adding the feature here but if you'd feel more comfortable with me creating a separate Nuget package I can do that as well.

I'm not familiar with the technology so whatever works with the CI. The expression builder tests check a string representation of the expression (in this case IQueryable.Expression) so that's also an option - ultimately that's all the libraries are doing.

Cosmos DB looks like a good fit with the other two libraries. I brought up your own NuGet because of the whole dynamic: my lack of knowledge of Cosmos DB, your work etc.. in case you hadn't thought of it.

So, it looks like the windows-latest runner already has docker installed, which means I could use the Cosmos DB emulator Docker container in both the CI and Release actions.

The issue, which may be a deal breaker, would be the requirement for the user to have docker installed on their machine in order to run the Cosmos DB tests.

I'm also looking into how I could potentially create a default CosmosClient which is what would provide me with the needed IQueryable in order to test against. If that works, I could generate a QueryDefinition (this is Cosmos specific) and then test against the generated SQL.

The problems here is I wouldn't be able to test the Get and GetAsync extensions methods.
There isn't a lot of logic in those two methods as I just call GetQueryAsync and GetQuery and execute the returned IQueryable.

The other issue would be when using $sount as that'll hit the DB.

I see the following logic in FilterHelper though - so not sure if is running in your scenario:

                    if (conversionType.IsEnum)
                    {
                        if (!(sourceNode is ConstantNode enumSourceNode))
                        {
                            if (GetClrType(sourceNode.TypeReference) == typeof(string))
                                return new ConstantOperator(null);

                            throw new ArgumentException("Expected ConstantNode for enum source node.");
                        }

                        return new ConvertToEnumOperator
                        (
                            GetConstantNodeValue(enumSourceNode, conversionType),
                            conversionType
                        );
                    }

Yeah that was one of the first things I saw so I put a break point in there last night and it never hit. That piece of code you showed would only get hit if the user is manually casting:

entities?$expand=Children($filter=MyEnums/any(value: cast(value, Edm.String) eq 'Value1'))

What I've tentatively done is create a new type implementing the IExpressionPart interface which inserts the needed convert expressions after we've constructed the IExpressionPart tree.

Ideally the convert node would be added while we're building the tree of course but this is a more complicated task.
I'll take a closer look at that later today.

Yes. It should be inserted wherever it's recognized as a Microsoft.OData.UriParser.QueryNode.

I'm assuming the following was deleted by you?

"I wonder if it's a bug in the OData parser as it never gets recognized as a QueryNode".

I don't see it in the thread.

The reference should be here. Then special handling for enums within that code.

Also, the OData repository could be helpful if it works at the root level.

Yeah I deleted those because I misunderstood the question initially.
I have things mostly working but I hit a bit of a road block when it comes to Nullable types at run time.

This is a bit of an interesting problem.
Currently we have the following filter we use in a test:

var filter = GetFilter<DataTypes>("NullableSimpleEnumProp in ('First', 'Second')");

We're taking a Nullable enum and checking to see if a List<Nullable<enum>> contains said value.
What's complicated here is dealing with the conversion from a Nullable enum value to the Nullable underlying type (an int in this case).

There isn't a way to convert an int constant to its nullable counterpart without boxing which coincidently strips away Nullable (which makes sense).

So, in the case of a Nullable enum type in a filter I could just box the underlying enum value and compare that to a List<object{underlying value}> .

Or, I could build a more complicated IExpressionPart where I convert the Nullable<enum> member to Nullable<underlying type> but wrap that in a conditional. So something like this pseudo code:

List<int> values = new() {...};
Nullable<int> prop == null ? false : values.Contains((int)prop);

Doubt you need a new IExpressionPart. This works in the utils library:

        [Fact]
        public void EnumInExpression_NullableEnum_WithNullable_IntegerList()
        {
            //act
            var filter = CreateFilter<DataTypes>();
            var constant = (ConstantExpression)((MethodCallExpression)filter.Body).Arguments[0];
            var values = (IList<int?>)constant.Value;

            //assert
            AssertFilterStringIsCorrect(filter, "$it => System.Collections.Generic.List`1[System.Nullable`1[System.Int32]].Contains(Convert($it.NullableSimpleEnumProp))");
            Assert.Equal(new int?[] { 1, 2 }, values);

            Expression<Func<T, bool>> CreateFilter<T>()
                => GetFilter<T>
                (
                    new InOperator
                    (
                        new ConvertOperator 
                        (
                            new MemberSelectorOperator("NullableSimpleEnumProp", new ParameterOperator(parameters, parameterName)), 
                            typeof(int?)
                        ),
                        new CollectionConstantOperator(new List<object> { 1, 2 }, typeof(int?))
                    ),
                    parameters
                );
        }

The cast should probably come from the request. In the worst case the FilterHelper will need an update.

By default I'm casting all enum operations to their underlying type.
So this: SimpleEnumProp in ('First', 'Second') becomes: int in (1, 2) unless the user has specifically requested a cast.

Same with this: SimpleEnumProp eq 'First becomes int eq 1, etc.

If a cast was requested nothing is changed, so this: cast(SimpleEnumProp, Edm.String) eq 'First' is left as is as it will do the right thing.

Actually, the test you should is essentially what I changed in the FilterHelper, except it does determines if the type is Nullable or not.

Just tested this and it works well, thank-you.

I'm a bit confused by the ConvertToStringOperator.

If the user specified cast(EnumProp, Edm.String) I believe the expectation would be to convert the variant name to string and not the variants underlying integer value.

So, if the user was storing their enum's as string's in the DB they'd be unable to perform the following query:

Query
cast(EnumProp, EdmString) eq 'SomeValue'

Expected Expression
$it.EnumProp.ToString() == "SomeValue"

Or am I not understanding something?

I believe the ConvertToStringOperator should be:

public sealed class ConvertToStringOperator : IExpressionPart
{
    private readonly IExpressionPart source;

    public ConvertToStringOperator(IExpressionPart source) =>        
        this.source = source;
    
    public Expression Build() => Build(this.source.Build());

    private Expression Build(Expression expression) => expression.Type switch
    {
        Type type when type.IsNullableType() => ConvertNullable(expression),
        _ => expression.GetObjectToStringCall()
    };

    private static Expression ConvertNullable(Expression expression)
    {
        Expression memberAccess = expression.MakeValueSelectorAccessIfNullable();
        return Expression.Condition
        (
            expression.MakeHasValueSelector(),
            memberAccess.GetObjectToStringCall(),
            Expression.Constant(null, typeof(string))
        );
    }
}

After any changes, the functionality should more closely mirror the OData repo rather than stray from it.

I'm quite sure the SQL Server provider has trouble translating - among other things - MyEnumInstance.ToString() into SQL. Here's an example.

I think, you're correct - we may want to submit a separate PR for changes to the existing code before the new stuff.

Now, in the issue you linked to I think the issue was with using Enum.TryParse which I would expect EF to choke on.
I've used ToString in the past with the SQL and obviously the Cosmos provider and it seemed to work correctly.

Currently in order to support enum's and the $this literal in subexpressions I had to make a few changes to the FilterHelper.
This includes the inclusion of two new operators.

The ToStringConvertOperator which I've shown above and the ConvertEnumToUnderlyingOperator.
I'm using the ConvertEnumToUnderlyingOperator here and here.

The reason behind the location was attempting to convert the enum to its underlying representation got really messy when dealing with cast's. The algorithm currently works on each node of the tree independently, so you don't know what came before or what comes after so you can't make an intelligent decision on whether you should or should not convert the enum to its underlying representation.

Do you want me to make the changes to the FilterHelper in a separate PR? This way if something goes awry it will be easier to determine which PR caused the issue.

OK what I'm going to do is create two new issues (for tracking purposes).

The first issue will be adding support for the $this literal.
The second issue will be about how we handle enum's in a subquery.

@BlaiseD

@BlaiseD So I was thinking of submitting a WIP PR for this feature. This would allow you to get some insight in to what I've been doing over the last couple months and will allow you to ask questions or raise some concerns you may have.

I'm still in progress with this feature but for the most part it's fully functional.

One thing I'm still on the fence about is how to handle queries such as top, skip and orderby is subqueries. Cosmos DB does not support those in sub queries so I'm unsure if I should just leave what's currently there and have Cosmos DB return an error or if I should throw a meaningful exception.

Regardless, I just wanted to get a WIP PR in so that you could see what's happening.

Regarding unsupported features I would look at what ASP.NET Odata does - not sure if there's any special handling.

For WIP - better to submit a PR for the shared/existing classes first then a second one for the remaining Cosmos DB specific code.

@BlaiseD

@BlaiseD So I haven't made any significant changes to any existing code. The changes that were made are just some minor extension methods added to TypeExtensions and I think that's it.

I tried to keep everything as self contained as possible without code duplication.

Navigation Menu

Repository navigation

Feature request: Add support for CosmosDB #166

10 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Feature request: Add support for CosmosDB #166

Description

Activity

wbuck commented on Nov 29, 2022

BlaiseD commented on Nov 29, 2022

wbuck commented on Nov 29, 2022

wbuck commented on Nov 29, 2022

BlaiseD commented on Nov 29, 2022

wbuck commented on Nov 30, 2022

BlaiseD commented on Nov 30, 2022

BlaiseD commented on Nov 30, 2022

wbuck commented on Nov 30, 2022

wbuck commented on Nov 30, 2022

BlaiseD commented on Dec 1, 2022

wbuck commented on Dec 1, 2022

BlaiseD commented on Dec 1, 2022

wbuck commented on Dec 1, 2022

10 remaining items

BlaiseD commented on Jan 7, 2023

wbuck commented on Jan 7, 2023

BlaiseD commented on Jan 7, 2023

BlaiseD commented on Jan 8, 2023

wbuck commented on Jan 8, 2023

wbuck commented on Jan 8, 2023

BlaiseD commented on Jan 9, 2023

wbuck commented on Jan 9, 2023

wbuck commented on Jan 10, 2023

BlaiseD commented on Jan 11, 2023

wbuck commented on Jan 11, 2023

wbuck commented on Jan 13, 2023

wbuck commented on Jan 24, 2023

BlaiseD commented on Jan 25, 2023

wbuck commented on Jan 25, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions