Using ADO.NET Entities LINQ Provider to model complex SQL Queries?

Question

What I find really powerful in ADO.NET Entities or LINQ to SQL, is the ability to model complex queries. I really don't need the mappings that Entities or LINQ to SQL are doing for me - I just need the ability to model complex expressions that can be translated into T-SQL.

My question is - am I abusing too much? Can I use the Entity Framework for modeling queries and just that? Should I? I know I can write my own custom LINQ to SQL provider, but that is just not possible to handle in the time spans I have.

What is the best approach to model complex T-SQL queries? How do you handle conditional group byes, orders, joins, unions etc in the OOP world? Using StringBuilders for this kind of job feels too ugly and harder to maintain given the possibilities we have with Expression Trees.

When I use StringBuilder to model a complex SQL Query I feel kind of guilty! I feel the same way as when I have to hard code any number into my code that is different than 0 or 1. Feeling that makes you ask yourself if there is a better and cleaner way of doing it...

I must mention that I am using C# 4.0, but I am not specifically looking for an answer in this language, but rather in the domain of CLR 4. Are there any real enterprise libraries that can help with constructing complex SQL at runtime?

Answer 1

If you find it easier (not to mention less error prone) to compose a query in LINQ rather than T-SQL, then why not?

There is no law written that says you can't use EF or L2S to help you write T-SQL, and then just go off and execute the T-SQL directly yourself.

In fact I think this makes a lot of sense.

Don't feel guilty!