SSWUG-TV
With Steven Wynkoop, Sam Brace, and our Field Reporter – Kevin Kline
In this new episode of SSWUG-tv we take a look at the purchase of Green Hat by IBM. Come Check It Out
Is Entity Framework the successor to ODBC?
Lots of good input on this topic. Some of the responses are contradictory. Here’s what some of our readers think…
Aaron:
…Entity Framework is depecrated, don’t you know that LINQ is the future?
Maurice:
Entity framework requires Linq to Entity. Is Linq to Entity allows you to use a CTE to produce a set of rows to update by a multi-row update such as?
With MyRowSetToUpdate — assumes this CTE returns many rows
As
(
Select A.* from A join B on a.key = b.key Where b.anycolumn = ‘avalue’
)
Update MyRowSetToUpdate — many rows that satisfy a business requirement
Set anotherColumnFromA = ‘newValue’
One may say that an equivalent to this is possible with Linq to Entity, but it is poorly implemented by Linq To Entity. It is going to translate the equivalent process to a row by row update. Why? Because it makes a local collection of rows to update, and then using this collection starts a loop to do individual updates. ORM falls short in this type of mapping.
It makes a difference in performance in cases where the number of rows is above 10 and a very big difference when it is in the 100 and above. Link to entity increase the network traffic and log writes because it is not able to translate simple business logic in set-based query like this one. This T-SQL example shows that a very simple business requirement like ‘update a row for each row that match some other row in another table for a given condition’ can be processed in one single query, which reduces greatly log writes and network traffic. Also, in the case of SQL Server, it reduces trigger activation to only one occurrence, since it is a single update query.
Granted that Linq To Entity is better that hibernate in generating queries that just return rowsets. It can generate a single query with joins if you are following some strict and hard to grasp rules when reusing Linq To Entity expressions. This help reduces network traffic, but forget about other query performance tweaking. There is no way to optimize the generated query by adding some query hints.
When it comes to Inserts, Updates Or Delete Linq to Entity falls short when muti-rows operations would be welcome. So if Entity framework rules, you will undercut the ability to make clever queries that would make your application perform greatly.
Linq to Entity precludes the use of T-SQL user defined functions, that eases very much translating business logic in database server-side business logic and brings the same improvement than the above (reducing network traffic, query parsing, log writes).
Great gains can be done if you translate business rules into set-based SQL expressions, but forget that with ORM.
Bryant:
I am always amused that .Net programmers, as an example, need an ORM because they can’t deal with the abstraction rows and columns present (extended pun intended).
What bothers me with things like LINQ (and possibly the Entity Framework) is the ability of 3rd generation-taught programmers writing SQL code just because they can. I say 3rd generation because IMO any language that still contains a DO loop is 3rd generation, OO or not. As long as SQL data access is controlled through stored procedures where they can be developed by people who have at least an inkling of Set Theory and proper SQL then the formulation of abstractions to emulate OO models is a good idea. It makes application developers more productive and code more extensible and maintainable.
Clearly, there is some confusion here regarding capability, implementaiton strategy, etc for accessing a relational database. Some feel that EF or LINQ precludes the ability to use stored proceudres or SQL Functions, which is not true.. Others wish to maintain all business logic in the database. Lets see if we can get some better clarification. Send your response and any links you would find helpful to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
10 IT Career Boosting New Year Resolutions (Part 1)
At the beginning of every year, many people in the IT field explore the possibilities to improve their career and professional life through New Year resolutions
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)