From the course: C#: Advanced Practices

LINQ, Entity Framework, and remote data sources - C# Tutorial

From the course: C#: Advanced Practices

LINQ, Entity Framework, and remote data sources

- In our last video, we spoke about how LINQ is lazy, how I can be in control of my queries. So in this video, though, we're going to talk about IQueryable and Entity Framework. And so before we were looking at, we've been using LINQ but it was for very simple data structures. So can it work with any other types? - Exactly. That's why IQueryable and Entity Framework are important. - [Mika] So let's jump into a project. - [Bill] So this is an ASP.NET Core application that makes use of Entity Framework, which is another whole series of videos that you can look at. What we're doing here is this is going to use LINQ queries now to get data out of a database. - [Mika] Right, so we can still use LINQ queries. - [Bill] Exactly. That's the whole point of LINQ. No matter what our data source is, LINQ is the way you query data. So we'll bring the application up and look at the products page. That's basically the list of all the products. And in the code you can see we're doing this context, that's part of that Entity Framework data source, products is like, the entire sequence of everything. And right now we're just using that entire table to basically bring in the entire list of products. - [Mika] So let's set a breakpoint at this index class over here. - [Bill] All right. So right here in the main page, OnGetAsync which is where we load our data, and I'll refresh the page. - [Mika] Yeah, let's see what executes. - [Bill] Okay. So now we're right here on this, and it's just one line. But once we look at product, once I hit F10, and now we can take a look at what's in it, and we can see all of the things that were returned. - [Mika] It returns the products property. - [Bill] Exactly. So that is the entire list of products. Do you want to break this up into a LINQ query? - [Mika] Yeah, let's do it. - [Bill] So we would say, let's go ahead and just say var, all of them, equals from P in our context, dot products. We can just select P. And then here I can await, we could just say all.products. - [Mika] Cool, and then we can say in. - In instead of on, there we go. Okay. And I will just continue. And there we go. Now it should be able to continue as well. (Mika chuckles) So we'll stop it. We'll restart it and we'll just rerun this and we'll walk through that breakpoint again. So we start it up and now it's looking like a different LINQ query, and I need to not put that in. There we go. Now it'll build. Once we had that all, I didn't need that products property in there anymore. And now it's building, bringing it back up, back into our main page. And then let's load our products page. And now here, we've got to the first point. And let's look at what all is. That's where all the magic happens. And let's look at that expression. Doo doo doo. The expression has all this different text in it which is kind of, that's sort of like how LINQ thinks of what your code looks like. So there's all that text and that's how it translates. This is the recipe you wanted. And Entity Framework then translates that into a SQL query and we'll send that to SQL, but nothing has been run. And the other thing that I think is really interesting here is if we look at that entire collection again, that last property called results view. We've got that special message there that says, expanding the results view will enumerate the IEnumerable which really means something once you've learned LINQ. And what that means is it hasn't done any of the work yet, so we don't have any results. And now, if I were to open that, it would make a trip to the database and get everything and come back. But otherwise it's going to wait until we execute that next line where it says, I actually want to go to the database, execute all the code and bring it back. And like we learned in the last video, use ToList to make it into our storage. So now if I press F10 and we execute that next line and we look at our products variable, there's our products. - [Mika] So does that mean I'm pulling in all of my products? - In this case, you are because we are pulling in the entire list, but I think you're right. Let's take a look at that. And if I click on one of these, why don't we set a breakpoint into the details page? So we're going to look at, just do the same kind of thing on just one of those. So if I click on our squeaky bone, here again, we've written it, 'cause it's kind of a simple query, on just all one page or one line of code. So as soon as we execute this, we'll have our product and that's going to be one of the things that came out of our database. So we put this query together, sent things to the database and got it back. So let's go ahead and we'll stop debugging and we'll make the same changes here. And let's look on this side. So here, I'm just going to say that all those products, is going to be that underscore context.products, and write it as a query. All right. Just like what we were doing before, in all products select P and then here, I'm going to say await, and I'll replace this, which was, you know, that same kind of thing with the property. And we'll just say all.FirstOrDefault. So now we've split it into two. And let's look at how that changes, if anything, the way this executes. So I'll start it up and we'll get to the details page. And look for that squeaky bone again And we'll click the products page. We still had a breakpoint there. So we're finished learning about that. Let's go onto the squeaky bone and here we are again. So we've executed this all. So we've defined that query. And once again, our results view would say, expanding that results is going to do something different. And our expression still just has all that crazy text in it. But now if I execute that next line, now we can see that product. We brought back only one, instead of all four. That all variable is still, it's still part of the query, right? And it's still, we never did grab in all four. So that result view still says, I'd have to expand that if I wanted to see what that looked like. So it doesn't go get everything. It only gets the stuff you needed, even if you put it on different lines of C# code or even if it was different methods and so on. So you can really write code the way you want to read it and let LINQ compose that into a single query for you when you've put together all your database logic. - [Mika] Wow, that's really cool. - [Bill] It's fun. It's a great way to work with data and it'll work with any data source. - [Mika] Nice, will it work with .NET Apache Spark? - [Bill] Not yet. That's one that just got released, part of .NET Core 3, but we certainly have aspirations that at some point a query provider, which is a really difficult thing to write if you think of everything Entity Framework does, that we may have something similar to let you use LINQ syntax to go write queries against a Spark data source, which would be incredibly awesome. - That would be incredible. Wow, so that's pretty awesome. - Right. So the whole point is you write queries against your database and those queries will only execute when you actually want the results. However you've composed them, under whatever methods you write. And that concludes our LINQ series where we went through the syntax in LINQ, gave you a great tool to explore that on your own with our 101 LINQ samples, talked a little bit about the execution model, and then applied that working with Entity Framework and an IQueryable data source. - [Mika] Yeah, and be sure to check out our next videos on async and we'll see you there.

Contents