Using Multiple EF Core DbContexts in a Single Application

multiple EF core database contexts in the same application in this video I'm going to teach you how to implement this what you need to be aware of and why you would want to do this in the first place I've got a simple application with two modules inside one is the orders module which contains the order entity it has a list of line items on it and the line item just contains the product ID which is a reference to the product entity in the products module and the price of the product at the time when the line item was created in the products module we only have the product it only has a name and a price so a very simple data model but we're going to focus on using multiple database contexts in the same application so let's see how we're going to do this I'll start by installing any framework core in my API so let's look for The Entity framework or nuget package and I'm going to use the SQL Server package because this is the database that I will be using behind the scenes I'm also going to install the Microsoft 1080 framework core tools package so that I can create my migrations from the package manager console so with these two packages installed let's create our first database context let's start with the orders module so I'm going to create an orders DB context we have to implement the DB context base class from Entity framework core and let's define a Constructor which takes in an instance of the DB context options and I'm going to specify the ordersdb context as the generic argument we'll pass this to the base class Constructor and this is all that we need from the Constructor then I'm going to Define two database set properties one for the order entity which I will call orders and I'm going to use this to create the same table in the database by convention the second DB set that I'm going to create will be for the line items and it's also going to create a table in the database with the same name I'm also going to override the on model creating method so that I can configure my orders entity I'm going to say model builder LED select order and I just want to define a relationship between the orders and the line items so I'm going to say has many and on the order I'm going to select the line items collection and I'm going to say that the line item has one order so it's a one-to-many relationship on the order side and it's a one-to-one relationship on the line item side this part is optional but you can also say that the line item has a foreign key and then you can access the line item object and select the foreign key property so in our case this is going to be the order ID now notice that I'm not going to define a reference from the line item to the product because this will be a separate database context and I'm going to show you how I'm going to separate them at the database level let's now create the database context for our products module so this will be the products BB context it's also going to implement the DB context base class from 80 framework core and I'm going to define a Constructor like I did with the ordersdb context which is going to accept the DB context options and just pass it to the base Constructor then I'm going to add one database set which is going to be my products and I'm also going to override the on model creating method but here I won't be configuring any relationships I just want to see some initial data for my product so that I have something to work with when I run the application how I'm going to tell this to me of core is by saying model builder LED and we select the product and then we say has data now I can pass the products array and when I create a database migration this product will be inserted into the database now we need to register these database contexts with dependency injection I'm going to start by defining a variable for the connection string because I want my database context to connect to the same database so I'm going to say Builder configuration get connection string and the connection string name is database then let's define our first database context with dependency injection so I'm going to say add DB context and let's start with the orders database context the argument for this method is is an action on the database context options and we're going to use it to call the use SQL Server method which I can use to pass the connection string and this will be enough to register my database context the only thing that will be different with the other database context is we just have to specify the products DB context and I can use the same connection string now let me comment on when you might want to use separate database contact in a single application there are three common use cases that I've seen the first use case is when you have different SQL databases for the same application for example one database could be SQL server but the other database could be a postgresql database so you would need to create a different database context to talk to these different databases the Second Use case that I've seen is when you want to use a read replica for one of your databases so this is just a copy of your main database but it's working in a read-only mode in that case you can configure your database context to work in read-only mode by calling the use Query tracking Behavior method I can then pass the no tracking Behavior by default and all of my queries are going to be no tracking when I'm reading from the database and this is something that you want in read-only mode alternatively you can also configure this from the database context by overriding the on configuring method and you have access to the options Builder and again you can say use Query tracking behavior and specify no tracking so this is another alternative but I'm going to leave this out because I don't want to be working in a null tracking Manner and the third use case that I've seen for multiple database context is when you are using a modular monolith when you are building a modular monolith each module will have one database context the separation at the database level could be by schema or even physically separated databases but regardless of that you absolutely have to use different database contexts if you want to build a proper modular monolith if I leave my database context like this because I'm connecting to the same database they're going to live in the same schema now I don't necessarily want to do this and let's Implement a form of logical separation at a database level by saying mono Builder has default schema and I'm going to say orders so now all of the tables in the orders database context are going to live in the orders schema and I'm going to also update the products database context to place the tables in the product schema the next thing I want to do is to create a migration for my database context so I'm going to open up the package manager console and let me try to add a Migration by saying add migration and I'll call it create database so if I run this command you're going to see that we get an error saying that we have more than one database context in the application so we have to specify the context using the context parameter to Target the specific database context that we want to create a migration for so I'm going to say add migration create database and let's target one of the contacts that we have so I'll use ordersdb context and by default the migration is going to live in a migrations folder that this tool is going to create this also means that we're going to mix the migrations between our database context and this isn't what I want so I'm going to define the output folder and let's call it migrations Dash orders so if I run this you're going to see that we get a migration and it's going to live inside of the orders folder in the migrations root folder so here's why what our migration looks like we have the order schema then we have the orders table the Align items table and everything that we need to make this functional let's also specify the same migration for the products database context I'm going to update the first parameter to be my products DB context and the output folder for the migration is also going to be migration slash products so if I create this we're going to get another migration is going to be in the product schema and we only have one table inside which is our products table notice also that this migration is going to seed our initial products so that we have some initial data to work with when we start the application if I want to apply these migrations on the database level I'm going to use the update database command so I'll say update database again I need to specify the specific contacts that I want to use so I'll say update database for the ordersdb context and when I run this it's going to apply the migration let's do the same for the product DB context now I'm doing this because I want to show you one thing that might not be obvious and you're going to see it when I show you the database so let's move over to my SQL Server management Studio the migrations I applied were referencing the webshop database and if I open up the tables inside you're going to see that we get the orders table the line items table and the products table all in their respective schemas if we select what's inside the products table you'll see that we have the five products that I defined as my initial data but you'll notice that the migration history table is not in its own schema it's going to contain the migrations for both of your database contexts and this might not be what you want to get so I'm going to show you how to move the migration history to be separate for every database context if I head over to the call to use SQL Server where we were passing the connection string I can also specify one more argument for the SQL Server database context options Builder so that's a really long name but I can specify an action and on this section I have the migrations history table method this allows me to specify the table name and the schema for the migrations history table and by default the name of this table will live in the history repository default table name and because I'm using the ordersdb context I can specify the schema as orders and this is how you can move your migration's history table into its own schema in this case it's going to match what we already have for the other tables in this database context I'm also going to update the product database context and now I'm going to apply the migrations again and I deleted the tables in the background so these are going to be applied on the fresh database so we're going to run the orders migrations and the products migrations and then we're going to to check our SQL Server instance so if I head over there and I refresh the tables that I have here this time you'll see that our EF migration history table is in the correct schema so for the products context we have one migration history and for the orders context we have another migration history so this is something that you definitely want to have if you're using multiple database contacts in a single database and I also want to show you how to actually use multiple database contacts from your application so let's start by defining some minimal API endpoints so this one will be with the route of products it's going to be asynchronous in the request delegate and I'm going to access the product DB context so let's call this product context and I'm going to use it to return my response so I'm going to say return results dot okay and I'm going to await product DB context access my product I'm going to select the product ID and I'm going to cast this to an array so I'm just going to return the identifiers of all of the products in the database and then I'm going to pass them to another endpoint for creating an order so I'm going to define a contracts folder which is going to contain one class inside and this will be my submit order request and all that I want on this class is just a list of goods which is going to match my product ID and I'm going to call it product IPS let's also assign this to a default empty list so now we're going to use this to create one more endpoint this is going to be a post endpoint with a route of orders and the request delegate is going to be a bit more involved so first of all I need my submit order request to create my order then I'm going to need the products DB context to be able to check if these products exist in the database I'm going to start moving them into separate rows so that you can see everything on the screen then I'm going to need my ordersdb context so this will be the orders context and I can Define the body of my endpoint and then what I'm going to do inside of the this endpoint is first fetch the products so I'm going to say product context products where the product ID is inside of the list that we pass in request so request products IDs contains the product ID I'm going to say has no tracking and let's load all of the products to memory so to list async of course I need to await this because this is an asynchronous method and let's do some simple validation I'm going to say if products count is not equal to the request product IDs count then let's return a bad request so I'm going to save results by request some product is missing I'm not going to deal with which specific product is missing now let's create our order instance so I'm going to say New Order and let's assign the properties so the ID will be a new good the total price of the order I'm going to get by iterating over the products using the sum method and just adding to together the individual prices for each product and then for the light items I'm also going to iterate over the products but I'm going to project them into a new line item instance so I'm going to say new line item and let's set the line item properties so the good is going to be a new good the product ID will be from the product instance then we have the price also from our product and the order ID is going to be set automatically by EF core and I also need to call to list for this to match what the line items property is expecting so let me just format this and we have our order and now I can say orders context and we add the order to the database context I'm going to save this by calling orderscontext save changes async and I'm just going to return this order from this endpoint so let's return the order to the user so that they can see what they created there are two constraints when you are working with multiple database contexts the first constraint is that you can't define an explicit join between the database sets or the tables on these database context this is because any framework core does not know if these tables live in the same database so even if this is true under the hood EF core doesn't know so it's not allowed the second thing is about transactions and they're only going to work if you're actually using the same database but you have to tell EF core to actually use the specific transaction how you can do this is let's say I create a new transaction so using VAR transaction and let's create a new SQL transaction I'm not going to specify the connection string now how you will tell the database context to use it is by saying order's context database use transaction and then you pass it the transaction instance and this is how you can share a transaction between multiple database contexts which means you would also have to do the same for the product context so you would say products context use this transaction and on the orders context use the same transaction and then both of the contacts will run in the same database transaction but I repeat this only works if they are using the same physical database under the hood now I'm going to get rid of this and I'm going to show you how this is actually working so let's start the application so I'm going to use my products get endpoint to get my array of product IDs so these are just the ones that we see that in our database migration and I'm going to pass them to my post endpoint to add them to the order so I'm going to pass these to my orders endpoint and it's going to fetch the products from the products DB context and then use the orders database context to create my order and you can see that we get back an order with a list of line items and all of the properties are matching what we wrote in the code I also have a nice article about this topic on my website and I'm going to leave the link to the article in the description of this video if you got value out of this video then make sure to smash the like And subscribe buttons and until next time stay awesome

Channel: Milan Jovanović

Views: 23,504

Keywords: ef core, ef core tutorial, ef core migrations, ef core code first approach, ef core relationship, ef core vs dapper, ef core 8, ef core 7, ef core code first, ef core 6, ef core dbcontext, ef core sql server, ef core postgresql, ef core multiple dbcontext, ef core multiple dbcontexts, ef core dbcontexts, ef core dbcontext migration, ef core multiple db, ef core readonly db, ef core modular monolith, modular monolith, multiple dbs, read replica, ef core read replica

Id: -_AKTzDrYVc

Length: 17min 45sec (1065 seconds)

Published: Fri Sep 22 2023