>> Come learn how to take your existing Azure SQL database
using Entity Framework Core and EF Core power tools.
Learn all about it this week on Data Exposed MVP Edition.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP Edition.
Today I'm joined with Microsoft Most Valuable professional, Eric.
Eric, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Yeah, I work as a consultant in the IT industry
and I help customers develop solutions in Azure Cloud.
Often involving some kind of
data storage like Azure SQL Database,
occasionally Table Storage, occasionally Cosmos DB.
I'm been a Microsoft MVP for number of
years and one of the reasons I picked that I suppose,
is because I provided some free tools and libraries that can
helps bridge the gap between.NET developers and data platform.
>> Awesome. Cool, well,
it's great to have you on the show.
I am familiar with some of the tools that you have out on GitHub,
so we'll make sure to share those in the description.
But today we're going to be talking about how to get started with
Azure SQL Database using
EF Core and we're going to do it in five minutes.
>> I think that was my optimistic approach,
but let's see how far we can get in five minutes.
I will tell you when I actually start the demo,
I would just do a little bit of introduction,
starting with explaining what does
EF in EF Core actually stand for.
It means entity framework and it's a.NET library
for data access is Microsoft's recommended debris
in.NET for data access in particular against
the relational sources like Azure SQL Database,
SQL Server, Postgres, MySQL.
A lot of other database providers available for the product,
but those are the main ones.
>> Okay, cool. Now, that's definitely good to know,
just make sure everyone's aware what we're even talking about.
>> Absolutely. Yes,
and this little demo
I will show will demonstrate one of the tools that I have created,
and there's a visual studio extension called EF Core Power Tools,
which will allow you to point to an existing database which
can be hosted on Azure SQL Database or on SQL Server,
on premises or in a VM.
Then generate a code so you can start
interacting with that database from other.NET application.
By doing that, it will help a lot.
It will get you started quickly and you
can concentrate on improving
your API documentation and on
your business logic rather than
the plumbing of accessing the database.
>> Awesome, great. Well let's take a look
and let's see if we can do the five minutes right now.
>> Yes the five minute have gone already yes and
let's just have a quick look at what we can do.
I would just start with presenting my screen here.
I have a blog post about what is new in the tool and it
has actually just nearly reached 250,000 installations.
That's quite a milestone and it's a free Visual Studio extension.
It's available in Visual Studio Marketplace and it's
completely free and it runs with any addition of Visual Studio,
including the free community edition.
It's open source and available on GitHub and
I get a lot of feedback reports and so on,
on GitHub and welcoming anybody using the tool,
to interact with me via GitHub.
What we're going to look at today is
basically what is in
this article here that I'm showing reverse engineering quickstart,
which will show very quickly how to get
started using the tool by pointing to an existing database.
I will switch to a Visual Studio.
I have created a new solution here
and I have a project,.NET 6 project.
There's nothing in it at the moment except just stop program CS.
Now I will connect this application is just a simple console app,
but it could be like my data
access library in a Web API or similar.
I will now connect that to to MySQL database and generate
an EF Core code base that I can
interact with in this app for my extra SQL database.
I will just right click my project
here and I have the tool installed already.
It's called EF Core Power Tools and I
will go to Reverse Engineer menu.
Then I will connect to my database.
I have already added a connection to an Azure SQL database.
But let's just look at the mechanics of doing this.
So I go in here,
I pick Azure SQL Database,
I call it something,
and I have a sample here that will give you
some ideas how you should compose your connection string.
The reason why I'm doing this and
not just pointing to it from this studio
is due to the fact that [inaudible]
if you're using a username and password,
and I really like my connections to
be password list and based on Active Directory.
This is the format of the connection string you will be
using in order to connect to a database.
But as I mentioned, I have already created a connection here to
my life database that I have established in Azure portal.
I will click "Okay" here.
The tool will now go out and
look in the database for all the tables,
views, stored procedures,
and functions that are in the table in the database.
I will just choose to just include everything
here so I can point to
my tables and get them generated as C# classes.
Similar the views,
I can also map store procedures
and SQL Server stored procedures and functions
with the tool and use those via my EF Core code.
I will click "Okay" chosen everything.
We have some options here,
I will just leave most of them are standard.
I have an extensive user guide on GitHub
that you can consult for more information about all these options.
I will choose to include the connection string in this case,
so we can do a quick demo.
I can also pluralize my object names,
meaning that if I have a table called customer,
it will become customers and so on.
As you can see, we can also install
the appropriate provider package in the project.
So I just need to click "Okay" and
immediately I can start coding against the Database.
Was that less than five minutes, Anna?
>> Yeah, it was. I think from start
to finish that was about four minutes,
maybe even less so pretty awesome.
>> Cool. But let's see if
the demo gods are with us today and click "Okay" here.
Now managed to click okay,
and you can see that a lot
of files have been added to the project,
which is code generated for all the objects in the database,
and we can start program CS
and start using the code that was just generated.
The code is now exposed via something called a
DbContext and it's named
after the name of the database that we used by default.
I can write queries now in C#,
losing a feature, C# feature called
Language Integrated Query and access the database like that.
I am not store any credentials on my machine because
I was using a Active Directory authentication.
>> Awesome, cool, this is really great to see.
One question that I have is what happens as you go
to make changes to these tables or add new tables?
How do you update that here?
>> That's a really good question, Anna.
One approach is that the older code is
generated in what is called partial classes,
so you can actually extend these classes.
If you need to modify these classes,
like say add a property it's with
the order details for a display name or something like that.
You can do that in a separate file
that's separate from this code generated file.
Another approach that I'm a big fan of is
that you can actually use a database project
to keep your schema on
the source control and evolve that using the database project,
which is basically a collection of
create scripts that you keep under source control.
The tool has the ability to point to a database project.
I will just show it here,
and we can actually create a context and
maintain the context based on what is in the database project.
I think that's a really good approach to managing the lifetime of
your database project because then you can deploy
your code and your database project,
which essentially becomes a DACPAC file,
at the same time when you're updating your app with new features.
>> Awesome, cool, that's
a really great combination of two awesome technologies.
One final question I have is let's say you're
managing this for multiple schemas or customers,
do you have any advice for
how you might go about using this to do that?
>> If you're manning it from multiple schemas,
the tool already support that.
Actually, it has a feature where you can generate
the classes into separate folders named after the schemas.
If you're overlapping,
if you have a school.customer or an order.costumer,
whatever, multiple schemas with the same table name,
the tool can separate them by namespace by doing that for you.
But you're also talking about how you are managing it in
terms of addressing various customer needs or what?
>> Yes.
>> Yes. Then you would probably start having different version,
but everything that we see here is under source control,
so that's definitely one way of managing
that because the code and the database schema in
this context will be using a DACPAC goes together so you can
branch out and have variations for individual customers.
>> Awesome, cool. Well,
Eric, thanks so much for coming on the show.
I personally learned a lot.
Having seen a ton of EF Core personally myself,
I know I am a rare breed in that case.
But I know a lot of folks are out there using Entity Framework,
and even if they're not,
maybe this is a good sign to take advantage of some of the tools
and capabilities like you showed off here today.
Thanks so much for coming on the show.
We'll put some links in
the description so folks can go learn more,
get access to these tools,
and start in under five minutes with Azure SQL database.
To our viewers, if you like this episode,
go ahead, give it a like,
subscribe to our channel,
leave us a comment and let us know what you think,
and we hope to see you next time on Data Exposed.
[MUSIC]