ORM Selection

[DotNetDublin]

One thing we know is that the application will utilise a SQL database for storing data.

In Blazor and other dot net applications it is common practice to interact with the database using an ORM.

To my mind we need to choose between Entity Framework Core and Dapper.

Personally I've been using Dapper with Dapper.Contrib in my projects and have been very happy with it.

I'm also aware that @adamfoneil has extensive experience with Dapper and is a big fan.

That said I'm aware this project is a learning resource for the community and it could be argued that developers will more likely face Entity Framework in the real world so I'm happy to consider both options.

[adamfoneil]

I'm pretty open-minded here on this question :-) at least on the CRUD/Repository side. I'd be happy to see an elegant, concise EF implementation. On the general-purpose query side, I'm a little more committed to Dapper.QX because it gets around LINQ translation issues, while providing a good testing approach.

[DotNetDublin]

Thanks @adamfoneil.

My only concern around utilising Dapper.QX is in relation to this being a learning project and the likelihood of developers who are new to Blazor having experience with Dapper.QX or new developers starting out who become familiar with Dapper.QX and are then unable to utilise it in the real world as their company doesn't use it.

I was thinking the more we keep familiar the better in relation to developers who are new to Blazor but familiar with MVC or Web Forms.

As I read that back I think am probably guilty of overthinking this and vastly overestimating the impact this project is going to have on the world :)

[adamfoneil]

I agree Dapper.QX would require some buy-in, and is hardly "mainstream." I should probably branch and PR a suggestion, so you can see what I'm really suggesting! :-)

[jonhilt]

Good points raised here.

I don't have a strong opinion on this one. What I would hope to aim for is an architecture that means it's no big deal to choose the best tool for the job, even for a specific feature.

I think (but please do correct me if I'm wrong) this is similar to where @adamfoneil is leaning as well, to be able to employ something like Dapper for queries, and it not be a big deal to use it in some places, but EF in others.

Interestingly, performance-wise the gap between the two is shrinking fast (especially with .NET 6) but I know there are considerations other than performance.

[DotNetDublin]

Nick Craver, the chap responsible for Dapper is also the lead architect at Stack Overflow and he tweeted that at Stack Overflow they use both Entity Framework and Dapper. As you say, choosing the best tool for each scenario.

His tweet was in response to a tweet about the performance between Dapper and Entity Framework closing.

For me the problem with Entity Framework was never performance but more around ease of use and getting into trouble with migrations but it's been a minute since I last used it so things may have improved and no doubt most of the the problems I encountered were down to my own lack of experience with it.

[jonhilt]

Just to bring in a bit more specific context (for background), I worked on a .NET Core project recently for monitoring people's blood pressure.

We took a Domain Driven Design approach and EF worked nicely for the transactional side of things (inserting, updated, deleting data primarily).

For any given operation we would retrieve an 'Aggregate' from the database (essentially an object hydrated from one or more tables in the database), call methods on that aggregate to apply business rules and run the relevant business logic, then save changes (whereby EF would figure out what had changed and make sure those changes were persisted).

For this, EF worked well.

On the query side, we did sometimes use EF, but Dapper often proved to be a better choice so we didn't shy away from using it on those occasions.

The key was, when we ran into a slow query, or one where there were simply too many hoops to jump through trying to implement a query using EF, we could easily use Dapper, often just for that one query.

[DotNetDublin]

Thanks for sharing your recent experience and it looks like we are starting to lean towards Entity Framework for normal CRUD operations and Dapper for specific scenarios.

That could end up being the best of both worlds as it will give people exposure to both.

It's funny how sometimes you start to think about technologies like sports teams in that if you like using one you cannot use the other or it will be seen as an act of betrayal :)

[AnkurSheel]

Why not just Dapper all the way? Isn't, the SQL for the basic transactional CRUD operations simple enough that we don't gain much by using both EF and Dapper. Although, I agree that partial Updates can be a little painful in Dapper.

Having 2 different systems for the same thing can increase the cognitive load for deciding which one to use.
Having said that, not too fussed about which way we go.

[DotNetDublin]

Hi Ankur

That's a good question and we may actually end up with a 100% Dapper solution as there certainly isn't anything lacking in Dapper that is making us think that it wouldn't be as good as Entity Framework for the CRUD operations.

What I'm guilty of is thinking of this project as possibly a great reference for developers new to Blazor or indeed for new developers in general and I think there is a good chance that when they get their first development job in the real world they could encounter projects that utilise entity framework so if this project can give them at least a taste of how it works all the better.

On a personal note I'm using 100% Dapper in my Blazor projects so I wouldn't mind seeing how nice or otherwise Entity Framework is to use these days as it's been a minute since I used it.

Another option is to have the main project use Dapper and have a branch that uses entity framework so people cloning the project could use what they prefer.

[AnkurSheel]

Another option is to have the main project use Dapper and have a branch that uses entity framework so people cloning the project could use what they prefer.

I don't think we should do this at the start of the project. Once we have the MVP built then sure.

If we want to use EF, do we foresee any situations where we think EF would fall over? If we have to, how hard is it to migrate away from EF to Dapper or just add it in at a later point of time?

[DotNetDublin]

If we want to use EF, do we foresee any situations where we think EF would fall over?

I don't believe so however @adamfoneil is the community expert on all things Dapper and even has some libraries we could consider using. He has some good views on why you should consider using Dapper over Entity Framework.

https://github.com/adamfoneil/Dapper.CX

https://github.com/adamfoneil/Dapper.QX

If we have to, how hard is it to migrate away from EF to Dapper or just add it in at a later point of time?

I don't see it being too difficult and how we architect the application would impact how efficiently we could switch from one to the other if required.

[jonhilt]

Most of the projects I've worked on in the last decade or so have been architected such that it's very easy to use the best tool for the job, right down to an individual feature. Weirdly (and seemingly paradoxically) some of the tools we used to do this in the past (like putting all the data access code in its own layer) can actually make it much harder to switch ORM, because changing the ORM becomes a big job which affects multiple features (often in difficult to predict ways). I'm confident we can architect TimeKeeper in such a way that ORM selection isn't a big deal, but as a general direction it looks like we're heading towards EF (especially for commands, where data is being changed/added) and potentially Dapper for (at least some) queries. I've been thinking about something else too. As it looks like we're heading toward a Blazor app which can run as both WASM and Server, the backend for TimeKeeper is essentially going to end up being an API (so that the Blazor WASM frontend can interact with it via HTTP). This has the advantage of keeping the Blazor UI relatively simple (because important business rules/logic would be in the API, not the frontend). A few projects show some interesting ways to stand up an API like this. Humanitarian Toolbox have a couple of apps, using different technologies. Their first project, AllReady uses an architecture I'm familiar with, where each feature is represented as a MediatR Handler. All Ready API https://github.com/HTBox/allReady/tree/master/AllReadyApp/Web-App/AllReady/Features Here's an example of a feature to retrieve a list of 'authorized campaigns' (so, a query). AuthorizedCampaignsQueryHandler https://github.com/HTBox/allReady/blob/master/AllReadyApp/Web-App/AllReady/Features/Campaigns/AuthorizedCampaignsQueryHandler.cs And here's a Command (to 'Unenroll a Volunteer'). VolunteerTaskUnenrollCommandHandler https://github.com/HTBox/allReady/blob/master/AllReadyApp/Web-App/AllReady/Features/Tasks/VolunteerTaskUnenrollCommandHandler.cs By contrast, their latest app is called TwoWeeksReady and looks like it uses Azure Functions for its API. I haven't really explored AZ Functions so it's interesting to see a real-world example. Two Weeks Ready - API https://github.com/HTBox/TwoWeeksReady/tree/master/api/TwoWeeksReady

[harperjohn]

Tim Corey has a nice video on the topic. https://www.youtube.com/watch?v=qkJ9keBmQWo

Points of concern:

• The "magic" and "automation" of things like EF can double back and bite you if you don't fully understand the concepts and the EF tool itself.
• For "enterprise" development, EF is probably a given, but for small apps EF might add unneeded complexity... and (the big one) complicated/expensive hosting requirements.

I share @AnkurSheel thought to use it when you absolutely need it. I agree with @jonhilt that we should do our best to make an insulating layer to make switching easier.

And... for this feature (and other topics) I really like the idea of using branches! While it might be nice for example/demonstration purposes to mix EF/Dapper/etc in the same branch, it would be very interesting to see EF in one branch and Dapper in another. We could see/experience the ease/difficulty/ simplicity/complexity in "isolation, and we could even do timing tests and use other "metrics" for comparison.

PLUS - @DotNetDublin I agree that branches could help us create a suite of templates, instead of a monolithic template. I find it easier to pick/choose/combine tight/clear templates than to prune/re-factor large ones. Large monoliths, despite good intentions, will always develop feature bleed/blur.

[AnkurSheel]

For "enterprise" development, EF is probably a given, but for small apps EF might add unneeded complexity... and (the big one) complicated/expensive hosting requirements.

@harperjohn My experience has been the opposite. From what I have seen most SASS companies targeting the enterprise space prefer to use Dapper rather than EF. @jonhilt already mentioned one of the main reasons - It's much harder to switch ORM's.
Also, if you have a team where people have some experience with SQL, I am not sure what EF gives.

Then again I have never worked with an ORM so I have a pretty one-sided view.

No matter what we go with, we should have a clear separation between the Application layer and the DB layer and the respective models. But that's a discussion for elsewhere.

[harperjohn]

@AnkurSheel Interesting! Glad to see the "enterprise" space being open!! Really glad to hear that!

[AnkurSheel]

This has the advantage of keeping the Blazor UI relatively simple (because important business rules/logic would be in the API, not the frontend).

I think this is a good strategy to follow and separate the UI and the API concerns. Whether we deploy the API separately or not 🤷

I've been thinking about something else too. As it looks like we're heading toward a Blazor app that can run as both WASM and Server, the backend for TimeKeeper is essentially going to end up being an API (so that the Blazor WASM frontend can interact with it via HTTP).

Probably the discussion about the API deserves its own issue. I am keen to hear what the opinions are on REST vs Graphql? I have been using the latter and it's quite nice but it adds a layer of complexity if you haven't used it before

[m-maste]

Since @DotNetDublin is imagining this project being a nice reference for new developers, I might be the target market here. I hopped on the dotnet train about 3 years ago after a failed relationship with Ruby/Rails. Here's my limited experience with EF and Dapper.

I've mostly used EF because it seems to "just work" in the dotnet world.

I like that it tracks changes.

For me, for most CRUD situations, EF and LINQ is more enjoyable to write than SQL.

Which brings me to Dapper. There were two projects I worked on that used EF and added Dapper later for some raw SQL queries.

One was a project that needed to query a SQL database that was part of a legacy app and had no API. I immediately reached for Dapper because it was easy to connect to this database, write SQL queries, and get the results I wanted.

Another time I needed to flatten or de-normalize some objects. I was having a hard time getting this done with LINQ's HasMany and I already had written a SQL query that gave the results I wanted, so I just used Dapper again.

Later, on this project, I learned about EF's FromSqlRaw extension method and switched to using that instead of Dapper. With EF it wasn't obvious to me that I needed to add a new a DbSet of my results object and then tell EF to ignore that DbSet for migrations...so not as straight forward as Dapper, but I got there and now I feel comfortable with raw SQL queries in EF.

As a newish developer who is comfortable with SQL, I would feel fine using EF or Dapper.

[DotNetDublin]

Thanks for the insight. That's really interesting.