Is LinqToSQL powerful enough? Isn't a more powerful but equally fluent interface easy to construct?

Answer 1

I think it is overkill, both LinqToSQL and LinqToEntities. I took a minimalistic approach. I developed a DAL that uses a DTO and reflects on its properties to generate SQL on the fly. It works for 95% of the cases where I don't need and dont' want to think about SQL. For all the other cases my DAL allows me to write any SQL I want and returns either a DataSet or if given a DTO it will populate it with the results. It also allows to call stored procedures with the same concept, returning DataSet or DTO's. It works pretty well for me and I don't need to see any SQL if I don't want to.

Answer 2

linq2sql has built in paging support and is strongly typed.

You can avoid using the entity tracking if you won't use it.

You can call the sql functions, by wrapping it into a method.

If you use entity tracking, you can easily batch several data manipulation calls (does so automatically, thus the SubmitChanges call). So you don't hit the database 15 times to do 5 inserts, and 10 updates.

It also comes handy when you want to get structured information, like:

var someInfo = from s in Context.Somethings
               select new SomeInfo
               {
                   AField = s.SomeColumn,
                   RelatedStuff = s.RelatedInfo.Where(someconditionhere)
               };

I would say it is more than worth it. Also note if you are really needing to get the most performance, you can compile expressions, and it will match datareader speed.

Answer 3

At the moment, I'm not a fan of Entity Framework, but 4.0 looks much improved. I'm not sure is it as big a change as Vista vs Windows 7, but definitely better.

For now, I find LINQ-to-SQL covers most things I regularly need. Heck, even if I just use it to connect stored procedures without having to write the parameter code, that is to my advantage. I find the CRUD stuff is great for simple orthogonal data manipulation. For more complex scenarios, I like UDFs etc - which LINQ-to-SQL supports fine.

As long as you hide it in a repository, you aren't even painting yourself into a corner.

Re datediff - have you looked at SqlMethods?

Answer 4

What you are proposing with your query class is that you still embed SQL directly in the application code. What you get with the LINQ + ORM solution is that the application code never contains SQL. You still have a mismatch and a distraction from working on the code. You mention it in your post:

We can test it out in a SQL window and then just cut and paste into our code, breaking it up as appropriate to enter our parameters. It couldn't be easier.

What the LINQ syntax is trying to give us is queries as first class citizens in the code. Yep some stuff is clumsy, some stuff might not be quite right yet, but the stated goal of LINQ is to integrate query and application code to the point where there isn't a disjoint. It sounds like that isn't what you are after, so maybe embedding SQL as string literals that are opaque to the compiler and runtime will work better for you. We know it can be made to work, we've done it for years.
The other benefit is that the LINQ statement will not be subject to vendor specific variations (that's the LINQ provider's problem). You could end up with the SQL string that you pass to qry.Command() being tied to a particular database.

Answer 5

So, isn't it better to simply embed SQL in C# (or VB) in ways that permit you to freely state your SQL but that also give you easy parameter embedding and automatic conversion to native classes?

What I usually do comes very close to this. I write the SQL in a stored procedure, so I get the freedom of real SQL. In C# I use Linq's DBML to access the stored procedures in a natural way. This feels like the best of both worlds.

The real solution looks even simpler to me: embed SQL in C# code and have the compiler generate the strong classes. If Visual Studio supported that, I doubt anyone would be using anything else.

(There' always people trying to create Entity Frameworks and "architecture" away the need for SQL. As far as I know, this has never really worked, if only because the result is dramatically harder to maintain and dead slow.)

Answer 6

var q = DataContext.ExecuteQuery<Etc>("SELECT * FROM etc") usually does a pretty good job.