May 17, 2016 at 1:00 pm
If you are developing apps that access a Sql server database then you should understand the database, how to design it , get and update records and use stored procedures.to encapsulate database commands. You should be be able to walk thru all the transactions you need to perform and see that the tables are structured correctly and the stored procedures perform to spec. Do this with sql management studio....essentially you test the database tier in isolation from the rest of the app.
Entity Framework is a bit fat wrapper around ADO and it took Microsoft about seven releases to get something that was relatively stable. It is essentially another version of Access as it is ok for simple databases with less than 10 tables. The real world is much more than 10 tables.
I have used an n-tier architecture of ui, ADO. NET ,custom business objects and sql server with stored procedures for over 20 years. The boilerplate code to write is negligible and can be automated, I have separation of concerns and can test each tier independently...and most important of all I have complete control over the code...and not have extra unnecessary code injected by Entity Framework. And lazy loading has been around for years. And you just kick single classes or collection of classes across the different tiers under your control.
...hmc
May 17, 2016 at 1:01 pm
If you are developing apps that access a Sql server database then you should understand the database, how to design it , get and update records and use stored procedures.to encapsulate database commands. You should be be able to walk thru all the transactions you need to perform and see that the tables are structured correctly and the stored procedures perform to spec. Do this with sql management studio....essentially you test the database tier in isolation from the rest of the app.
Entity Framework is a bit fat wrapper around ADO and it took Microsoft about seven releases to get something that was relatively stable. It is essentially another version of Access as it is ok for simple databases with less than 10 tables. The real world is much more than 10 tables.
I have used an n-tier architecture of ui, ADO. NET ,custom business objects and sql server with stored procedures for over 20 years. The boilerplate code to write is negligible and can be automated, I have separation of concerns and can test each tier independently...and most important of all I have complete control over the code...and not have extra unnecessary code injected by Entity Framework. And lazy loading has been around for years. And you just kick single classes or collection of classes across the different tiers under your control.
...hmc
May 17, 2016 at 1:12 pm
In your article, you have:
var results = db.Customer.Where(c => c.State == "Ohio");
Which is changed to this to limit the query to only the necessary columns:
var results = db.Customer
.Select(x => new
{
x.State,
x.Name,
x.Address,
x.Email
});
Now, I'm not an application developer, but I can see that these two queries are not equal - there isn't a predicate on the second one, so it will get all of the rows in the table. For completeness sake, how would this second query be written to implement this where clause? Is it db.Customer.Where().Select()? Or db.Customer.Select().Where()? or something else? If I'm going to be telling the application developers that they need to change, I need to give them a valid example of how the change should be.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 17, 2016 at 2:17 pm
Thanks for the comments everyone. And also thanks for finding the few goofs! (errr..I put them there to test you!). I've updated the article with corrections and hopefully they will be showing soon.
May 18, 2016 at 5:25 am
Use of ORMs to shield programmeurs from the reality of their data and relational database tech, results in:
* overly complicated code (The "concise" LINQ code in the article is everything but that. As soon as it is extended, it becomes language and tool specific code hell)
* badly maintainable code
* faulty/incomplete code
* poor performance
* promotes all of the above as habits form without understanding!
A programmer needs to know the data and organize it efficiently to solve a particular problem.
For this, good knowledge of the tech and tools used is needed and an understanding of the process the data goes trough and the trade-offs that are/were made.
LINQ and large ORMs like EF really do not match the most basic requirements for a good software development tool and are an outgrowth of fantasies filled with wishful thinking.
How many programmers will know and understand what goes on well enough to concisely make the right decisions when using either and when not to use them?
Honestly, pretty much almost none, not even among many of the people working on it!
Instead, use something that is simpler and is easy to understand / follow step by step, by those not already a deep insider of the project and tool.
This promotes true skill development that remains valid over time and is adaptable to new problems.
I seen many people lyrical about both LINQ and ORMs and seen them working and the result was almost always resounding bad in all the mentioned aspects.
Some are obviously better at organizing their code, but even then the core issues about maintainability remain and extreme amounts of time are wasted.
Not to dish the OP as the article is actually a good take on the issue!
I just wanted this of my chest as a professional that is witnessing the software development field, and the level of skill within it, going down the drain in slow motion.
May 18, 2016 at 6:33 am
Support with both hands - IMHO ORM breaks encapsulation principle - why on Earth front end should knew about EACH table in database instead of calling DB operations (aka as Stored Procedures)?
May 18, 2016 at 11:10 am
I'm not experiencing this...not using Select() when I run a LINQ query, I get just the columns defined in my entity
EF will only select the columns required by the Linq query that calls it. In the first example he's producing an enumerable of Customers based on the default constructor and that will use all fields. The Select in the second example causes a transformation to use a specific constructor. Linq sees that, sees the fields that are being used in the transformation, and only selects those fields.
What really escapes me that EF team consciously decided not be bothered with SQL Server improvements (like synonyms) or TVP.
My guess is that it's because they don't want to tie it to SQL Server. It should (and does) work over any data storage - it doesn't even need to be an RDB. In theory you could use it over a bunch of Excel files (though I'm not sure why you would).
how would this second query be written to implement this where clause? Is it db.Customer.Where().Select()? Or db.Customer.Select().Where()? or something else?
All three though the first would be most common. I think the confusion comes from the fact that a Linq Select is not a Selection operator, it's a Transformation operator. So DB.Customer returns an enumerable of Customers, a Where clause against that would have to act on the attributes of the Customer class. But the Select could be used to, for example, transform that enumerable into Orders for those customers. A Where that followed that would act on the the attributes of an Order. That's a contrived and not very good example but hopefully it'll serve to clarify. Essentially, you can chain up a whole bunch of Linq statements in any order you like but each consumes the output of it's predecessor.
why on Earth front end should knew about EACH table in database
It shouldn't. Entity Framework operates as a Data Access Layer and you're only required to map the entities you require. It's up to your BLL (or possibly another abstracting layer, e.g a Presenter) to interpret between the DAL and the next tier up. I think this miss-underanding is mainly MS's fault because when the offer up tutorials it's always for the simplest possible scenario, ie one where the UI maps directly onto the DB. Also, a lot of their scaffolding tools e.g. in the MVC framework, make the same assumption. But 1. a DAL created using EF doesn't need to know about any database entities you don't want it to and 2. the interface presented by a DAL does not need to resemble the one presented to the UI at all.
The attitude of many developers toward SQL and relational databases has always puzzled me
I find the reverse to be an equally valid statement. EF is a very powerful tool and, used correctly, it offers huge productivity benefits. It's not a silver bullet but neither is SSMS. If you think I can't produce efficient code using EF you're wrong. And if you think I can't produce some catastrophically bad code using SSMS you're equally wrong.
Anyone who thinks an ORM is a silver bullet is a fool. But those who dismiss it as worthless because it's not a silver bullet are equally foolish. They're a tool, just like any other.
May 18, 2016 at 11:35 am
FunkyDexter (5/18/2016)
I'm not experiencing this...not using Select() when I run a LINQ query, I get just the columns defined in my entity
EF will only select the columns required by the Linq query that calls it. In the first example he's producing an enumerable of Customers based on the default constructor and that will use all fields. The Select in the second example causes a transformation to use a specific constructor. Linq sees that, sees the fields that are being used in the transformation, and only selects those fields.
Yes, I realize that, but that was not my question. My question is, specifically, why a "SELECT *" shows in the author's EF-generated query when I don't see that behavior when I do the exact same thing. Now, I do get all the fields so it's essentially a SELECT * anyway, but there is no "SELECT *" in my query -- I get an explicit list of all the fields.
So, what causes the difference, where author sees a "SELECT *" but I get the actual field list? I guess it doesn't really matter, but I'm curious why there appears to be different behavior. Best guess it it's how I map the entities, but I'd sure like to know for sure.
In any case I think the author's point still stands -- if you don't actually want all fields, do a Select() to pull only the fields you want.
May 18, 2016 at 11:53 am
Apologies, I misread your post and missed the NOT using select:hehe:
In that case I don't know what the difference is but your guess that it's something to do with Code First seems the most feasible cause.
May 19, 2016 at 3:53 pm
Ray Herring (5/17/2016)
The attitude of many developers toward SQL and relational databases has always puzzled me.The database provides data persistence, can help enforce data integrity, and simplifies many relationship oriented patterns.
All interesting development work is "difficult" and "tedious" in some sense. When we turn to automated code generators to "ease our task" we are depending on deterministic, rule driven code to produce the desired result. Performance, efficiency, and readability are not the main objectives of the tool. It does not matter whether the tool is producing C#, HTML, or SQL.
If you are working on a one-off, demo, or prototype then generate away. If you are putting code in production then Plan For Success and make sure the code has good performance, is efficient, and is readable.
Cannot agree more. 😛
In my experience, EF is best for small sized project (with small DB), if your backend database is big and complex (in terms of table numbers and relationships, and the table rows, say you have many tables with tens to hundreds of millions records), EF (and "code-first" principal) is a big headache / challenge to DBAs and to support people.
May 20, 2016 at 9:44 am
With extreme respect I would like to submitt that custom DAL's are all very fine if you're a one-man operation, but it strikes me that in a larger shop or when there are "succession issues", handing your custom DAL's to the next generation and have them learn it Iand maintain it) (when they already know EF) is a bit of a mug's game. (Saying this because I have this situation in my world -- a hot-shot who won't learn mainstream tools, has to do everything manually).
May 20, 2016 at 5:29 pm
@Holy One,
Using complex tools that require specific knowledge in places that can also work well with something more basic, is I think just not a good idea.
Taking EF re-usability as the example:
* Its large and complex (lots to know, hard to debug).
* Many ways to use and extend on it (even more to know).
* Each way of using it places different architectural and procedure restrictions (removes freedom to seek optimal solution, developer needs to know more and expect quite a bit of custom code).
* It abstracts too much and not always in a good manner (understanding what is happening is a serious issue here, causing hard to discover bugs and sometimes really bad performance).
* Many quite distinct versions as well as the underlying tech/tools, each with their issues (something as basic as where conditions on tinyint columns only got fixed like two years back).
I do not know its current state is and have already written it off and placed on the list of bad ideas (it costed us lots of time and money with very little to show for it).
Proponents will always claim the next version is better, fixes earlier issues, just like with each version before.
In the wild there will be lots of EF based implementations (versions + development methods + custom extensions and sage patterns), each with its own issues and specific knowledge requirements.
To me that hardly qualifies as maintainable and many libs/packages/tools have this same problem.
For example: when mapping objects members from one object to another, just write the mapping code as a series of assignments as this is easy to write, read, verify, modify, is visible and requires no prior knowledge.
But when using a sexy/hip auto mapper package then some hidden magic, based on a custom syntax/pseudo language, happens and all these good traits are gone.
Suddenly someone new has to learn yet another tool, its specifics and discover where the developer has hidden the "mapping configuration" in the code.
Code is way more often read/extended by multiple persons, then written/maintained by one person.
Keeping things simple, visible and recognizable is always easier to dive into later even for the original writer of the code!
May 21, 2016 at 10:44 am
ORMs are fine for prototyping.
The issues I have with them are:
[Ul]
[Li] I've yet to see anyone do the "M" for "Map" in ORM, it's always a 1 for 1 thus throwing away the alleged raison d'être of the ORM[/li]
[/ul]
I think DBAs would be wise to learn as much as possible about the ORM they are threatened with. Full marks to the author for the first 3 things he wishes he knew. Hopefully there will be at least a couple of follow-up articles in the near future
May 23, 2016 at 4:45 am
jeffrey yao (5/19/2016)
Ray Herring (5/17/2016)
The attitude of many developers toward SQL and relational databases has always puzzled me.The database provides data persistence, can help enforce data integrity, and simplifies many relationship oriented patterns.
All interesting development work is "difficult" and "tedious" in some sense. When we turn to automated code generators to "ease our task" we are depending on deterministic, rule driven code to produce the desired result. Performance, efficiency, and readability are not the main objectives of the tool. It does not matter whether the tool is producing C#, HTML, or SQL.
If you are working on a one-off, demo, or prototype then generate away. If you are putting code in production then Plan For Success and make sure the code has good performance, is efficient, and is readable.
Cannot agree more. 😛
In my experience, EF is best for small sized project (with small DB), if your backend database is big and complex (in terms of table numbers and relationships, and the table rows, say you have many tables with tens to hundreds of millions records), EF (and "code-first" principal) is a big headache / challenge to DBAs and to support people.
[font="Verdana"]+1[/font]
May 23, 2016 at 6:52 am
David.Poole (5/21/2016)
ORMs are fine for prototyping.The issues I have with them are:
[Ul]
- I now have to give direct table access to people I don't know, let alone trust
[/ul]
No you don't. EF is perfectly capable of working with views and stored procedures, at least for CRUD. It gets more difficult to work with stored procedures beyond that, though, which I certainly don't like.
I've yet to see anyone do the "M" for "Map" in ORM, it's always a 1 for 1 thus throwing away the alleged raison d'être of the ORM
Does mapping a view count? Doesn't have to be one-for-one to a table.
Beyond simple queries the ORM code gets far more complex than the SQL it is trying to eliminate
I don't think eliminating SQL is the goal of an ORM. It's more about trying to eliminate the "impedance mismatch" between objects and relational systems. I think ORMs do achieve that, though there are inevitable trade-offs that come with it that are not desirable.
I'm not arguing in favor of ORM's, I use EF but so far I've not seen much I disagree with in this thread. Just trying to speak from my own experience, YMMV and all that.
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply