How to write a simple ADO.NET ORM by using Linq and extension methods (in 10 minutes) | The Coffeehouse

littleguru
<3 Seattle

Extension methods, Linq and Lambda Expressions are just great. I have written a short article on how to create a simple "ORM" by extending the existing ADO.NET classes...

What do you gals/guys think?

http://www.liensberger.it/web/blog/?p=15

TommyCarlier
I want my scalps!

The article is nice and really shows off the features of C# 3.0 and LINQ, but I have a feeling you're trying to use all the features, even if there are better ways to do things.

Here's an example that I feel is not good:

var result = from u in connection.GetObjects<User>("select * from [USERS]")
    where u.Name == "Alex" || u.Name == "Christian"
    orderby u.Name ascending
    select u;

This will select all records on the server and send them to the client, where the client will filter them. This is a horrible thing to do. The filtering should definitely happen in the database, like this:

var result = from u in connection.GetObjects<User>(
    @"SELECT * FROM [Users]
      WHERE Name IN ('Alex', 'Christian')
      ORDER BY Name ASCENDING")
    select u;

littleguru
<3 Seattle

It's true... I wanted to show what is possible. You could do a powerful query to get already sorted results from the database.

Next you could allow people to filter stuff in memory... A second filtering or ordering etc.

The current approach allows you to use the new Linq features in combination with the power of SQL. It's just a simple demonstration on what is possible Smiley

Chadk
excuse me - do you has a flavor?

TommyCarlier wrote:
The article is nice and really shows off the features of C# 3.0 and LINQ, but I have a feeling you're trying to use all the features, even if there are better ways to do things.

Here's an example that I feel is not good:
var result = from u in connection.GetObjects<User>("select * from [USERS]")
    where u.Name == "Alex" || u.Name == "Christian"
    orderby u.Name ascending
    select u;
This will select all records on the server and send them to the client, where the client will filter them. This is a horrible thing to do. The filtering should definitely happen in the database, like this:
var result = from u in connection.GetObjects<User>(
    @"SELECT * FROM [Users]
      WHERE Name IN ('Alex', 'Christian')
      ORDER BY Name ASCENDING")
    select u;

While i get your point, the first example just looks alot nicer to me code wise.

It's surely better to filter on the server rather then the client. But how big a difference does it make performance wise?

Sven Groot
My name has 9 letters. Coincidence? I think not...

Chadk wrote:
It's surely better to filter on the server rather then the client. But how big a difference does it make performance wise?

Depends. If there's three users named Alex or Christian, and 5,000,000,000 users with other names, the difference would be enormous, in network bandwidth usage alone. And if the DB server has an index on the Name column, the difference would be even bigger!

littleguru
<3 Seattle

Chadk wrote:
While i get your point, the first example just looks alot nicer to me code wise.

It's surely better to filter on the server rather then the client. But how big a difference does it make performance wise?

It's also possible to convert a Linq expression to SQL. I could cover that in a future post.

Databases hold indices and build trees from the data that is inserted. They are fast in searching and filtering data, because of the indices and trees...

Sven Groot
My name has 9 letters. Coincidence? I think not...

Having played a little with the LINQ to SQL O/R designer, I think you're reinventing the wheel here. Especially with those attributes; there's already a TableAttribute and a ColumnAttribute. And contrary to your example, when using LINQ to SQL the LINQ expressions will get translated to SQL and run against the DB server.

I have the suspicious feeling that this LINQ to SQL stuff is going to replace my home-grown ORM once it comes out. Big Smile

Plus you can get LINQ to SQL to use stored procedures for its default update/insert/select operations by just dragging them from the server explorer to the O/R designer. Ultra-cool. Cool

SecretSoftware
Code to live, but Live to code.

Cool.

Sven Groot
My name has 9 letters. Coincidence? I think not...

SecretSoftware wrote:
but the auto-generated myDBContext class, is a bit not easy to read.

Since when are you supposed to read auto-generated code? If a code generator tool does it job well, you should never have to look at the generated code.

SecretSoftware
Code to live, but Live to code.

Sven Groot wrote:

SecretSoftware wrote:but the auto-generated myDBContext class, is a bit not easy to read.

Since when are you supposed to read auto-generated code? If a code generator tool does it job well, you should never have to look at the generated code.

Because in win forms, I used to write the auto-gen code, instead of using the designer.

You are supposed to read them, to understand the implementation of things. Its not good to just use with out understanding of what goes on. Wink

SecretSoftware
Code to live, but Live to code.

Sven Groot wrote:

Having played a little with the LINQ to SQL O/R designer, I think you're reinventing the wheel here. Especially with those attributes; there's already a TableAttribute and a ColumnAttribute. And contrary to your example, when using LINQ to SQL the LINQ expressions will get translated to SQL and run against the DB server.

I have the suspicious feeling that this LINQ to SQL stuff is going to replace my home-grown ORM once it comes out.

Plus you can get LINQ to SQL to use stored procedures for its default update/insert/select operations by just dragging them from the server explorer to the O/R designer. Ultra-cool.

but the auto-generated myDBContext class, is a bit not easy to read.

At least they don't have usings on top of the namespace, and they use the fully qualified assembly names.

Also, no regions, no organization, and no exception handling to sql. (like errors with connection strings etc etc..).

littleguru
<3 Seattle

Sven Groot wrote:
Having played a little with the LINQ to SQL O/R designer, I think you're reinventing the wheel here. Especially with those attributes; there's already a TableAttribute and a ColumnAttribute. And contrary to your example, when using LINQ to SQL the LINQ expressions will get translated to SQL and run against the DB server.

I have the suspicious feeling that this LINQ to SQL stuff is going to replace my home-grown ORM once it comes out.

Plus you can get LINQ to SQL to use stored procedures for its default update/insert/select operations by just dragging them from the server explorer to the O/R designer. Ultra-cool.

Isn't DLinq or "Linq to Entities" not going to be cut in the final version? I thought it's going to be removed in the final version and coming some time later?

SecretSoftware
Code to live, but Live to code.

littleguru wrote:

Sven Groot wrote:Having played a little with the LINQ to SQL O/R designer, I think you're reinventing the wheel here. Especially with those attributes; there's already a TableAttribute and a ColumnAttribute. And contrary to your example, when using LINQ to SQL the LINQ expressions will get translated to SQL and run against the DB server.

I have the suspicious feeling that this LINQ to SQL stuff is going to replace my home-grown ORM once it comes out.

Plus you can get LINQ to SQL to use stored procedures for its default update/insert/select operations by just dragging them from the server explorer to the O/R designer. Ultra-cool.

Isn't DLinq or "Linq to Entities" not going to be cut in the final version? I thought it's going to be removed in the final version and coming some time later?

Its not going to make it, it will ship after Orcas is Gold. At least thats what Andres said in his LINQ Presentation at MIX 07

littleguru
<3 Seattle

SecretSoftware wrote:
Its not going to make it, it will ship after Orcas is Gold. At least thats what Andres said in his LINQ Presentation at MIX 07

ADO.NET Entities Framework == DLINQ?

It looks similar, but doesn't seem to be the same... or is it the same, just renamed to a better name?

Found answer: http://msdn2.microsoft.com/en-us/library/aa697427(vs.80).aspx - last paragraph says that Linq to SQL is a direct thing, where ADO.NET Entity Framework adds an abstraction layer. Smiley

SecretSoftware
Code to live, but Live to code.

littleguru wrote:

SecretSoftware wrote:Its not going to make it, it will ship after Orcas is Gold. At least thats what Andres said in his LINQ Presentation at MIX 07

ADO.NET Entities Framework == DLINQ?

It looks similar, but doesn't seem to be the same... or is it the same, just renamed to a better name?

Found answer: http://msdn2.microsoft.com/en-us/library/aa697427(vs.80).aspx - last paragraph says that Linq to SQL is a direct thing, where ADO.NET Entity Framework adds an abstraction layer.

DLinq is renamed to LINQ TO SQL (Database LINQ).

On the whole, I wished if I had LINQ with VS 2005. Because Now there are whole classes that are just auto-generated, when it took days to test and debug and get it running LOL. And now they get generated in a click.

Amazing.

JohnAskew
9 girl in pink sweater

Excellent article, littleguru.

Thanks.

johndauphine

Great article. Really elagant code. I will try this approch to cut down on code in one of my projects.