Cosmos: FromSql support

[AndriySvyryd]

No description provided.

[mattrozema]

Is there a roadmap for implementing this feature? Without the ability to create custom SQL, and without for the built-in Cosmos functions (#16143), I don't believe there is any way to use EF Core if you require any built-in Cosmos DB function.

[AndriySvyryd]

These issues are currently in Backlog meaning that we don't have plans on working on them any time soon. Please vote 👍 on the issues important to you to help us prioritize.

[thomaslevesque]

This feature is absolutely necessary. There are far too many Linq queries that can't be translated to Cosmos SQL, and there's no workaround.
I just spent a few hours trying (for the third time) to use the Cosmos provider, and I hit a brick wall again. In its current state, the Cosmos provider is unusable for anything non trivial. Is anyone actually using it in production?

[JonPSmith]

Hi @mattrozema and @thomaslevesque,

I agree I would love these features but I have recently build an example app using Cosmos DB for a chapter in my book Entity Framework Core in Action, 2nd edition and I build code to do direct read/writes to the Cosmos DB Container. It takes a bit more work, but it can be done.

Have a look at the BookApp.ServiceLayer.CosmosDirect.Books project and you can see some code in the Services folder that does direct reads. Also this unit test has some direct read/writes too. The GitHub repo is open-source so you can use the code in your app. Be aware the code you need is in branch Part3.

PS. Not trying to sell my book - you can get what you want from the links to the code. But just so you know the chapter (16) is finished but it won't be out until (late?) January. That's because each chapter goes through at least 2 review cycles before it in the early release eBook, and about 4 more reviews before the print version. (Crazy hard work!)

[thomaslevesque]

@JonPSmith thanks for the link. Sure, you can use the Cosmos DB client directly, but then you're no longer using EF Core, you're just working around it... Entities are serialized/deserialized based on the Cosmos client settings, not based on the EF Core configuration.

[JonPSmith]

Hi @thomaslevesque,

Sorry, I pointed you at the direct accesses because that has the code you might need, but the actual app creates the Cosmos DB database using context.Database.EnsureCreatedAsync and writes to the database uses the normal EF Core commands like SaveChangesAsync. That means I could use EF Core for most of the features but revert to direct Cosmos commands where EF Core didn't work - see BookApp.ServiceLayer.CosmosEf.Books.

I then build the BookApp.ServiceLayer.CosmosDirect.Books project that I pointed to so that I could compare performance/features between EF Core's Cosmos DB access with direct access to Cosmos DB.

[JonPSmith]

I recommend this gets into the EF Core 6 list. If this is available many EF Core limitations can be worked around.

NOTE: At the moment its quite hard to obtain an Cosmos Container instance as you need to know the database name, which isn't available via the DbContext linked to the Cosmos DB.

[bekirakinci]

We're still waiting for FromSqlRaw support, when are you planing to release it?

[ajcvickers]

Triage: balancing to @roji to take a stab for 6.0.