Generic parsing of Expressiontree to SQL

Question

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.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?

for a thousand different reasons, but one crucial problem is that i won't know the table and column names before at runtime so there is no way to create a dbml schema to pass to Linq2Sql — Pauli Østerø
– Pauli Østerø, Commented Dec 14, 2010 at 20:38
its obvious you don't see the the point because there is no point to see. I'm not asking how to solve my specific domain problem, since that is working quite fine and i'm happily using expression trees for simple stuff like OrderBy methods. For something like Where i'm a bit nervous though since they can get arbitrary complex, and re-inventing the wheel for parsing into sql seems silly since most if it is so generic anyway. — Pauli Østerø
– Pauli Østerø, Commented Dec 14, 2010 at 20:45
@Pauli Østerø: It is still possible to use LINQ to SQL even if you don't know the table and column names at runtime. I designed and developed a dynamic report generator using expression trees created at runtime that I let LINQ to SQL parse to SQL for me. It works and has been in production for close to two years. — jason
– jason, Commented Dec 14, 2010 at 20:51
maybe i should look into it... i always thought Linq2Sql being a bit of overdoing it when all i actually need is to take a Where expression and turn it into Sql. I don't allow anything like joins, projection or that advanced stuff anyway, just Where :) — Pauli Østerø
– Pauli Østerø, Commented Dec 14, 2010 at 21:04

Pauli Østerø · Accepted Answer · 2010-12-15 02:42:21Z

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());
}

Damian · Accepted Answer · 2010-12-14 20:45:32Z

0

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.

answered Dec 14, 2010 at 20:45

Damian

2,8092 gold badges30 silver badges42 bronze badges

1 Comment

Pauli Østerø Over a year ago

actually i know about the IQueryable Toolkit and i have been looking at the SqlFormatter class that should be able to emit sql from a Expression. Maybe i should just get the source and remove all the dependencies it has to the ORM facilites and use it in my own code.

Collectives™ on Stack Overflow

Generic parsing of Expressiontree to SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related