Convert Linq Query to SQL Server Query
-
Hi All I am converting one of my small forms from typed-dataset to LINQ as a testing to see the difference and if it is better than dataset, so far I can tell it is much much better, easier, less coding. But since I am still learning this LINQ I came up with 2 issue I want to ask about. 1- The scenario in my old form is as follow: I have a dynamic query build on bases of string concatenations depends on many form control values. After I display the result in listview, there is a button to export the result to excel. In my new form where I used LINQ, I manage to convert the dynamic query by string concatenation to LINQ daynamic query, but my export to excel function works by passing the sql select statement. of course I can keep building my dynamic sql in both way one for my result and one for my export to excel procedure, but I was wonder if I can convert the LINQ query to SQL select statement. 2- I found 2 way of creating dynamic LINQ, one of them is Using the ExecuteQuery Method My questions is, Is this method safe against SQL Injection since it use parameter? Thanks in advance
-
Hi All I am converting one of my small forms from typed-dataset to LINQ as a testing to see the difference and if it is better than dataset, so far I can tell it is much much better, easier, less coding. But since I am still learning this LINQ I came up with 2 issue I want to ask about. 1- The scenario in my old form is as follow: I have a dynamic query build on bases of string concatenations depends on many form control values. After I display the result in listview, there is a button to export the result to excel. In my new form where I used LINQ, I manage to convert the dynamic query by string concatenation to LINQ daynamic query, but my export to excel function works by passing the sql select statement. of course I can keep building my dynamic sql in both way one for my result and one for my export to excel procedure, but I was wonder if I can convert the LINQ query to SQL select statement. 2- I found 2 way of creating dynamic LINQ, one of them is Using the ExecuteQuery Method My questions is, Is this method safe against SQL Injection since it use parameter? Thanks in advance
- You can use ObjectQuery.ToTraceString[^] to retrieve the generated SQL commands. It's not pretty though. LINQ does its best but it can't optimize SQL statements in the best manner for all situation. 2) As long as you are using parameterized queries it should be OK.
I know the language. I've read a book. - _Madmatt
-
- You can use ObjectQuery.ToTraceString[^] to retrieve the generated SQL commands. It's not pretty though. LINQ does its best but it can't optimize SQL statements in the best manner for all situation. 2) As long as you are using parameterized queries it should be OK.
I know the language. I've read a book. - _Madmatt
Hi Mark Let me tell you that I liked your answer, and it may seem the way to go except that it seem my computer does not like it :laugh: I write this code down
' References added are:
System.Data.Entity
System.Data.LinqImports System.Linq
Imports System.Data.ObjectsDim productID = 900 Using context As New dxAdventureWorksDataContext() ' Define the object query for the specific product. Dim productQuery As ObjectQuery(Of Product) = **context.Products.Where("it.ProductID = @productID")** productQuery.Parameters.Add(New ObjectParameter("productID", productID)) ' Write the store commands for the query. Console.WriteLine(productQuery.ToTraceString()) End Usingbut on the underlying bold sentence, it is giving me error : Overload resolution failed because no accessible 'Where' can be called with these arguments: 1- What I should change? 2- What is the meaning of 'it' in
Where("it.ProductID = @productID")3- That ObjectQuery is new to me, I am using this styleDim q = From p In context.Products
Where p.ProductID = 1
Select pShould I change my Linq query to ObjectQuery in order to get the sql query from it? Thanks in advance.
-
- You can use ObjectQuery.ToTraceString[^] to retrieve the generated SQL commands. It's not pretty though. LINQ does its best but it can't optimize SQL statements in the best manner for all situation. 2) As long as you are using parameterized queries it should be OK.
I know the language. I've read a book. - _Madmatt
Hi again. Regarding my first and second question, I manage to get it. It seem the code in the link you gave is Linq to Entity not Linq to SQL as I understand from this link Querying with LINQ to Entities vs ObjectQuery in EF I don't try the linq to entity yet so I guess i will not care for 'it' right now. [edit] I deleted my code because it is wrong. I am confused a little.
modified on Friday, April 29, 2011 2:45 AM
-
Hi All I am converting one of my small forms from typed-dataset to LINQ as a testing to see the difference and if it is better than dataset, so far I can tell it is much much better, easier, less coding. But since I am still learning this LINQ I came up with 2 issue I want to ask about. 1- The scenario in my old form is as follow: I have a dynamic query build on bases of string concatenations depends on many form control values. After I display the result in listview, there is a button to export the result to excel. In my new form where I used LINQ, I manage to convert the dynamic query by string concatenation to LINQ daynamic query, but my export to excel function works by passing the sql select statement. of course I can keep building my dynamic sql in both way one for my result and one for my export to excel procedure, but I was wonder if I can convert the LINQ query to SQL select statement. 2- I found 2 way of creating dynamic LINQ, one of them is Using the ExecuteQuery Method My questions is, Is this method safe against SQL Injection since it use parameter? Thanks in advance
LINQ has better facilities than SQL for working with dynamic queries, because it provides programmatic interfaces for building and inspecting queries. Instead of building and parsing LINQ query strings, you can build LINQ expressions directly by calling static methods of the Expression[^] class. Then, instead of parsing SQL strings, you can use ExpressionVisitor[^] to determine the structure of the expressions that you've built. This way, you would bypass SQL completely, and make your code shorter, more maintainable, and easier to read.
-
LINQ has better facilities than SQL for working with dynamic queries, because it provides programmatic interfaces for building and inspecting queries. Instead of building and parsing LINQ query strings, you can build LINQ expressions directly by calling static methods of the Expression[^] class. Then, instead of parsing SQL strings, you can use ExpressionVisitor[^] to determine the structure of the expressions that you've built. This way, you would bypass SQL completely, and make your code shorter, more maintainable, and easier to read.
I will look to it and see how they works. Thanks.
-
Hi All I am converting one of my small forms from typed-dataset to LINQ as a testing to see the difference and if it is better than dataset, so far I can tell it is much much better, easier, less coding. But since I am still learning this LINQ I came up with 2 issue I want to ask about. 1- The scenario in my old form is as follow: I have a dynamic query build on bases of string concatenations depends on many form control values. After I display the result in listview, there is a button to export the result to excel. In my new form where I used LINQ, I manage to convert the dynamic query by string concatenation to LINQ daynamic query, but my export to excel function works by passing the sql select statement. of course I can keep building my dynamic sql in both way one for my result and one for my export to excel procedure, but I was wonder if I can convert the LINQ query to SQL select statement. 2- I found 2 way of creating dynamic LINQ, one of them is Using the ExecuteQuery Method My questions is, Is this method safe against SQL Injection since it use parameter? Thanks in advance
Thank all for your help, I guess there is to much in LINQ to learn since many objects are related to each other. Regarding my question, I solved using this way.
Dim context As New dxAdventureWorksDataContext Dim query\_product As IQueryable(of Product) = From p In context.Products Select p ' build dynamic linq query\_product = query\_product.Where(Function(f) f.ProductID > 1000) query\_product = query\_product.Where(Function(f) f.Name.Contains("A")) ' create another query for required field, if you want all fields, you may omit this line Dim query\_product\_field = From pp In query\_product Select pp.ProductNumber,pp.Name ' get sql query Dim cmd As SqlCommand = TryCast(context.GetCommand(query\_product\_field), SqlCommand) Debug.Print(cmd.CommandText)the output is : SELECT [t0].[ProductNumber], [t0].[Name] FROM [Production].[Product] AS [t0] WHERE ([t0].[Name] LIKE @p0) AND ([t0].[ProductID] > @p1)