I think probably the most viewed video
of mine in the last few months has been the one I did on in-memory databases in Entity Framework Core.
And in a sense I was kind of jumping ahead, because I've had quite a few
questions asking me to look rather more at the basics of Entity Framework and
particularly Entity Framework Core. So this - and the next few videos actually -
I'm going to be looking at various aspects of that. But for now,
we're just going to look at how to get started in Entity Framework Core. So what I'm going to do here is just today the basics of how to create a database from scratch
using code. So I've just set up here a program called EntityFramework and then
a library called BookLibrary. And the first step that we're going to take
is into that library we're going to add a couple of classes. So I'm going to have a class that I will call 'Book' and I'll make that 'public' and we'll also
have a class called 'Author'. So that means we will just have
an example of two classes with a relationship between
them that Authors write Books. And that, in the database, will be
reflected as two tables with a foreign key connecting Authors to Books. Then let's put some information in there.
So because these are going to go into a database, one thing that will make them slightly different from classes that are just going to be existing in the program is they're going
to need Ids. You can use typically either ints or GUIDs for Ids,
but I'm just going to use an 'int'. And then we're going to say that an
Author will have a 'string' which is going to be 'Name' and then an Author will have a collection of Books. Now actually you can use various
different types of collection for this, but the one that is normally recommended is just to use an interface 'ICollection' generic for '<Book>' and that means that the underlying system can choose what is the most appropriate type of
collection, depending on whether the Books are in memory or still stored
in the database or something like that. So that's all we need for our Author, and
then for our Book we'll again have an 'Id' and we'll have a 'Title' and then for a bit of variety, let's also have a 'PublicationYear' And then we will have our Author. Now I'm making an assumption here that a Book has a single Author. Practically speaking
that's not necessarily true you can have a Book that is co-written
by more than one Author, but for this video at least we're going to keep
things simple with this idea of a one-to-many relationship -
so a single Author having multiple Books. Many-to-many relationships are possible
in databases - they're a little bit tricky to implement -
and we'll see in a video coming up how actually in Entity Framework it's
really quite easy to do that. But for the purposes of this, we've got
that going. We're not going to introduce the database yet; we're just going to
have some manipulation of these items. So what I will do here is just one more thing.
On a Book I will give it a 'override' of 'ToString' and what we'll return there is going to be the 'Title' and then in brackets we'll put the 'YearOfPublication'.
And then on our Author we'll simply have the 'ToString' returning the 'Name'. And then in my main Program, to save a little bit of effort, I've already created a selection of Books - just need to get
hold of the namespace. And so here I can say 'var authors = CreateFakeData();' And then I could do something like, say,
'foreach (var author in authors)' and then I could maybe write out '{author}' - so that'll call a ToString() on
Author - 'wrote ...' and then have another loop and just write out just a little indentation to make it easier to read.
And then again that will call the ToString on Book and then just put a blank line between them. And then I've just spotted I've called this 'YearOfPublication' whereas in here
I called it 'PublicationYear'. So if we just change those two that'll be fine. And then if we run that, we'll see that we get that information.
So nothing to do with Entity Framework at that point, but we've simply put together the classes that are then going to be turned into tables in our database. So now we need to introduce Entity Framework, and I need to introduce a few NuGet packages that are going to make
this work. So if we go in here, we can go to Manage NuGet Packages,
and in our library I am going to firstly have EntityFrameworkCore, so that gives us the majority of
the things that we want. And I'm also going to have EntityFramework.Relational, because I want this connect to a
relational database - EntityFramework can be used with all sorts of databases. And then I'm going to choose the particular type of database that I want to connect to. So we could go for SQL Server - and very often I would do. So we can see
there got Microsoft.EntityFramework for SQL Server. We could go for Oracle - so you can see there we've got Oracle.EntityFrameworkCore - but to keep things simple, I'm just going to
go for SQLite. It shouldn't really make very much
difference, if any, to the programming, but that's still what we're going to
have. So there's our Microsoft. EntityFrameworkCore.SQLite.
So we'll install that as well. And having done that, I now need to write
one extra bit of code. So back in the library, I'm going to add a class and I'm going to call this 'BooksContext' and make it 'public'. And I'm going to derive this from a class that we've just got from that
EntityFrameworkCore package called DbContext - so if we just pop in
the namespace there - and this is going to be our connection
point to the database itself. So this is where the real work of the database
connection happens. And what I need to put in here is a
couple of things. One is, I need to put in an entry for each and every one of my entity types. By that, I mean my
classes that are going to be represented by tables in the database; those are called entities - and Author and Book in this case.
So we'll have a property, and the collection we use here, we've got to be quite
precise on is the thing called a DbSet . So that's going to be a DbSet of
'Author' called 'Authors'. And then we'll also have a DbSet of 'Book' called 'Books'. And what we're doing here,
even though it's not obvious, we are actually starting to configure how the database is going to look, because by calling those things 'Authors' and 'Books' we are telling the
system that that is what we would like the names of the tables to be. Thing to
notice, our classes almost always, in programming, your class name is singular
- 'Author' and 'Book' - but our table names will be plural and so that will sort that out, although, you know, we could choose completely
different names if we wanted to and put them in there. The other thing we have to do in here is give this a constructor.
And the constructor has to take some options which are going to end up providing the rules about how it actually connects to
the database. So the way we pass this in a thing called 'DbContextOptions', which is actually a generic for
this context type. So we pass that in there, call it 'options' and then simply
pass that down to the base class. Okay. So DbContext will deal with all of that. Then in our program we've got to
actually hook up to the database. So the first thing I've got to do is
create those options that we were going to pass into our DbContext. So let's say
'var options =' and then we can say 'new DbContextOptionsBuilder' again instantiated for our 'BooksContext'.
And then it's here that we tell it we want to use SQLite so here we say '.UseSQLite()' and then that has to
take the connection string - so where it's actually going to connect to.
Now in a practical application - and we'll see this more in later videos -
your connection string, you don't want to hard code into your program.
You want to have in some sort of config file. But just to keep things simple here, I am going to hard code it. And with
SQLite, the nice thing about that connection string is we can just specify a file name. And then rather than it going in the actual executable directory, I'll do a couple of
'..'s to bring it up to this level. And let's just call this 'MyLocalLibrary.db' And then on the end of that I say
'.Options' to actually give us back our options object. And we'll notice if we look at that that, that is of that type that we set up
as the constructor parameter for our BooksContext. So now let's actually use that; let's say 'using var db = new BooksContext(' passing in those 'options'. Just an aside I'm using
the new C# 8 style of 'using' there, where I don't
have to put it in its own block, and this will have its Dispose() called
just when we go to scope there. Next thing I'm going to do - and again
there are various ways around this - but I'm going to do it the simplest way;
I'm just going to do 'db.' and then 'database' and then '.EnsureCreated();'
and what that means is, because this is going to be the first
time that we run it, that will actually create the database with those tables for us. And in fact if I just comment out this code for now and run it up at that point ... takes a little while because it's got to
create the database ... and then the program closes because I
wasn't doing anything. But we can see, because I did all those '..'s, it's
put it into our folder there and so it's created our database because I did that 'EnsureCreated ()'. We can have a look in there actually. This is actually a plug-in; it's not an
absolutely standard part of Visual Studio, but you can get this SQLite toolbox plug-in - have a look at that and if I just hit the connect button there, it actually
automatically looks for local dbs. And there we've got the one I created.
And if we look inside there, we can see that it's got table for Authors, table for Books. Those won't have any data in there yet, but they've actually got
the structure put in there. Let's take that one step further then. So let's get those fake Authors that I created there, and then let's add them to the 'db.Authors'. So remember that we put in those two DbSets on
there; one for Books, one for Authors. So I will take that one for 'Authors' and just do an 'AddRange ()'
and pass in those fake Authors. Now notice the neat thing here. I don't
explicitly have to add any of the Books because the Authors already have Books in their collections, and I've now added the Authors to the database, the database
- the Entity Framework system - will follow through all those connections and make sure everything actually gets put into the database in the correct way. So all I actually have to do is - one thing to remember - I've got to do a 'db.SaveChanges();' Until you do a SaveChanges, anything you've done is only in memory. SaveChanges() starts a transaction, does whatever SQL is necessary to issue that to the database and then commits the transaction. So if anything goes wrong, nothing happens or it'll all go in there in one go. And so now if we run that again, we won't see anything. A bit quicker that time because it
didn't have to create the database, but now, if we look at Authors and just do Edit Top 200 Rows, there we can see our two Authors automatically had Ids allocated to them.
If we look at the Books we can see ... got all the Books, they've got their own Ids, but also it's worked out that foreign key. So we
never mentioned AuthorId. It's just made the assumption
that because we had, in our Book class, we had a reference
for 'Author' it has decided the most sensible name for that would be 'AuthorId' as the foreign key, so it's set all of that up. Also something you'll notice, it's worked out for itself - if we look in here - is that 'Id' in both cases should be a primary key. How did it know that?
Well, it knows that because everybody calls their primary key 'Id' - or
not everybody, but it's a common enough convention that rather than us as a programmer having to do anything explicit to say
I want you to make this the primary key, it just does it. It's what's known as a principle of convention over configuration.
So because by convention, Id is very commonly a primary key, it just works these things out. All of these things can be overridden if you want to do things differently.
And we'll see in a later video some of how to do that, but why go against the flow?
For most things following these conventions
not only makes your life easier in that you just let the tools get on with it,
but also makes the code easier to read for anybody else, because most people
are following the same conventions. Okay. So having got the data in there, let's just change our program a little bit to read it out. So we'll get rid of the creating of the fake data, but let's put back in the printing out. And so rather than Authors, will now just say 'db.Authors' and so that should give us our collection. Let's run that
and we can see it got as far as 'Jane Austen'. So it read in 'Jane Austen',
but then it hit this NullReferenceException, and that was happening actually when we got in here. If I just run that up in debug, we'll see that if we look at this Author it's 'Jane Austen'. But the Books collection is null, and this comes down to a concept in EntityFramework known as 'loading', and the contrast between 'lazy loading' and 'eager loading'.
If we had lazy loading enabled, then when the system got to this point,
saw that books was empty, it would have issued another query to get the Books back from the database and then be able to display them.
But actually, although lazy loading is available in Entity Framework Core, it is very much deprecated. In Entity Framework 6 it was kind of the default option, but the problem with it is it can be very inefficient, because
what happens here is we issue a single query to get all of the authors, which remember, as far as this example goes,
is two authors. But then on each iteration of that loop we're going to be
issuing another query, firstly to get Jane Austen's books and
then secondly to get Ian Fleming's books. And you can imagine if this were a big
system where we had a thousand authors that would be a thousand and one separate queries being issued to the database to get back
the authors and all of their books. Therefore what is a much better idea is to use eager loading, where we specify upfront what it is that we want to load. And so what I would do here would be 'authors.Include(' and then using a lambda. I could say 'a' for author '=> a.Books)'. So don't just get the Authors; get the
Authors and their Books. Much more on eager loading in later
videos, but now if we run that we can see that that gives us the Authors and their Books. And really that's the basics of Entity Framework. We've managed to create the database, we've managed to create data for the database and we've managed
to read it back. One last thing I'll just show you - and
then we'll finish it for this one - is that we can also issue queries.
So just make sure that we've got LINQ included here, so 'using System.Linq;' and then in here we could say 'var recentBooks = from b in db.Books where b.YearOfPublication > 1900 select b; '
And then in my loop here I could go through 'var book in recentBooks' I'm in this case going to do an 'Include' of author because, again, it works both ways. If we
didn't do that, we'd just get the Books. And then I'm going to put something like
'WriteLine' and then '{book} is by' and then we can have '{book.Author}' And if I run that, we'll see it does the query and we just get the Ian Flemings
because they're the modern books. So very quick introduction, but that is how
you can create your first database with, in this case, a couple of classes - a one-to-many relationship. Other things like many-to-many relationships, inheritance, all those things are possible, but
they'll be seen in later videos. So I hope that was helpful, and do watch
out for the next one where we'll be looking at some other features
of Entity Framework and Entity Framework Core
