Generic parsing of Expressiontree to SQL

So, im having this very simple linq provider that does stuff with objects in a small constrained domain. No need for a full blown Linq2Sql implementation, but some of the stuff i need to do involves sql queries. My thougt was that, if you look away from table and column names, most expression tree => sql must be very (very) generic.

Expression<Func<DateTime, bool>> expr = d => d开发者_Python百科.DateTime.Month == 1

will always translate to

DATEPART({0}, m) = 1

or

Expression<Func<DateTime?, bool>> expr = d => d.HasValue || d ==  DateTime.Now

into

{0} IS NOT NULL OR {0} = NOW()

hope you get the point. Is there any generic sql generators like this out there?

---

hehe, dont we just looove hacking around? So, its not FULLY working yet, i need to figure out how to dynamically inject some mapping information, but so far it looks promising.

I've been able to produce this sql

SELECT t0.[Born], t0.[Yo]
FROM [Heies] AS t0
WHERE ((MONTH(t0.[Born]) = 1) OR (NOT (t0.[Yo] IS NULL OR t0.[Yo] = '') AND (t0.
[Born] = @p0)))

with this code

var provider = new DbEntityProvider(new SqlConnection(), new TSqlLanguage(), new ImplicitMapping(), new QueryPolicy());
var exp = provider.GetTable<Hey>().Where(d => d.Born.Month == 1 || (!String.IsNullOrEmpty(d.Yo) && d.Born == DateTime.Now)).Expression;
var sql = ((QueryProvider)provider).GetQueryText(exp);

using the IQToolkit, so thanks to Damian for reminding me about it again! If anyone has a similar way to produce sql via Linq2Sql i would love to hear from you so i could loose the dependency on IQToolkit.

Update

After a lot of fooling around i have hit the wall at extracting the values for each of the parameters. After a lot of source reading it seems like its hidden far away for the actual execution of the query. Well, guess what, i don't want the query to be executed :/

After looking into Linq2Sql i came up with this code that does roughly the same, so maybe i should just stick with that

var xml = Generate<NewsProperty>();
var mapping = XmlMappingSource.FromUrl(xml);
var ctx = new DataContext(new SqlConnection("..."), mapping);

var query = ctx.GetTable<NewsProperty>().Where(n => n.Date.Year == 2010).OrderBy(n => n.Date).Take(5);
var cmd = ctx.GetCommand(query);
string sql = cmd.CommandText;

foreach (DbParameter param in cmd.Parameters)
{
   sql = sql.Replace(param.ParameterName, param.Value.ToString());
}

---

You should take a look at the IQueryable Toolkit and its related articles by Matt Warren http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx.