.NET Framework Class Library
DataContext..::.ExecuteQuery<(Of <(TResult>)>) Method (String, array<Object>[]()[])

Executes SQL queries directly on the database and returns objects.

Namespace:  System.Data.Linq
Assembly:  System.Data.Linq (in System.Data.Linq.dll)
Syntax
Visual Basic (Declaration) 
Public Function ExecuteQuery(Of TResult) ( _
    query As String, _
    ParamArray parameters As Object() _
) As IEnumerable(Of TResult)
Visual Basic (Usage) 
Dim instance As DataContext
Dim query As String
Dim parameters As Object()
Dim returnValue As IEnumerable(Of TResult)

returnValue = instance.ExecuteQuery(query, _
    parameters)
C# 
public IEnumerable<TResult> ExecuteQuery<TResult>(
    string query,
    params Object[] parameters
)
Visual C++ 
public:
generic<typename TResult>
IEnumerable<TResult>^ ExecuteQuery(
    String^ query, 
    ... array<Object^>^ parameters
)
JScript 
JScript does not support generic types or methods.

Type Parameters

TResult

The type of the elements in the returned collection.

Parameters

query
Type: System..::.String
The SQL query to be executed.
parameters
Type: array<System..::.Object>[]()[]
The array of parameters to be passed to the command. Note the following behavior:

  • If the number of objects in the array is less than the highest number identified in the command string, an exception is thrown.

  • If the array contains objects that are not referenced in the command string, no exception is thrown.

  • If a parameter is null, it is converted to DBNull.Value.

Return Value

Type: System.Collections.Generic..::.IEnumerable<(Of <(TResult>)>)
A collection of objects returned by the query.
Remarks

This method is a pass-through mechanism for cases where LINQ to SQL does not provide for a particular scenario.

The algorithm for matching columns in the result of the query to fields and properties in the object works as follows:

  • If a field or property is mapped to a particular column name, that column name is expected in the resultset.

  • If a field or property is not mapped, a column with the same name as the field or property is expected in the resultset.

  • The comparison is performed by first looking for a case-sensitive match. If such a match is not found, a subsequent search occurs for a case-insensitive match.

  • The query must return all the tracked fields and properties of the object (apart from those subject to deferred loading) when all the following are true:

    • If <T> is an entity explicitly tracked by the DataContext.

    • ObjectTrackingEnabled is true.

    • The entity has a primary key.

    Otherwise an exception is thrown.

  • In all other cases, the query can retrieve just a subset of the tracked fields and properties for the object.
Platforms

Windows 7, Windows Vista, Windows XP SP2, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003

The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.
Version Information

.NET Framework

Supported in: 3.5
See Also

Reference

DataContext Class
DataContext Members
ExecuteQuery Overload
System.Data.Linq Namespace
Tags :


Community Content

DeborahK
Calling Stored Procedures

You can directly call a stored procedure with ExecuteQuery by passing the stored procedure name as follows:

 result = dc.ExecuteQuery(Of T)(storedProcedureName)

But if your stored procedure has parameters, you need to pass a SQL statement that executes your stored procedure with replacements ({0}) for the parameters. The parameter list in this method is the list of parameters for the replacements. Generalized code would then look something like this:

Dim queryString As String = "Exec " & storedProcedureName
For i As Integer = 0 To arrParam.Count - 1
queryString &= " {" & i & "},"
Next
queryString = queryString.TrimEnd(","c)
result = dc.ExecuteQuery(Of T)(queryString, arrParam)

In the above code, the resulting queryString would look something like this:

"Exec RetrieveCustomerById {0}"

If you want to pass the parameters by name and not order, you can modify the above code to generate a queryString that looks like this:

"Exec RetrieveCustomerById CustomerId = {0}"


Y-SW
ATTN: NULL values are not supported

unfortunatelly, it is not possible to pass NULL values to this method as it is decribed. Confirmed at: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305114&wa=wsignin1.0

Tags :

Peter Oehlert
ColumnAttribute.Name property not honored for result
Unlike directly executing a stored procedure, a TResult member decorated with the ColumnAttribute will not be respected. This means your property/field value in TResult needs to exactly match the output of your query to have the field value populated. Another ramification is that you may not map to a column in your query that has a space in it since you can not have a space in a CLI member name.
Tags :

Page view tracker