New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

NetCore data access strategy: resolving EF blockers #96

Closed
HeyJoel opened this Issue Jun 6, 2017 · 2 comments

Comments

Projects
None yet
1 participant
@HeyJoel
Copy link
Member

HeyJoel commented Jun 6, 2017

In order to move to .NET Core we need to replace our EF 6 data access strategy with a .NET Core compatible solution.

EF Core 2

When looking at EF Core 2 there still seems to be some features missing that we make use of, mainly support for GroupBy queries. Some investigation is required here to see how compatible our use of EF 6 is with EF Core. Even if we do drop EF use within Cofoundry, we'd certainly want an EF integration package to provide a DbContext to developers who prefer to work with EF.

It could be that we simply use stored procs for certain queries to get around missing features in EF Core.

Database Support

Currently we only support MS SqlServer. We haven't had feedback to say that other database support is required, and I don't think we'd ever want to have a broad range of DB options but I could see supporting something like postgres may be popular. However being able to target a single database allows us to optimize for that platform which I feel is an advantage compared to being restricted to the lowest common denominator.

Using an ORM like EF does give us flexibility in the underlying db platform, but our migrations, stored procs and other db objects still tie us to SqlServer. Unless we hear reasons otherwise, I'd like to move forward with a single database platform strategy targeting only MS SqlServer.

Other Providers

Dapper is an obvious candidate that needs researching. I've always thought that we'd move towards stored procs for data access as part of the optimization process so a lighter ORM would be in line with that thinking.

I'm also keen to remove our dependency on AutoMapper for mapping database data to domain models, mainly because it's a popular tool and our internal use can conflict with an implementation in unpredictable ways. So I'd also want to look at how we materialize those domain objects as part of this too.

Some other features to keep in mind:

  • We should play nicely with whatever data access strategy the host application uses
  • We need to support connection resiliency/retries, particularly for Azure Sql
  • Async must be supported
  • We'd need to think about transaction/UoW management so that a batch of commands can be included within the same transaction.
@HeyJoel

This comment has been minimized.

Copy link
Member

HeyJoel commented Jun 20, 2017

I've migrated to use EF Core. Some queries that use GroupBy suffer from in-memory materialization of the grouping, but this isn't show-stopping at typical loads and can be optimized with a bit of work. Our two main EF issues will remain unfixed in EF Core 2:

  • GroupBy translation #2341 - Allows translating LINQ queries with GroupBy() operator that project to aggregate functions to be translated to SQL queries with GROUP BY.
  • Raw SQL queries for non-entity types #1862 - Enables executing queries with ad-hoc mappings to types that are not in the model.

We'll come back and revisit this after we are up and running in .net core and assess our options.

@HeyJoel HeyJoel removed the help wanted label Aug 10, 2017

@HeyJoel

This comment has been minimized.

Copy link
Member

HeyJoel commented Nov 9, 2017

The AutoMapper dependency has now been removed. EF still remains but to get around issues we've restructured some queries to remove the need to use groupings.

Although the question of using EF as a data access strategy long term still remains, I think for now it is working well enough to use for the net core release.

@HeyJoel HeyJoel closed this Nov 9, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment