Completely missing the point

[pkmnfrk]

The CTO at our company has a habit of finding out about a new shiny library or technology, and doing a bit of experimentation to find out if we can integrate it into our current or future projects. This is all well and good, but certain co-workers sometimes have trouble understanding the point of some of these technologies.

For example, we've been playing around with Dapper-dot-net, a lightweight SQL object mapper that allows you to map queries directly onto classes (eg, a pre-written class) or onto dynamic objects. It's really neat, and really easy to use, too:

using(var dbConn = GetConnection()) {
    foreach(var u in dbConn.Query<User>("SELECT * FROM [User] WHERE Salary < @salary ORDER BY Salary", new { salary = 40000 })) {
        //do something with u
    }
}

Basically, whatever query you write, that's what your results are. I ended up doing the evaluation, and we liked it enough to start using it in an actual project.

The other day, one of the developers on that project came to me for help wondering why his queries were so slow. I went over to his workstation to look at his code, and then very nearly facepalmed. This is what he had written:

using(var dbConn = GetConnection()) {
    var list = dbConn.Query<User>("SELECT * FROM [User]");
    foreach(var u in list.Where(u => u.Salary < 40000).OrderBy(u => u.Salary) ) {
        //do something with u
    }
}

The difference between this sample and the sample above is that this one pulls the entire table, and filters it and sorts it in code, rather than doing it in the database.

When I asked him why he did it like this, he said "it was more flexible this way".

[Sutherlands]

Didn't want to use Entity Framework for some reason?

[snoofle]

 Already knowing the answer, I HAVE to ask: was there something else he was doing (beyond what was posted) where he needed different subsets of the whole list for each different task?

[pkmnfrk]

@Sutherlands said:

Didn't want to use Entity Framework for some reason?

Don't even start. We've gone through several ORM systems in the last few years.

@snoofle said:

 Already knowing the answer, I HAVE to ask: was there something else he was doing (beyond what was posted) where he needed different subsets of the whole list for each different task?

:) Yeah, that would be a good reason. No, that's not what he was doing.

Pressing him for details, I discovered that 1. He assumed that it would affect the query, a-la LINQ-to-SQL, and 2. he likes LINQ a lot better than SQL, and so decided to ignore all the tutorials I wrote on the subject. Or at least the parts where I said "Don't do this, write it in the query instead."

[Xyro]

@pkmnfrk said:

@Sutherlands said:

Didn't want to use Entity Framework for some reason?

Don't even start. We've gone through several ORM systems in the last few years.

As have I, although all from the Java side. I've been less than impressed with all of them. Currently we've settled for straight-up raw JDBC on one active project and Hibernate in another. They each have a lot of pains.

However, I was recently exposed to Entity Framework for .Net and I was blown away by how much nicer it is than Hibernate and its ilk. I have not yet played around with it, so I'm curious as to how it really performs (both computationally and developmentally). Is it just another ORM? Is it the future of all database access? What are your opinions of it and why do you dismiss it so?

[orange_robot]

Am I the only one who saw this..

The CTO at our company has a habit of finding out about a new shiny library or technology

 .. and was surprised when the technology was actually relevant and useful, with TRWTF being a co-worker who didn't understand how to use it?  The "arbitrary inappropriate technology demanded by C-level exec because he thinks it's swish" is so common in IT horror stories that my brain filled in the entire rest of your post before I read it.

Maybe I'm thinking of enterprise XML...

[boomzilla]

@Xyro said:

However, I was recently exposed to Entity Framework for .Net and I was blown away by how much nicer it is than Hibernate and its ilk.

I'm more familiar with Hibernate than I'd like, and I doubt I'll have an opportunity to use anything .Net, but could you be more specific about your comparison of EF and Hibernate?

[pkmnfrk]

@Xyro said:

@pkmnfrk said:

@Sutherlands said:

Didn't want to use Entity Framework for some reason?

Don't even start. We've gone through several ORM systems in the last few years.

As have I, although all from the Java side. I've been less than impressed with all of them. Currently we've settled for straight-up raw JDBC on one active project and Hibernate in another. They each have a lot of pains.

However, I was recently exposed to Entity Framework for .Net and I was blown away by how much nicer it is than Hibernate and its ilk. I have not yet played around with it, so I'm curious as to how it really performs (both computationally and developmentally). Is it just another ORM? Is it the future of all database access? What are your opinions of it and why do you dismiss it so?

It's not that I'm dismissing it, it's that I've never used it, and probably won't get a chance to, now that we're using Dapper.

@orange_robot said:

Am I the only one who saw this..

The CTO at our company has a habit of finding out about a new shiny library or technology

 .. and was surprised when the technology was actually relevant and useful, with TRWTF being a co-worker who didn't understand how to use it?  The "arbitrary inappropriate technology demanded by C-level exec because he thinks it's swish" is so common in IT horror stories that my brain filled in the entire rest of your post before I read it.

Maybe I'm thinking of enterprise XML...

Well, the CTO is a (different) WTF in his own right. He lives in a fantasy land where grep and sed can literally do anything. While I'd like to live in such a land, I unfortunately can't afford to move. To wit:

A project that I'm going on is to port the Model layer of our internal Intranet site from the old ORM system (LLBL Gen, in case someone's used it) to Dapper. A few stats:

• ~650 Tables
• 150 Megs of generated code
• 5 Megs of hand written business logic code
• Takes ~2 minutes to compile all that
• Altogether about 4000 classes (most generated)
• At some point, some one created a table that causes out LLBL generator to crash
• The source code to our LLBL generator is missing (it does not exist in our source control, as far as I can tell, only the binaries)
• Every place where we call an LLBL generated method needs to be updated.
• "Should only take you 40 hours"

[twelve]

Reminds me of what we've got here. Most queries are pulling 3K rows into an untyped DataSet, filtering and sorting it in the code and iterating it over repeatedly all inside of an AJAX call. I'm tasked to document and clean up this mess which we call a Jeffism around here.

[blakeyrat]

I've always been of the opinion that ORMs are basically just for programmers who are irrationally scared shitless of learning SQL and basic database administration.

I have to admit that your first code sample up in the OP might change my mind... it looks pretty slick.

[boomzilla]

@blakeyrat said:

I've always been of the opinion that ORMs are basically just for programmers who are irrationally scared shitless of learning SQL and basic database administration.

My experience with ORM has been with Hibernate. It can be extremely useful and time saving for setting up mappings, etc, and automatically filling in stuff for a class, lazily loading collections and members of a class. There are times when the performance sucks, however, or the autogenerated SQL of the ORM doesn't quite do what you want. Knowledge of SQL is still required to effectively use Hibernate, and they have HQL (Hibernate Query Language) which is similar to SQL. If you're not using HQL and SQL, you've probably got a very simple app, or you're doing it wrong.

[pkmnfrk]

@blakeyrat said:

I've always been of the opinion that ORMs are basically just for programmers who are irrationally scared shitless of learning SQL and basic database administration.

I have to admit that your first code sample up in the OP might change my mind... it looks pretty slick.

Dapper is very slick, thus the name. If your model classes are set up correctly (default constructor, public properties, etc), it can load query results into objects of your choosing, or (assuming .NET 4.0) you can use dynamic objects that have exactly the properties from the results - very useful for joins, etc.

[Xyro]

@boomzilla said:

@Xyro said:

However, I was recently exposed to Entity Framework for .Net and I was blown away by how much nicer it is than Hibernate and its ilk.

I'm more familiar with Hibernate than I'd like, and I doubt I'll have an opportunity to use anything .Net, but could you be more specific about your comparison of EF and Hibernate?

I was shown a quick demonstration of it primary as an exercise in LINQ. This is what I saw: the instructor had a gob of tables and whatnot already defined in his database. He created a new EF project using VS's built in wizardry and basically only had to specify the connection string and a namespace to plop the ORM definitions into. Nothing other than that. After the wizard magic, he was set. No ugly XML config tweaking, no need to manually define join points (or whatever they're called), nor even the obsessive-compulsive impulse to run through the auto-generated code to clean it up. You didn't have to worry about connection pooling, nor really anything at all about the connection - that's what impressed me the most. You just write out a LINQ query and away you go. No fuss, muss, or SQL/HQL/XQL string manipulation (i.e., "puss").

It just looked so easy and pain-free. In Hibernate, I've had to suffer through manually defining how tables need to get joined together, trying (mostly in vain) to use the same POJO for multiple database views, endlessly fidgeting with the connection settings, and BOY do I have problems with its fall-on-its-face threading model. Seriously, sometimes I feel like I'm the only programmer in the world who understands how to run concurrent threads safely. I don't see how third-party libraries consistently get it wrong. Needless to say, I'm not thrilled with Hibernate's method of handing entities traveling around threads while other threads try to update them. I have no idea if EF is any better at this, but I have a hunch it is. (Of course, I'm not too keen on some of the ways the .Net framework handles threading in general -- a singleton ThreadPool? seriously? -- but I'm not an expert enough in .Net to wave my opinion around. Not quite yet anyway.)

The demo raised some obvious questions, like how do you specify database environments, or how do you control the number of connections or pooling parameters; but those weren't really addressed since they weren't the point of the demo. Maybe once you get past the default setup, things get just as hairy as other ORMS, I don't know. I would like to know, however.

[Sutherlands]

@pkmnfrk:

You've just explained every ORM ever. 

My point was that... if you're going to use an ORM, it makes sense to try the one INCLUDED IN THE FRAMEWORK, as opposed to going and finding this brand-fangled-new-thing off in la-la land. (I have no knowledge of dapper, this is not a criticism of it.)  If you needed a message queue in windows, I would expect you to start out with MSMQ, not go find something else you can install.  That's why it surprises me to hear that you have "gone through several ORM systems in the last few years" but haven't used the one that you would expect to start with.

 

Blakeyrat: ORM is pretty cool, depending on what your architecture is like.  I used NHibernate at my last job and liked it.  Being at my current job I wish I could rewrite it using an ORM, it would make it so much easier.

[pkmnfrk]

@Sutherlands said:

@pkmnfrk:

You've just explained every ORM ever. 

My point was that... if you're going to use an ORM, it makes sense to try the one INCLUDED IN THE FRAMEWORK, as opposed to going and finding this brand-fangled-new-thing off in la-la land. (I have no knowledge of dapper, this is not a criticism of it.)  If you needed a message queue in windows, I would expect you to start out with MSMQ, not go find something else you can install.  That's why it surprises me to hear that you have "gone through several ORM systems in the last few years" but haven't used the one that you would expect to start with.

Can't disagree. I probably should have mentioned, though, that I only started here eight months ago, which is less than "several years", so I had very little input in the decision.

Part of the problem is that we use other inhouse software in lieu of the proper Microsoft versions. ASP.Net? No, we use a custom view processing language thing. XML? Custom processor. Standard collections? Nope, meet the StringArray. ORM? What's one more thing on the pile.

Of course, everyone here is well aware what a load of horse crap this is, so we're moving towards more standard solutions in the future.

(However, I still don't have any experience with Entity Framework since this morning when I posted this thread. How does it handle joins?)

[Sutherlands]

@pkmnfrk said:

(However, I still don't have any experience with Entity Framework since this morning when I posted this thread. How does it handle joins?)

I don't know, myself.  I was originally expecting to hear some reasons why you guys didn't use it.  It doesn't sound like Xyro knows that answer, either, and he might be the most familiar with it.

[Xyro]

@Sutherlands said:

@pkmnfrk said:

(However, I still don't have any experience with Entity Framework since this morning when I posted this thread. How does it handle joins?)

I don't know, myself.  I was originally expecting to hear some reasons why you guys didn't use it.  It doesn't sound like Xyro knows that answer, either, and he might be the most familiar with it.

Well, I know there's basic join on syntax in LINQ that works the way you expect, as far as I can tell.

Here's the first result of my Google: [url]http://msdn.microsoft.com/en-us/library/bb896266.aspx[/url]. (It also comes in this OO-like syntax: [url]http://msdn.microsoft.com/en-us/library/bb534644.aspx[/url].) (...MSDN URLs are pretty horrid.)

Interesting things to note: those are complete examples in that no connection resources need be explicitly allocated, and the AdventureWorksEntities class is completely auto-generated without any frustration. (...As far as I know.) So basically as soon as you point the wizard to the database, you're completely ready to go.

ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;
var query =

from order in orders

join detail in details

on order.SalesOrderID equals detail.SalesOrderID

into orderGroup

select new

{

CustomerID = order.SalesOrderID,

OrderCount = orderGroup.Count()

};

That's pretty damn slick.

[da_Doctah]

@pkmnfrk said:

The difference between this sample and the sample above is that this one pulls the entire table, and filters it and sorts it in code, rather than doing it in the database.

When I asked him why he did it like this, he said "it was more flexible this way".

 

Forgive my Primal Duh, but aren't "efficiency" and "flexibility" two of the five fundamental features among which you can trade off one for another?

 

[serguey123]

@Xyro said:

@Sutherlands said:

@pkmnfrk said:

(However, I still don't have any experience with Entity Framework since this morning when I posted this thread. How does it handle joins?)

I don't know, myself.  I was originally expecting to hear some reasons why you guys didn't use it.  It doesn't sound like Xyro knows that answer, either, and he might be the most familiar with it.

Well, I know there's basic join on syntax in LINQ that works the way you expect, as far as I can tell.

Here's the first result of my Google: http://msdn.microsoft.com/en-us/library/bb896266.aspx. (It also comes in this OO-like syntax: http://msdn.microsoft.com/en-us/library/bb534644.aspx.) (...MSDN URLs are pretty horrid.)

Interesting things to note: those are complete examples in that no connection resources need be explicitly allocated, and the AdventureWorksEntities class is completely auto-generated without any frustration. (...As far as I know.) So basically as soon as you point the wizard to the database, you're completely ready to go.

ObjectSet orders = context.SalesOrderHeaders;
ObjectSet details = context.SalesOrderDetails;
var query =

from order in orders

join detail in details

on order.SalesOrderID equals detail.SalesOrderID

into orderGroup

select new

{

CustomerID = order.SalesOrderID,

OrderCount = orderGroup.Count()

};

That's pretty damn slick.

 

Indeed, I tinkered a lot with LINQ to SQL (hardly an expert) and it can do some interesting stuff.

However for more complex queries it can degenerate a lot so you need to be really careful, also as you add a new layer is not as fast as raw sql but for the most part IMHO it is worth it.

There are some articles about how to make joins and you can see the generated sql to check it for performance.

http://msdn.microsoft.com/en-us/library/bb397908.aspx //standar join operation, you can check how to make other kinds of joins here as well

http://msdn.microsoft.com/en-us/library/cc853327.aspx //this you should read

http://www.codeproject.com/KB/database/Improve-Entity-Framework.aspx //I have not read this yet so I can't vouch for it

[pkmnfrk]

@da Doctah said:

@pkmnfrk said:

The difference between this sample and the sample above is that this one pulls the entire table, and filters it and sorts it in code, rather than doing it in the database.

When I asked him why he did it like this, he said "it was more flexible this way".

 

Forgive my Primal Duh, but aren't "efficiency" and "flexibility" two of the five fundamental features among which you can trade off one for another?

 

Yes, and in the strictest sense, he's correct that it's more flexible. However, he wasn't doing anything in C# that could not be done in SQL, and you really want to be doing filtering in the database when at all possible, not in the application.

If he needed to filter based on the results of a method call, that would be different. But, it was just a simple select.

[sh_code]

@pkmnfrk said:

foreach(var u in list.Where(u => u.Salary < 40000).OrderBy(u => u.Salary) ) {

would the LINQ result be cached, or computed on every iteration? the later was first reason that came to my mind, as i thought LINQ is supposed to be fast. probably not as fast as SQL, but nevetheless... or am i wrong?

[Timm]

In this case the LINQ part does a search and a sort in a giant list of objects. This is never as fast as doing it in SQL. Linq-to-SQL and EF can translate these kind of query's to SQL so it will be done in the database. The OP's query looks something like this with EF:

BlablaContext context = new BlablaContext();
IQueryable<Users> users = context.Users.Where(u => u.Salary < 40000).OrderBy(u => u.Salary);
foreach (var u in users)
{
//This is the moment the database query is executed
}

I use EF a lot. Setting it up in your application is very easy as someone asked. It works great in simple situations, but there is definately a limit in what it can do. We found the limit when we tried to get all order for the last 30 days out of the database of a big online shop. Added a lot of where clauses and grouping and sorting and it turned into a monster that took 5 minutes to run. My SP did it in 15 seconds..

TLDR: EF is cool but can't do everything you ever wanted in life..

[Weng]

@blakeyrat said:

I've always been of the opinion that ORMs are basically just for programmers who are irrationally scared shitless of learning SQL and basic database administration.

I have to admit that your first code sample up in the OP might change my mind... it looks pretty slick.

I'm in the same boat. I'm going to try it on my next hobby project.

[CS1]

@pkmnfrk said:

"Should only take you 40 hours"

So about 3 weeks then?

[Xyro]

@Timm said:

TLDR: EF is cool but can't do everything you ever wanted in life..

Tell me more about this. And are there straightforward ways to bypass EF while still using its features if need be, or does that create more problems like on some ORMs? Or is the way to bypass it just to use raw ADO.NET (which imo is as bad as JBDC if not worse)? I suppose if Dapper exists, then there isn't a feature in EF that basically allows you to do what the OP did? I would like to know more about your experience here.

The thing I hate about ORMs the most is that, when you're in the middle of your project and realize your chosen ORM isn't meeting expectations, it's really difficult to rip it out of the project and replace it with something better -- be it lower-level access or a different ORM or whatever. That is, I've never met an ORM that didn't require really tight coupling in order to properly use. (Imo, the Java Persistence API still leaks waaay too many vendor-specific things into client code.)

[moz]

 What does EF gain you over calling a parameterised query on a DB connection and pulling the results out? Apart from a new pile of syntax to learn, that is?

[serguey123]

@Xyro said:

Tell me more about this. And are there straightforward ways to bypass EF while still using its features if need be, or does that create more problems like on some ORMs? Or is the way to bypass it just to use raw ADO.NET (which imo is as bad as JBDC if not worse)? I suppose if Dapper exists, then there isn't a feature in EF that basically allows you to do what the OP did? I would like to know more about your experience here.

The thing I hate about ORMs the most is that, when you're in the middle of your project and realize your chosen ORM isn't meeting expectations, it's really difficult to rip it out of the project and replace it with something better -- be it lower-level access or a different ORM or whatever. That is, I've never met an ORM that didn't require really tight coupling in order to properly use. (Imo, the Java Persistence API still leaks waaay too many vendor-specific things into client code.)

You can just use plain sql if you are inclined to do so

dotnet-bot

DataContext.ExecuteQuery Method (System.Data.Linq)

Executes SQL queries directly on the database.

@__moz said:

 

What does EF gain you over calling a parameterised query on a DB connection and pulling the results out? Apart from a new pile of syntax to learn, that is?

Read about it and decide for yourself, EF can be handy in some situations.

@Timm said:

I use EF a lot. Setting it up in your application is very easy as someone asked. It works great in simple situations, but there is definately a limit in what it can do. We found the limit when we tried to get all order for the last 30 days out of the database of a big online shop. Added a lot of where clauses and grouping and sorting and it turned into a monster that took 5 minutes to run. My SP did it in 15 seconds..

This result could probably be improved but adding a new layer will always degrade performance a bit, everything is a tradeoff

@SEMI-HYBRID code said:

 

would the LINQ result be cached, or computed on every iteration? the later was first reason that came to my mind, as i thought LINQ is supposed to be fast. probably not as fast as SQL, but nevetheless... or am i wrong?

The result are cached, there is only one trip to the db, unless that is not what you want of course.

[Timm]

@Xyro said:

Tell me more about this. And are there straightforward ways to bypass EF while still using its features if need be, or does that create more problems like on some ORMs? Or is the way to bypass it just to use raw ADO.NET (which imo is as bad as JBDC if not worse)? I suppose if Dapper exists, then there isn't a feature in EF that basically allows you to do what the OP did? I would like to know more about your experience here.

Depends on what you want to do. You can easily bypass a very complex LINQ query by creating an SP and mapping that to the EF model. That and LINQ which generates decent SQL for 95% of the time will be good enough for a lot of applications.



What i dont really understand myself is that they seem to actively try to make it a cool gimmick..

Example: http://blogs.msdn.com/b/adonet/archive/2011/03/15/ef-4-1-code-first-walkthrough.aspx
I tried it and its really superawsome when you are hacking a simple website or fast demo but big projects just dont work this way..

#edit: have been a lurker so far and you need to type in HTML to post anything with layout, or am I just blind?

[Timm]

@serguey123 said:

This result could probably be improved but adding a new layer will always degrade performance a bit, everything is a tradeoff

Agreed, but the query that came out of EF was so bad that it deadlocked our orders database which is also used to store new orders active users on the website are making. A little to much tradeoff. :)

[serguey123]

@Timm said:

@serguey123 said:

This result could probably be improved but adding a new layer will always degrade performance a bit, everything is a tradeoff

Agreed, but the query that came out of EF was so bad that it deadlocked our orders database which is also used to store new orders active users on the website are making. A little to much tradeoff. :)

Yeah, you have to be really careful when creating the query because sometimes linq produces awful sql, some expresion are poorly optimized but with a bit of logic (dust of your De Morgan law) it can be optimized. However I agree that no matter what software you use, knowing SQL and how databases work is a good idea and something every good programmer needs to know.

[pjt33]

@serguey123 said:

However I agree that no matter what software you use, knowing SQL and how databases work is a good idea and something every good programmer needs to know.

Can you please qualify that as "every good programmer who works with relational databases"? People who work on embedded software, device drivers, games, etc. have no need to know SQL.

[serguey123]

@pjt33 said:

@serguey123 said:

However I agree that no matter what software you use, knowing SQL and how databases work is a good idea and something every good programmer needs to know.

Can you please qualify that as "every good programmer who works with relational databases"? People who work on embedded software, device drivers, games, etc. have no need to know SQL.

I though that this was clear on context, I was discussing E.F. and implying that good programmers that use ORM should also know SQL, but again for the  context-deficient pedantic dickeweeds general userbase of this fora, knowing SQL even if you don't use it will not harm you, it might not be a skill needed on you current trade at this time but hey, the future is unknown.  It is a good skill to have even if its never going to be more than a bullet point in your CV IMO. 

[pkmnfrk]

@serguey123 said:

@pjt33 said:

@serguey123 said:

However I agree that no matter what software you use, knowing SQL and how databases work is a good idea and something every good programmer needs to know.

Can you please qualify that as "every good programmer who works with relational databases"? People who work on embedded software, device drivers, games, etc. have no need to know SQL.

I though that this was clear on context, I was discussing E.F. and implying that good programmers that use ORM should also know SQL, but again for the  context-deficient pedantic dickeweeds general userbase of this fora, knowing SQL even if you don't use it will not harm you, it might not be a skill needed on you current trade at this time but hey, the future is unknown.  It is a good skill to have even if its never going to be more than a bullet point in your CV IMO. 

Since it's not clear from context:

• E.F. = Entity Framework, a .NET ORM
• ORM = Object Relation Mapping, a means of representing database records as objects
• SQL = A language typically used to query databases and extract relevant data
• CV = Curriculum vitae, a record of your work experience, skills, and other thing s that an employer may want to know about you to help decide whether you are a good fit for a job. Also known as a resume.
• Job = A position working at a company, typically exchanging labour for money.
• Programmer = a person who is employed to write software to be executed by a computer, generally to make the computer do something useful (however, exceptions to this have been noted)

You need to write to your audience, serguey123.

[Sutherlands]

Lol

[PJH]

@pjt33 said:

People who work on embedded software, [....] have no need
to know SQL.[citation needed]

I do. Embedded stuff talks to servers - that use databases.

[veggen]

@Xyro said:

@pkmnfrk said:

@Sutherlands said:

Didn't want to use Entity Framework for some reason?

Don't even start. We've gone through several ORM systems in the last few years.

As have I, although all from the Java side. I've been less than impressed with all of them. Currently we've settled for straight-up raw JDBC on one active project and Hibernate in another. They each have a lot of pains.

However, I was recently exposed to Entity Framework for .Net and I was blown away by how much nicer it is than Hibernate and its ilk. I have not yet played around with it, so I'm curious as to how it really performs (both computationally and developmentally). Is it just another ORM? Is it the future of all database access? What are your opinions of it and why do you dismiss it so?

I must notice it sounds like you didn't use any of the available tooling for Hibernate. With that, you get the same "specify the connection and the package name only" approach. But anyway, if you don't like Hibernate or raw JDBC, try Siene, ActiveJDBC or even iBatis (is that thing still alive?). All are different twists on the "in between" type.

On the other hand, I can't say much about EF except that the people around me that had used it no longer do, as they say it runs like a dog.