SQL to LINQ Converter
-
iangrech wrote:
Does anyone know of any SQL to LINQ convertors around?
Ummm... Why would you want that? I don't know of any converters. And I am trying to figure out why you would want to do such a thing as there may be another solution.
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Introduction to LINQ to XML (Part 1) - (Part 2) My website | Blog
I am trying to understand LINQ and lambdas. I am trying to write a LINQ statement for SELECT DISTINCT [FieldName] FROM [TableName] ORDER BY [FieldName] where [TableName] is a datatable.
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
-
I am trying to understand LINQ and lambdas. I am trying to write a LINQ statement for SELECT DISTINCT [FieldName] FROM [TableName] ORDER BY [FieldName] where [TableName] is a datatable.
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
First: You can not make it work like you described. You can not make linq operate on "made up" identifiers, the identifiers must exist on an a real class. so "Select distinct Foo" where foo is the name in a datatable wont work. you have to make a property called foo on some sort of object. That applies to both lambdas as delegates and lambdas as expression trees. eg: from row in DS["tablename"] order by row["fieldname"] select row["fieldname"] is valid, but: from row in tablename order by fieldname select fieldname is not.. no matter how much magic you try to do with expression trees. 2nd :) : Ive got a small clone of the Linq to objects engine: http://rogeralsing.com/2008/03/31/linq-to-objects-for-net-2-available/[^] you can always download that and customize it to work for datatables.
Blog: http://www.rogeralsing.com Projects: http://www.puzzleframework.com
-
First: You can not make it work like you described. You can not make linq operate on "made up" identifiers, the identifiers must exist on an a real class. so "Select distinct Foo" where foo is the name in a datatable wont work. you have to make a property called foo on some sort of object. That applies to both lambdas as delegates and lambdas as expression trees. eg: from row in DS["tablename"] order by row["fieldname"] select row["fieldname"] is valid, but: from row in tablename order by fieldname select fieldname is not.. no matter how much magic you try to do with expression trees. 2nd :) : Ive got a small clone of the Linq to objects engine: http://rogeralsing.com/2008/03/31/linq-to-objects-for-net-2-available/[^] you can always download that and customize it to work for datatables.
Blog: http://www.rogeralsing.com Projects: http://www.puzzleframework.com
Hi Roger; Thank you for your reply but maybe I should have explained that what I put in [] is generic, ie:when I say [TableName], in real life [Tablename] will be a real tablename, eg: AccountTransactions Now back to the issue at hand. My problem is recreating this SQL statement as LINQ:
SELECT DISTINCT [fieldname] FROM [tablename] ORDER BY [fieldname]I get as far asSELECT DISTINCT [fieldname] FROM [tablename]which translates to(from u in [tablename] select u.[fieldname]).Distinct()but the result is not sorted. I want to include the ORDER BY. Further to this, my o.p. was a request for a tool where I could write a SQL statement and it outputs the relevant LINQ, even if this is as lambda. I have LINQPad but it only does LINQ to SQL, not SQL to LINQ. Tnx IanNo trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
-
Hi Roger; Thank you for your reply but maybe I should have explained that what I put in [] is generic, ie:when I say [TableName], in real life [Tablename] will be a real tablename, eg: AccountTransactions Now back to the issue at hand. My problem is recreating this SQL statement as LINQ:
SELECT DISTINCT [fieldname] FROM [tablename] ORDER BY [fieldname]I get as far asSELECT DISTINCT [fieldname] FROM [tablename]which translates to(from u in [tablename] select u.[fieldname]).Distinct()but the result is not sorted. I want to include the ORDER BY. Further to this, my o.p. was a request for a tool where I could write a SQL statement and it outputs the relevant LINQ, even if this is as lambda. I have LINQPad but it only does LINQ to SQL, not SQL to LINQ. Tnx IanNo trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
Well, you could do this as:
(from u in ctx.tablename orderby ctx.field select ctx.field).Distinct();
As far as I'm aware, there's no tool that reverses the process, i.e. from Sql to Linq.
Deja View - the feeling that you've seen this post before.
-
Well, you could do this as:
(from u in ctx.tablename orderby ctx.field select ctx.field).Distinct();
As far as I'm aware, there's no tool that reverses the process, i.e. from Sql to Linq.
Deja View - the feeling that you've seen this post before.
-
Tried it already. Output is unsorted.
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
Did you try
orderby ... ascending?Deja View - the feeling that you've seen this post before.
-
Did you try
orderby ... ascending?Deja View - the feeling that you've seen this post before.
-
Yeppp. I even tried typing it while sacrificing a virgin (packet of orange juice).
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
Hmmm. Not sure what to suggest here, as this works OK for me. It might be worth SQL Profiling your app to see what's hitting the database.
Deja View - the feeling that you've seen this post before.
-
Hmmm. Not sure what to suggest here, as this works OK for me. It might be worth SQL Profiling your app to see what's hitting the database.
Deja View - the feeling that you've seen this post before.
Using LINQPad, SQL Profiler shows that only a simple select (SELECT DISTINCT...FROM...) is passed to SQL Server, without the ORDER BY clause. I will try it from within an application later on. Tnx Ian
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
-
Using LINQPad, SQL Profiler shows that only a simple select (SELECT DISTINCT...FROM...) is passed to SQL Server, without the ORDER BY clause. I will try it from within an application later on. Tnx Ian
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
Hmm. Using LinqPAD, I ran the following query and it worked OK until I put the Distinct query in.
(from p in Customers
orderby p.Name
select new {p.ID, p.Name})This produced the following SQL:
SELECT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]
ORDER BY [t0].[Name]As soon as I put the Distinct() in, I get:
SELECT DISTINCT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]which would seem to be a bug.
Deja View - the feeling that you've seen this post before.
-
Hmm. Using LinqPAD, I ran the following query and it worked OK until I put the Distinct query in.
(from p in Customers
orderby p.Name
select new {p.ID, p.Name})This produced the following SQL:
SELECT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]
ORDER BY [t0].[Name]As soon as I put the Distinct() in, I get:
SELECT DISTINCT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]which would seem to be a bug.
Deja View - the feeling that you've seen this post before.
Worked in code. Apparently LINQPad mishandles some queries then. So we agree: LINQPad developer will be tried, found guilty, tortured and executed. :) Tnx for your help.
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
modified on Tuesday, April 29, 2008 9:55 AM
-
Hi All; Does anyone know of any SQL to LINQ convertors around? Tnx Ian
No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced.
SQL to LINQ converter is available at www.sqltolinq.com