Sign in to follow this  

[.net] LINQ

This topic is 3151 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey folks, I am trying to write a sort of SQL wrapper and I would like to use LINQ with it. Sadly I almost know nothing about LINQ, which is why I ask for your help ;) I would like to write LINQ syntax and "transform" it into SQL syntax. Something like this: String sql = Transform(from t in l select t); Where the Transform function returns a string containing the SQL syntax. Is there a way to "deveiver" the "from t in l select t", so that I can figure out what the appropriate SQL syntax would look like? Maybe all this does not make any sence at all. I'd appreciate any help, link suggestion!! Sorry for my bad english cherio Woltan

Share this post


Link to post
Share on other sites
I'm not sure that is possible. LINQ gets compiled to method calls and the original syntax is basically lost. .NET 3.5 lets you work with parse trees for some constructs, but I don't think it would work for a complete LINQ query.

The real question is, what do you want to do that for? If all you're trying to do is integrate LINQ into some other database engine, or some other backend, then you're much better off building a new ADO.NET Entity Framework backend.

Share this post


Link to post
Share on other sites
Converting expression trees into SQL is entirely possible. In fact, that's what LINQ to SQL does. If you're using LINQ to SQL, you can retrieve the underlying SQL using the System.Data.Linq.DataContext.GetCommand method. Here's an example.

There are also many other providers that convert from expression trees into other formats. And for more information on expression trees themselves, see this article: Expression Tree Basics.

Share this post


Link to post
Share on other sites
Well, there you go. Still, I'm not sure this is the best solution. Woltan: can you show us an example of what you're trying to do, exactly?

Share this post


Link to post
Share on other sites
Wow,
thank you very much for those replies. I think I have a lot of reading to do. But to answer your question Codeka, what I am trying to do:
I am trying to access a postgreSQL DB with the Npgsql data provider. I would like to use LINQ to form my SQL expressions. So that only my wrapper is using SQL and accesses the DB with Npgsql.
For that I think I need to read out the table structures etc. in advance in order to let the compiler know what the LINQ query is built against.

So this is the way I think I have to take before reading the links above^^

1. Export the Table structures into classes, so that the compiler knows the datatypes I want to build my LINQ query on
2. Write some Functions that can connect to the DB and translate the LINQ queries to SQL syntax in order to use the Npgsql data provider.
0. Do a lot of reading first.

I hope my posting was not too confusing. If you have a suggestion how I should build my wrapper, please let me know!!

Best regards and thx again
cherio Woltan

Share this post


Link to post
Share on other sites
I've written my own expression tree-to-SQL generator and it is not trivial. If possible I recommend switching to MS SQL and using either LINQ to SQL or ADO.NET Entity Framework. Or try to find a free LINQ provider for PostgreSQL.

A quick Google search found this: http://code.google.com/p/dblinq2007/. I'm sure there are others.

Share this post


Link to post
Share on other sites
You can use the Log-property on the datacontext to retrieve the generated sql, however this requires the query to be run.

Out of my head (untested code):

MyDataContext.Log = new System.IO.StringBuilder();

var Result = from X in MyDataContext.Y where Z select W;

//Do something with Result

var SqlString = MyDataContext.Log.ToString();

Share this post


Link to post
Share on other sites
Quote:
Original post by mutex
I've written my own expression tree-to-SQL generator and it is not trivial. If possible I recommend switching to MS SQL and using either LINQ to SQL or ADO.NET Entity Framework. Or try to find a free LINQ provider for PostgreSQL.

A quick Google search found this: http://code.google.com/p/dblinq2007/. I'm sure there are others.


I think that LINQ to SQL has been deprecated in favor of LINQ to Entities. I'd start with that.

I've not had very good luck with getting LINQ to SQL or LINQ to Entities to work for me. Early in a project it seems like a godsend, but eventually it just gets in my way.

Share this post


Link to post
Share on other sites
Heyas,
first of all a big thx for all the posting. I read a lot of articles today and I think I do now know how I will procede.
I am going to use LINQ to SQL. In VS there is a LINQ to sql class file, which is based on an xml file that holds all the tables and column information of the database. I am going to write a tool, that exports the table data into that xml file and then use a dynamic LINQ call to write a query on those tables. Then I am going to parse the expression tree generated by that query and transform it into a sql query.

Right now I have three remaining problems:
1. The expression tree is not that trivial to parse and transform into an sql statement
2. I am not sure how to include stored procedures
3. I read an article that LINQ to sql is going to be replaced with LINQ to entety. However, in order to use LINQ to entety I need an "ADO database integreation", which is currently not possible with postgresql.

If you have an idea on how I could impove my approach please let me know.
Thx again for your help!!
cherio Woltan

Share this post


Link to post
Share on other sites

This topic is 3151 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this