Support for online/offline sync between local and remote database.

[10Dev]

I realize in advance that is is a difficult task but would like to get the request in early so it can be considered for planning. The feature would be of great benefit to Microsoft's large (and small) enterprise customers.

With the recent purchase of Xamarin by Microsoft I suppose Android and IOS targets might eventually enter the picture but for now I will describe a scenario based on a "pure" Windows 10 UWP based ecosystem.

The Surface tablet in particular has sparked a renewed interest in Microsoft development technology to deploy enterprise wide apps to an increasingly mobile workforce.

The original design of "data" for WIndows 8 had a strangley myopic view of "lightweight" apps using Web API based calls to an Azure based cloud where presumably you have a traditional EF based implementation to SQL Server. This is a "first world" viewpoint based on fast relaible cell communication in major metropolitan areas. Even there you are going to get "dead spots" and other glitches. Additionally, cell connectivity in tablets and laptops is still a rare option. Works for BUILD demos, falls apart in real world. The only devices with reliable connectivity were cell phones and Microsoft is still looking for traction on phones (note optimistic viewpoint)

But with Windows 10 UWP it can all come together nicely. Enterprises can issue continuum based 950 cell phones and/or i7 based Surface tablets to enable mobile apps that can bring very real competitive advantage because for the first time there is an intersection of computing power and development productity along with legacy compatability that exists in no other ecosystem.

This scenario all revoles around taking advantage of local DB storage that enables complex apps for a mobile workforce spanning a range from engineering modelling, bridge and road inspection, sales and ordering etc where the employee with a sophisticated Surface tablet has no internet connectivity for large periods of time.

And then the tablet detects interent all the updates in the tablet DB need to be 2 way synced with the cloud DB. I am sure you are aware that this becomes complex.

But the original EF Code First Migragrations (Magic Unicorn Version1) made something very complex become very amazing simple.

And this partially connected scenario of the local EF UWP Database (which is also a rather abstract non-extistent magical creature at this point) seems approachable as something that fits within an abstract view of the code generation already part of EF.

A person could of course rain on this parade by saying this is nothing but a sync framework being proposed, but by integrating this into the EF Code First, it enables developer productivity to manage an important upcoming opportunity of "UWP Everywhere" by addressing the issue at the design level/surface/point where it belongs.

[rowanmiller]

This does have the potential to get overly complex if we tried to make it a completely automatic sync framework... but I think there is probably a sweet spot where we provide patterns/guidance and a set of useful APIs that make it very easy to setup online/offline sync. A lot of the complexity in a full blown sync framework comes from having to deal with every possible case... but if you know how your data "behaves" then it quickly becomes a lot simpler. This is somewhat similar to our attempt to solve the N-Tier change tracking problem with self-tracking entities (did not go well :scream:)... but with our new TrackGraph API we are making progress towards having the right building blocks to easily solve the problem (though we are not quite there yet).

[VagueGit]

This would be a major benefit to us as ISVs. We provide software to small businesses. They want to use their software on multiple devices, but they don’t understand networking. They also don’t understand backup.

Sync local SQLite database to Azure SQL Database would solve both of these issues and get millions of small businesses onto Azure.

[VagueGit]

There are a lot of apps using SQLite offline that need sync to a cloud db. Where is this feature in the EF Core roadmap?

[ErikEJ]

Out of scope for EF Core, IMHO

[VagueGit]

There was a time when I would have agreed @ErikEJ but the world is evolving in unexpected ways. EF was developed as an Object Relational Mapper ... to mitigate the mismatch between object oriented code and relational databases. Yet EF Core is to support Cosmos DB ... a non-relational database. So the purpose of EF Core has changed, from ORM to a tool to facilitate data access.

To quote @rowanmiller

we are making progress towards having the right building blocks to easily solve the problem

That was Feb 2016. There is huge demand for data driven partially disconnected apps, so if EF Core can facilitate that, why not? Who is better placed?

[ErikEJ]

Microsoft already provides a Sync framework for SQLite to Azure: https://docs.microsoft.com/en-us/azure/app-service-mobile/app-service-mobile-offline-data-sync - it would be more useful to ask for CosmosDB support there, as CosmosDB now provides change delta info

[VagueGit]

We looked at Azure mobile offline sync @ErikEJ. It is aimed at what we used to call applets and is incompatible with EF. We need something that can recurse an object graph, not just sync the odd table.

[ajcvickers]

@VagueGit It's something we have thought about in the past, but honestly there are many other things that are higher priority than this at the moment. As you said, things can change, but right now it's not something I expect the EF team to work on anytime soon.

[VagueGit]

I don't doubt that @ajcvickers but the EF team has seemed to be very web focused in recent years. Has the team spoken with @migueldeicaza or Kevin Gallo about working with offline data? The ability to work with offline data and sync back to the cloud is really important for native application developers.

[weitzhandler]

Isn't it time for official adoption of a client-side package that will make it easier to maintain client entities against a server using an EF model? Such as TrackableEntities.

[bricelam]

FYI, I stumbled across Mimetis/Dotmim.Sync the other day if anyone here is interested in taking a closer look at it. (This is not an endorsement.)

[weitzhandler]

@bricelam, looks like that repo is interacting directly with the DB. I'm looking for interaction of client with an ASP.NET Core Web API server that uses EF. The only projects I found that does what I'm looking for are:

• Trackable Entities by @tonysneed
• Breeze# - which looks abandoned

But even tho, as a subject that is so demanding for ANY client app (WPF, UWP, Xamarin), that has to retrieve and save a graph(s) of entities. That's why it's so important Microsoft should take control and have one of their own or embrace one of the existing ones, but this matter requires official support. Silverlight used to have RIA services, which although didn't deal with graphs, at least offered good tooling and consensus for server-client entity sharing.

A LoB dev has to spend so many hours writing model code twice for client and server, or for writing the load and save of related entities both in the client and server. And once the graph gets really complex the job gets really messy involving a lot of dirty code that could be saved if there were a central tool that takes care of it. It's really hard for a LoB dev to compete with his fellow programmers that use MS Access, because although they don't have the code productivity and code efficiency of .NET, developing time is 10 times faster!

So what I'm asking or merely begging, is for MS to consider adopting more tools and tooling for server-client exchanging of entity graphs. Please bring back entity modeling, form generation, client-side entity generation and announce official support for client online or offline graph maintenance etc.

[bricelam]

@weitzhandler I think you're looking for something more like issue #5536. This one is about having an offline copy of the database that re-syncs when the app comes back online.

[weitzhandler]

That issue comment is a bit unclear, but I think it is indeed what I'm referring to. In what is it different from the current issue? I actually forgot that I already read it before, it was a while ago. Every here and there when I'm starting a new project I'm bumping into this issue. Last time I ended up using Trackable-Entities.

[HappyNomad]

I'm working on a synchronization framework that can be used with EF Core. Unlike the libraries mentioned above, mine uses deltas. Changes are sent among client and server in the JSON Patch format - actually, a derivative of it.

Although as a feature it's out of EF Core's scope, this issue should remain open as a goal. We can continue discussing how to facilitate the occasionally-connected mobile app scenario. For one benefit, this issue provides context as to why I want https://github.com/aspnet/Home/issues/2743 fixed.

Since this is my current focus, I'd love to discuss my approach to synchronization, and the overall occasionally-connected mobile app scenario, with whoever's interested. Reply here or email me to continue the conversation.

[ajcvickers]

After discussing this as a team we are closing this issue since we don't think that EF Core is the correct place for a full offline/sync implementation. However, we still believe that EF Core could be used in such an implementation. For example, #5536 could be used as part of the implementation. Therefore, if the community is using EF to implement something like this, then we would consider taking pull requests to EF Core to help such an effort.

[HappyNomad]

Sounds in line with my thinking, except for #5536 part. IMHO EF Core's success and longevity depends on keeping it focused - small but of high quality for its well-defined, specialized purpose.

Therefore, if the community is using EF to implement something like this, then we would consider taking pull requests to EF Core to help such an effort.

How about start with https://github.com/aspnet/Home/issues/2743? It's a simple fix, so it'd be an easy first step in this direction.

[Mimetis]

@weitzhandler That's not true, you can use Dotmim.Sync behind an Asp.Net Core application (obviously :) ).
See the doc here : https://mimetis.github.io/Dotmim.Sync/Web
I'm the author of this framework, do not hesitate to reach me if you have any question !

Seb

[weitzhandler]

@Mimetis I'm actually looking for a client-side library that interacts and syncs with the server-side EF-powered relational-model.

[charlesroddie]

Since most apps need this, I would have expected solutions to be available. It's disappointing that Microsoft is no longer actively developing its sync technologies: Sync Framework and Azure Mobile Apps.

I am considering EF and it appears that using EF core code-first (rather than managing database tables directly) will actually make sync harder, since we will want to use all the tables as they are in the database (with ids instead of objects) in order to sync. Please correct me if I'm wrong here.

[VagueGit]

@charlesroddie it does seem Microsoft has failed to read the market and recognise the importance of offline data. We used to develop against SQL Server and SQL Azure, but market demand for offline sync forced us to move to Realm Cloud.