May 23, 2016 at 12:01 pm
When I say "I have to give direct table access" what I mean is having had an ORM inflicted on me the ORM fans will now push for direct table access.
I've had nHibernate working with stored procedures. The enthusiasm from the developers was conspicuous by its absence.
Mapping to a view doesn't count because it is not the ORM that's doing the mapping, the DBA putting in a coping strategy because the enthusiasm for using an ORM rarely goes beyond the realisation that SQL can be avoided.
May 23, 2016 at 12:39 pm
David.Poole (5/23/2016)
When I say "I have to give direct table access" what I mean is having had an ORM inflicted on me the ORM fans will now push for direct table access.I've had nHibernate working with stored procedures. The enthusiasm from the developers was conspicuous by its absence.
Mapping to a view doesn't count because it is not the ORM that's doing the mapping, the DBA putting in a coping strategy because the enthusiasm for using an ORM rarely goes beyond the realisation that SQL can be avoided.
So then you educate your "ORM fans" and make sure they understand that direct access isn't needed? Yeah, I know, easier said than done, and if you don't have the authority to push back you're pretty much screwed, aren't you?
Sounds like the problem is your environment as much as it is the ORM. I certainly understand that and I can sympathize.
It's ironic that an ORM really doesn't remove the need to know the SQL, and it's unfortunate that many (if not most) developers don't understand the problem that ORMs are trying to solve.
September 29, 2017 at 1:00 am
new DateTime(2016–04–01)
should be
new DateTime(2016,4,1)
September 29, 2017 at 1:36 am
Really? What you describe here has been documented in many other articles since EF was released. And Microsoft released about five versions in two years after the initial release because it was full of bugs. Any serious developer working on a SQL Server database should understand the Database schema well and be familiar with the possible transactions and stored procedures. I never found object to relational mapping just using C# and TSql stored procedures and raw ADO.Net a big deal or a chore and in fact knew I had complete control over how the records were retrieved and updated. Serious production databases tend to have 15 plus tables in the schema and may also Have some complex transactions other than the basic CRUD actions. EF is essentially Access with ORM mapping and is OK for a schema with up to 10 tables. EF is one big fat wrapper round ADO.Net
....and you already know ADO , don't you ???
September 29, 2017 at 3:00 am
Developer here - I hate ORMS and I know SQL Server SPs like the back of my hand, please don't generalise when you say "developers".
The article is also not true when you have to repeat lots of connection, cmd code - you don't. You can write wrappers, for example I in my code I can say:
using (SqlDataReader dr = dataAccess.RunSpForReader("mySpName", new Dictionary<string, object> { { "Id", id } })
{
while (dr.Read())
{
yield return dr.GetFor(new SomeObject(), new object[] => _.Prop1, _.Prop2, _.Prop3);
}
}
In the code above SomeObject has 3 properties. I have an extension method GetFor that can be called on SqlDataReader. It returns the object passed in as first param. The lambda expression defines the properties from the object passed as first param that it wants to set. So effectively the stored procedure has to return a field called "Prop1", "Prop2" and "Prop3", the method behind the scenes has small helper methods that look at the type of the object property and use the appropriate GetInt32(), GetString(), GetCustomMethod().
The above code is written into a repository pattern class that implements an interface of which the above is from the SQL version. I can implement a test version of the same interface that gets data from somewhere else (XML, JSON, CSV) in order to test. Essentially in EF and other ORMs you still have to write configuration code to map between DB and objects, with the described code pattern and helpers mentioned you are affectively doing the same thing. The main difference I can see is that I can do whatever I want to cope with whatever new feature SQL introduces unlike with something like EF where I am at the mercy of whether it supports it. Anything that is remotely different to pure CRUD is off the table.
September 29, 2017 at 6:25 am
In my experience EF and ORMs are just another tool and like any tool they can, and unfortunately are, often used to advantage and disadvantage.
In general, the simpler the data structure the better EF and other ORMs work. As soon as it gets beyond simplistic queries and relationships the standard use of EFs and ORMs begins to fail and becomes an impediment rather than useful. While functionality can be deferred into stored procedures this adds another application layer where business logic can reside and therefore deployments, upgrades and reversions become more complicated. The work around for this that I often see is to deploy more complicated and crafted EM/ORM code however beyond simple queries these rapidly become unintelligable (bad for maintenance) and as noted by others here, start to require another skillset and introduce more scope for bugs caused by the EF/ORM layer.
All this is before we start to use the functionality and features of the database itself to our advantage at which point a generic abstacting layer becomes an impediment rather than a useful tool, particuarly when it comes to adequately handling errors/exceptions (something that EF and ORMs tend to ignore and pretend that handling expected errors is a chore and instead prefer to throw unintelligable exceptions to end users). The other issue that I frequently come across is where the EF/ORM designs the database at which point we get a horrendously inefficient database both in structure and data definition terms. What passably works for a user interface does not necessarily work for anything else.
September 29, 2017 at 7:23 am
I have been using EF for four years now. What we have done in our environment is the following:
We use EF and only call stored procedures. Join them to functions and custom entity types when needed. This allows us to use POCO classes for our objects in C# that we share in our model layer with other applications when needed.
This also provides the ability to have other front end applications use our database in a controlled way. It also allows us great efficiency in pulling data using all of the benefits of SQL Server.
We do use LINQ but not to directly query the database except in rare occasions. We use LINQ if for some business rule we need to filter or manipulate the data in our business logic layer.
Our gains:
1. Fast performance and data retrieval.
2. Auto generated object classes.
3. Easy implantation of calls from our business layer.
4. Database integration standards are easily enforced.
We fought and fought over EF for almost two years. Now we see the benefits of not just "drinking the kool-aid" but doing true evaluation of how to use EF in your own environment.
September 29, 2017 at 7:32 am
Michael Clark-449749 - Friday, September 29, 2017 7:23 AM(snip)We fought and fought over EF for almost two years. Now we see the benefits of not just "drinking the kool-aid" but doing true evaluation of how to use EF in your own environment.
I very much suspect that you are doing it "right" - which unfortunately is slightly different to most of the tutorials, introductions and guides on using it.
September 29, 2017 at 7:53 am
I wrote a related post about this years ago. It all still holds true IMHO: How to Fail at ORM
TLDR: I've seen the good and bad with ORMs. Success comes when 1) they aren't seen as a black box / replacement for SQL knowledge, 2) the developers using it know the tool deeply (i.e. know about the items in the post along with others), 3) the developers are willing to drop down to SQL when they are getting poor performance (this assumes they are even profiling). Also as other's have pointed out, the simpler and more normalized the schema, the better.
There's been a lot of talk in this thread about how code generation can solve the same problem. I think this is partially true. Code gen is good at solving the mapping issue, but I've yet to meet a code gen'd DAL that does the following:
1. Can grab only the fields you need without having the developer supply a one off string of SQL.
2. Can help you avoid the n+1 select / looping against the db scenario listed in the post (yes, this happens all the time with sproc based DALs too)
3. Does change tracking on the loaded objects to figure out if updates should be issued even if the developer calls Save()
4. has an identity map / cache. If I ask for the same data twice for different parts of the page, only load it once.
I'm not saying these items aren't possible with code gen, but I haven't seen it. Besides, haven't you just code gen'd a home grown ORM by that point?
I'd also like to point out that although this type of code is considered to be boilerplate, developers should not be writing it by hand. It is very difficult to do it consistently and correctly. You'll have a lot of ongoing / whack-a-mole type bugs.
September 29, 2017 at 8:26 am
William Anfin - Tuesday, May 17, 2016 5:45 AM"a feature that removes a lot of the tedious boiler plate code that developers would otherwise have to write." Well, we do have code generating software for that. I still like creating my own custom DAL but I have to admit that I don't know much about Entity Framework.
I'm with you! I prefer having a little extra and knowing exactly what's going on in my queries than rely on some ORM's black box magic.
September 29, 2017 at 12:39 pm
call.copse - Tuesday, May 17, 2016 3:22 AMI'm going to say there is absolutely nothing wrong with the query in point 3. It's a little ungainly looking, sure, but that is here nor there regarding performance.
That is absolutely incorrect. That parameterized queries will check the value sent in on the first execution, get a good plan for that, and STORE THAT PLAN. So say you have 10 years worth of data and you pass in a parameter that is YESTERDAY. BAM - index seek, lookup, 10000 rows out of 50M and you have a VERY fast query. The very next execution passes in 17530101, so you hit ALL 10 YEARS WORTH OF DATA - with an index seek, lookup plan. You can go buy and eat lunch while that thing is spinning a bajillion logical reads.
The opposite direction kills you too, where you scan the table for all data then use the same plan to bring back 1 day of data (or an hour or minute ...).
So, the lesson of the day is that many/most queries ever written that have such a date comparison predicate (or the ubiquitous @StartDate/@EndDate pairs used in reporting queries) should have OPTION (RECOMPILE) on them. Trade CPU ticks (3-5 BILLION COMPUTATIONS PER SECOND PER CORE) to avoid DISASTROUSLY bad plans.
Thus endeth the lesson. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2017 at 12:51 pm
1) Developers don't want to be bothered with RTFM (Read the Fine Manual). BTW, I was an officer in the US Navy in a past life, and I assure you they didn't use the word "Fine"!! :hehe: And for those that DO care what their code is doing to SQL Server, well, you actually have to think and write MORE CODE, thus reducing the benefits you were supposed to get from EF in the first place. Regarding that - see #4 below.
2) When I pick up a new client with EF in play you know what I do - go KAAAAACCCCCCHHHHIIIIIINNNNNNGGGGGGG$$$$$$$ 😀
3) I have never, EVER come across a client (~25 years consulting on SQL Server at mom-and-pop to Fortune 100 sized clients) that develops in EF where the application(s) wasn't bad - usually HORRIFICALLY SO!!!
4) ORM Guruism #1: Anything that allows developers to slap together code more quickly is inversely proportional to the performance and concurrency you will get from that code.
5) ORM Guruism #1: Code First - Performance Last!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2017 at 5:23 pm
Michael Clark-449749 - Friday, September 29, 2017 7:23 AM<snip>We use EF and only call stored procedures. Join them to functions and custom entity types when needed. This allows us to use POCO classes for our objects in C# that we share in our model layer with other applications when needed.
<snip>
Could you explain what POCO is? Being used in this context, I must assume that it has nothing to do with the band.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2017 at 3:36 am
WayneS - Friday, September 29, 2017 5:23 PMMichael Clark-449749 - Friday, September 29, 2017 7:23 AM<snip>We use EF and only call stored procedures. Join them to functions and custom entity types when needed. This allows us to use POCO classes for our objects in C# that we share in our model layer with other applications when needed.
<snip>
Could you explain what POCO is? Being used in this context, I must assume that it has nothing to do with the band.
Plain old class object
October 2, 2017 at 9:25 am
funbi - Monday, October 2, 2017 3:36 AMWayneS - Friday, September 29, 2017 5:23 PMMichael Clark-449749 - Friday, September 29, 2017 7:23 AM<snip>We use EF and only call stored procedures. Join them to functions and custom entity types when needed. This allows us to use POCO classes for our objects in C# that we share in our model layer with other applications when needed.
<snip>
Could you explain what POCO is? Being used in this context, I must assume that it has nothing to do with the band.
Plain old class object
Thanks.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply