none
Generate SQL Statements from LINQ RRS feed

  • Question

  • I am sure this is a stupid question but i will ask it anyway. 

    LINQ will generate the SQL statements when it executes the .submitchanges() method and i know that i can view/generate the SQL statements either from the .log property or from the SQL profiler. 

    Here is my question.

    How can i view SQL statements without submitting the changes. I dont want the changes that were done to the LINQ Objects to be executed on the SQL server but i just want the SQL Statements.

    How can i achieve this ? 


    -ravi

    Thursday, May 31, 2012 2:58 PM

Answers

  • Very simple.  In your app (VB.NET but same for C#) to get the SQL generated simply use a ToString() with your statement.

    For example in my app I have the following Linq to SQL:

    Dim files As List(Of RFolder) = (From f In dataContext.RFolders Select f Order By f.UpdateDate Descending Skip skipNum Take pageSize).ToList

    If I add the line:

    Dim sqlSTR As String = (From f In dataContext.RFolders Select f Order By f.UpdateDate Descending Skip skipNum Take pageSize).ToString

    it returns:

    SELECT [t1].[FolderNumber], [t1].[FolderName], [t1].[UpdateDate], [t1].[PossibleLocationURL], [t1].[ReleaseYear], [t1].[Rating]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UpdateDate] DESC) AS [ROW_NUMBER], [t0].[FolderNumber], [t0].[FolderName], [t0].[UpdateDate], [t0].[PossibleLocationURL], [t0].[ReleaseYear], [t0].[Rating]
        FROM [dbo].[RFolders] AS [t0]
        ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
    ORDER BY [t1].[ROW_NUMBER]

    You do not have to execute and you can use the generated string to log/archive or whatever you want.

    Hope this helps

    Lloyd SHeen


    Lloyd Sheen

    Thursday, May 31, 2012 5:19 PM

All replies

  • I recommend this tool http://www.linqpad.net/.

    Great for anything linq related and free sans the AutoComplete.

    Regards

    Thursday, May 31, 2012 4:30 PM
  • Yep. I'm using it already. i'm trying to retrieve the SQL Statements in the Application. I'm not experimenting with LINQ statements. 

    -ravi

    Thursday, May 31, 2012 4:37 PM
  • Then you know there is tab that shows you the sql generated by a linq statement right?

    Alternatively

    1. There are some tools that convert linq to sql
    2. You can create a mock up modifying a local copy instead.

    Regards

    • Edited by Serguey123 Thursday, May 31, 2012 4:48 PM Added options
    Thursday, May 31, 2012 4:42 PM
  • Very simple.  In your app (VB.NET but same for C#) to get the SQL generated simply use a ToString() with your statement.

    For example in my app I have the following Linq to SQL:

    Dim files As List(Of RFolder) = (From f In dataContext.RFolders Select f Order By f.UpdateDate Descending Skip skipNum Take pageSize).ToList

    If I add the line:

    Dim sqlSTR As String = (From f In dataContext.RFolders Select f Order By f.UpdateDate Descending Skip skipNum Take pageSize).ToString

    it returns:

    SELECT [t1].[FolderNumber], [t1].[FolderName], [t1].[UpdateDate], [t1].[PossibleLocationURL], [t1].[ReleaseYear], [t1].[Rating]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UpdateDate] DESC) AS [ROW_NUMBER], [t0].[FolderNumber], [t0].[FolderName], [t0].[UpdateDate], [t0].[PossibleLocationURL], [t0].[ReleaseYear], [t0].[Rating]
        FROM [dbo].[RFolders] AS [t0]
        ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
    ORDER BY [t1].[ROW_NUMBER]

    You do not have to execute and you can use the generated string to log/archive or whatever you want.

    Hope this helps

    Lloyd SHeen


    Lloyd Sheen

    Thursday, May 31, 2012 5:19 PM
  • That still wouldn't fix his problem with the SQL generated by SubmitChanges().

    Friday, June 1, 2012 8:36 AM
  • Oh boy. I feel stupid. I'm using LINQ for the last two years and i did not know that i can view the query with .ToString() 


    -ravi

    Friday, June 1, 2012 7:31 PM
  • i did not know that i can view the query with .ToString() 


    As an aside, that is why answering posts in forums is rewarding, for one learns new things which may have been overlooked.

    William Wegerson (www.OmegaCoder.Com)

    Friday, June 1, 2012 7:51 PM
    Moderator