If anyone is around I need help with a couple things relating to DAL design.
Specifically:
1. I don't understand the concept of the DbContext very well and therefore don't know if the derived classes I have designed are correct.
I get that it encapsulates a connection to the Db but is it proper to have multiple contexts for a single Db or not? I have two derived contexts, one for regular users and one for admin, under the idea that these are separate usages and should be kept separate, but am not sure if this is correct.
2. In the case of multiple derived DbContexts is it worth trying to abstract them behind the same interface?
If this is not clear lmk and will clarify whatever.
@theVBE-it'srightforme it encapsulates a transaction - so yeah one connection, but you also want to call SaveChanges once. Typically you'd inherit a class from EF's dbcontext base class, and expose a generic IDbSet for each entity/table; then you override onmodelcreating and that's where you need to pick a scenario: db-first, or code-first: it's where all the table and key mappings and entity relationships happen.
So basically your dbcontext is abstracting your whole database
If an app needs to connect to two databases, it might need two dbcontext implementations
For the rest of your code, dbcontext just doesn't exist. It's an implementation detail of whatever service/interface the rest of the project uses to access/manipulate the data
@MathieuGuindon So there is no point to have a separate derived context for a regular user and for an admin to represent the separate capacities in which they would need to access the database?
I have the context wrapped in a unit of work class to allow for implementing generic repository pattern which I named DbTransaction which is why I used connection to describe it. I did basically understand it's role in the way you describe it (abstracting the db) though of course in not as clear words!
I suppose I do not know if the generic repository part is itself correct either.
I went with db first because as I understood it that was the norm for enterprise environments. I used a bunch of Fluent API calls in that method override to essentially mirror how I designed the db to make sure there were no issues.
I love how the MS documentation says OnModelCreating is called the first time the derived context is instanced rather than the first time the derived context is told to actually connect to the data source it is configured to connect to.
I can share code if it makes things clearer/you are interested of course.
@theVBE-it'srightforme This SO answer sums it up pretty neatly for me - multiple DbContexts can sometimes be helpful, but you need to have a very definite case for them, and there's a complexity cost to going that route.
I would think very carefully before putting EF behind another abstraction. EF is arguably already the ultimate generic repository pattern - not only does it abstract database operations, it also has a highly engineered API (IQeuryable) for interacting with it.
The only argument I can think in favour of another abstraction is if you wanted to be able to swap out EF for another ORM. In many years, I've never come close to needing to do that.
(The above is based upon modern EF, with simple POCOs acting as the model, and fluent configuration. Earlier versions like edmx had different concerns like the need to be able to test them properly)
@mansellan I have actually read that answer (and did not really understand it :/). What would be a "very definite case" for that? I don't get what is meant by multiple schema in one db; I thought schema described how a db is designed.
I put it behind another layer because as I understood it the core business logic layer was not supposed to (directly) know about any specifics of the DB access implementation and because I thought it would keep the code cleaner to allow those classes to just call simple "neutral" CRUD functions and have the repository layer handle the overhead needed to persist correctly in a disconnected scenario.
(i.e. marking the entity states)
Even if there is no intention to swap ORM it still seemed like adhering to separation of concerns.
@MathieuGuindon ^ is consistent with what you say here right?
@theVBE-it'srightforme Some types of database let you split a single database into multiple "schemas" - this helps DBAs manage different policies for storage, security, etc. For example, the default schema in MS SQL is dbo, so you may see a Products table referenced as dbo.Products.
By "very definite case", I meant (for example) a huge database where it might be inefficient to model the whole thing in one DbContext. Or a multi-tenancy arrangement where you need to be 100% sure that data is segregated. Personally, I wouldn't use multiple DbContexts just to model different access roles, it would need to be more than that.
@theVBE-it'srightforme With regard to business code - you're right to say that business logic should not know about database specifics. So, for example, you should never put SQL code directly in business code. But EF already abstracts database specifics, and exposes a general API to callers (IQueryable). You can still, for example, swap out MS SQL for Oracle without impacting business code.
Opinion varies as to whether you need another abstraction, I'm sure some would disagree with me. I just don't see the value of abstracting an abstraction...
A repository would make perfect sense if it were interacting with the database directly (or using a minimal abstraction like Dapper). But a repository wrapping EF is of limited value imo.
Part of it is at least for me is that it feels like EF tries to do so much for someone that there is nothing for them to really do at all and that is boring.
It feels like it tries to simplify the bridging of the .NET app and the database as much as possible for people who are not familiar with the database side.
It did not feel like I really had to learn the db properly or that I was encouraged to when I read the guides about and that felt cheap to me.
I can imagine for some more experienced programmers using all the features of it might feel like giving up too much control.
This is just my impression as someone learning it.
If you want to get more hands-on with the database you could take a look at Dapper. You have to write the SQL rather than have it written for you, but it takes care of mapping the results into objects.
And as I say, that would be a better use-case for a repository imo
@mansellan Oh no I need as much help with accessing the database as possible. I was just thinking of people who might want a greater degree of control but still might use EF instead of Dapper or something else for whatever reason.
I do not get the sense that EF is bad but it might be overly helpful for some people so they use it but "disable" the features they don't like so to speak.
@mansellan This was super helpful in clarifying btw, so thank you for that. Need to read about multi-tenancy more I think but I should be able to figure this out fairly easily now. It never ceases to amaze me how so many things in software development are not necessarily obvious. Much more art to it than I think is sometimes in the popular conception.
Recognizing this makes me appreciate it more (once I get over the frustration of not knowing what to do).
As an abstraction representing a db transaction, DbContext is already a UnitOfWork, and each IDbSet<TEntity> in it is essentially a repository: there doesn't need to be an extra layer between it and your data access code, so long as there's an interface between your business logic and the data access code.
Unless the data access is very complicated, typically a couple of specialized classes that take some IDbContextFactory dependency, and then the methods can do using (var context = _factory.Create()) and pull & return entities (or take & persist/update/delete them)
..works fine, and then tests can easily stub that service class
@MathieuGuindon ikr? no more worrying about sending rubbish sql to the db
the only solid place I can think of full ORM is still useful is where you need to have automatic conversion from expression trees to sql. Specifically, I'm thinking about oData, letting your api callers issue arbitrary queries. oData is getting attention from MS again after a hiatus.
@this Well, it's up to you how much of the database you expose. Ultimately, the abstract relationship between sets is going to be common between db and odata, but exact implementation details (pk/fk relationships etc) need not be exposed if you don't want.
It's not all the time that you need to allow arbitrary queries, but when you do have a need to do so you need oData or something similar.
The thing is that I'm not sure you can say that abstract relationship is the same between odata (e.g. what the consumers sees and cares about) and the database layer. For example, if you have an entity that had large amount of properties, you might implement a 1:1 table, a columnstore index, or some other details that shouldn't be leaking through to OData or equivalent layer.
Consumers shouldn't have to do join Orders, OrderOptionalDetails, OrderHeader to get all the data about an order. They only want an Order
it's just a mapping exercise though, should be a one-time cost absent radical changes
a use-case. suppose you have a web app with a data grid. requirements are that users should be able to configure it six ways from Sunday - which columns, in which order, with which filters, in which row order, with an arbitrary number of rows per page. Basically no hope of handling all that with pre-canned service operations...
I guess with a web client the web server could hit up the database directly. Not so easy with a rich client though.
hmm views I can see, but with a sproc it's not easy to allow extreme flexibility like that because you have fixed parameters
I guess you could have a TVP for columns, another for sort orders, another for filters... but at some point you're just reinventing SQL (or at least DML) no?
I suppose to some extent it depends how tightly locked down you need the DB to be. I guess if you're exposing services to the general interwebs or you're in a tightly regulated sector the needs might be different. But atm my work is B2B, so can be a bit more flexible.
Dapper is much more low-level (and performant); it's a set of extension methods that extend IDbConnection so var users = connection.Query<User>("select * from users") gets you your User entities. With the abstraction I suggested earlier you could swap the ORM for it (and basically rewrite the data access), because how it's queried is an implementation detail - with an Repository<T> you'd still have to rewrite the data access =)
VBA Rubberducking
VBA Rubberducking