334. Is Entity Framework Still Relevant When AI Can Write SQL?

Every technology, framework, and feature in software development has a drawback. That's the reason why we have so many options, and it's why it depends is such a common approach to any question. And software development is always changing, so we need to constantly evaluate what worked yesterday and see if it still works as well today. In this episode of Dev Questions, I want to start the conversation around ORMs and if they're even necessary anymore in light of AI-empowered development. Software development is more than just writing code. So, let's talk about the rest of it. Specifically, let's talk about if Entity Framework is still relevant to modern software development. Now, this episode is a bit different than normal. I want to talk through with you what I've been working through personally. I don't think this subject is decided yet, nor do I think there's one clear answer for everyone. However, I do think the landscape is shifting, and I want to use this as an opportunity to help you work through your own decision-making process around AI and development in general. So, let's start with a baseline understanding of Entity Framework. Now, it was designed to reduce T-SQL by saying, "Hey, if you write a model in

C# just say that model is now in my table." And that reduces the T-SQL you have to write. It makes database It makes database development faster at the expense a bit of the speed in production. Now, it accomplished this with a lot of generated code. And it can be abused by developers who think they don't need to know SQL if they use Entity Framework. So, I'll be careful here. Entity Framework is a great tool. It is also abused a lot, okay? So, there is a bit of a you know, little bit of column A, little column B in any of these decisions. Now, if you want boil it down to its essence, Entity Frame- Framework is supposed to make your development experience faster at the expense of runtime performance. And again, to be clear, I'm not saying it makes your runtime performance terrible. Unless you abuse it. But, it is going to be a little bit slower than if you didn't use Entity Framework. It's got overhead.

Now, as you probably know, personally I like I like to use Dapper, which is a micro ORM, okay? I still like to write the T-SQL. Um, I still have to do it myself. The I think the T-SQL is is pretty easy to to to write because I'm a a SQL developer. But, also T-SQL is easier to debug when it's in the database as opposed to ad-hoc queries. Um, I still have to maintain the database project. If you don't know, there is a database project type in Visual Studio or in .NET um that you can then maintain your your SQL project in and do things like refactoring, etc. But, I still have to maintain that additional project as opposed to Entity Framework where that generated code does it for me. Now, every data call that I make, I have to write a query for or at least have a query that will stand in for that. I may have three or four calls that use the same query. To boil it down to its essence, Dapper makes my runtime performance faster at the expense of development performance, which is why I have always landed mostly on the side of using Dapper. I don't mind spending a bit of extra time in development to make my runtime performance faster.

Now, now we have an understanding of these two options, I think it's time we ask some questions. Uh, number one, why is Entity Framework less performant? And this comes down to assumptions. It's really what it comes down to. Entity Framework is going to write boilerplate code, but it doesn't know what you're thinking. And so, when you say, "I have a model that has string first name, string last name." It doesn't know how big are those strings. Should they be 100 characters, 200 characters, 300 characters? We don't know. And so, it does things like create nvarchar(max) fields for every string field unless you specify a size. Which makes sense when you think about it because it doesn't know what size your string might be. Those decisions along with a number of others that can kind of pile on top of those can cause overhead. They can cause your application to run slower, your database to run slower, queries to run slower.

Now, yes, you can write better EF models where they say exactly how many characters and and what data type it should be for a string type, etc. You can do a lot more work in C# to make sure that Entity Framework has all the information it needs, so it doesn't have to make an assumption. The problem is is that takes a lot of work. And that a lot of work really reduces how much value you get out of Entity Framework making your job easier. Okay? There's also just the overhead of it has to run a lot of code. There's a lot of code that has to run in order to build those queries, in order to send those queries across the wire, in order to get the data back. If you're not careful, then you can have a lot of duplicate duplicate data come across the wire so they can reconstitute that into a a nested model. Now, there's ways again to make that more efficient, but again, you have to know more about Entity Framework.

But that's that's one of the reasons why Entity Framework is less performant. There's just the the pure overhead of it's running more code and also a lot of assumptions in your code that has to make because it doesn't know unless you're really careful to specify everything. So, why is Dapper development slower? Well, when you're writing a stored procedure or query by hand and then trying to keep those in sync every time you make a change, especially during the early development process, once you've kind of locked things in, it's a whole lot easier, but when you're first developing you're like, "Well, I think I'm going to need first name first name and last name." So, I'll create the table that has first name and last name and then I'll create the stored procedures for all the CRUD actions, you know, create, read, update, and delete. And then you go, "Hmm, I actually need first name, middle name, and last name." Okay, so I change the table, I change the the the model, I change um every one of those stored procedures. And then I go, "Okay, is that enough?" And that's where the development process can be slower.

So, is there a way we can meet in the middle? So, you get, I want the development speed of Entity Framework with the production speed of Dapper. So, the question is, can we get there? And I think the answer is yes, with AI-assisted development. Now, some of you may have just cringed all over the place because I said AI in reference to your database and you just got terrified. And there can be a good reason why you're terrified because yeah, if if you are thinking about letting AI loose in your database, don't do that. That's scary. That's dangerous, but I'm not talking about that. I'm also not talking about telling the AI just figure it out. Again, assumptions, not a good thing. But if you say, "Hey, I've built the database. AI, can you turn these tables into C# models?"

Super easy to do, low cost, you know, local models can do this very easily. So, now all of a sudden, the the advantage of well, I can do, you know, database first development with with Entity Framework and have it create the models, I can do that with the AI. Only, they're more efficient because of the fact that it's specific, no assumptions. Now, if you build them out the models in C#, and AI can turn your models into efficient SQL tables. AI can also create queries based upon what data you need. And if you change your model, then AI can update your tables and the associated views and stored procedures, if you even need that, because you also have a database project that can do refactoring, etc. But, with AI, we can do some of this boilerplate stuff. And this is where I think AI can really be a big benefit when it's assisting you, not leading, assisting you, because all of a sudden, now you're allowing it to say, "Hey, here is your very strict requirements, go do this thing, and you just need to check it. That's it."

So, you In my opinion, user experience is incredibly important part of the success of an application. I've known organizations that have spent thousands of dollars per month in cloud resources just to throw resources at a poorly tuned Entity Framework system that they just couldn't tune anymore, it felt like. And they're just trying to get some type of performance out of it. They're trying to overcome that poor performance that was baked into the code by just throwing more resources at more money. And that gives your users a bad experience, because it's still slow, and it crashes a lot, or it's, you know, it's taking up a lot of resources, but it also increases the cost to your company. By utilizing a AI effectively, I think we can reduce the use of ORMs like Entity Framework by re- uh which allows us then to reduce costs and increase customer satisfaction. We can kind of have the best of both worlds.

So, this allows us to say, "Hey, I want some of that generation ability." But, instead of trying to use code generators, which have to make broad assumptions, you know, from the factory essentially, um Microsoft writes these code generators to work in the broadest number of circumstances. Your AI assistant is building based upon your specific needs. And so, it's very much more uh focused on you and less the the generic, "We hope this works for most people." And it's very specific to, "This should work for your specific case." So, also, we have that same code generation, but instead of being entity framework and boilerplate code code and a lot of uh generated code that really we have to support even though we didn't write it, and we might not even look at the code, which we've been doing that for years before AI. Uh instead of doing that, we can actually use AI to help us use a faster tool like Dapper or even just go ADO.NET if you want. Oh, I think that Dapper provides enough tooling um in that micro part without providing a whole lot of overhead. Um I think it's worth it. But, but even so, you can have this micro ORM that accesses our database very, very efficiently while the same time not adding to our development experience.

So, while ORMs like Entity Framework are a big target, I don't think we should stop there. Now, I would like you to think about how you can make um something better, more efficient in your software by using AI or using the new ways of doing things. So, not just saying, "Hey, we've done this for years. That's the way we're going to do it going forward." But, thinking, "Hey, how can we be more efficient? How can we provide a better product for our customers at a cheaper price at a at a more you know, a faster turnaround without creating AI slop, Without creating unmaintainable code. I'm not advocating for that. In fact, I'm advocating against that. But, just because you can abuse something doesn't mean there isn't a place for it. Kind of like how Entity Framework can be abused, that's not to say there wasn't a place for it. But, I think when we talk about AI and think about the new ways of doing software development, I think we need to think through how do you make this process better for everybody?

So, we want to create better software based upon wiser decision-making that does adjust with the times. Because the times are changing. And if we just stay where we're at, we're going to get left behind like every generation before us that has done the same thing. So, when you need to think about adjusting, we need to think about, "Hey, where can we make things better and more efficient with these new ways of doing things without losing who we are?" Without making a poor product, without making a a messy product or a slop product, but instead making a more efficient product that's better for our users. All right? Thanks for listening. As always, I am Tim Corey. >> [music] [music]