Muokkaa

Jaa

How to: Directly Execute SQL Queries

  • Artikkeli

LINQ to SQL translates the queries you write into parameterized SQL queries (in text form) and sends them to the SQL server for processing.

SQL cannot execute the variety of methods that might be locally available to your application. LINQ to SQL tries to convert these local methods to equivalent operations and functions that are available inside the SQL environment. Most methods and operators on .NET Framework built-in types have direct translations to SQL commands. Some can be produced from the functions that are available. Those that cannot be produced generate run-time exceptions. For more information, see SQL-CLR Type Mapping.

In cases where a LINQ to SQL query is insufficient for a specialized task, you can use the ExecuteQuery method to execute a SQL query, and then convert the result of your query directly into objects.

Example 1

In the following example, assume that the data for the Customer class is spread over two tables (customer1 and customer2). The query returns a sequence of Customer objects.

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
(@"SELECT c1.custid as CustomerID, c2.custName as ContactName
    FROM customer1 as c1, customer2 as c2
    WHERE c1.custid = c2.custid"
);

Dim db As New Northwnd("c:\northwnd.mdf")
Dim results As IEnumerable(Of Customer) = _
    db.ExecuteQuery(Of Customer) _
    ("SELECT c1.custID as CustomerID," & _
    "c2.custName as ContactName" & _
    "FROM customer1 AS c1, customer2 as c2" & _
    "WHERE c1.custid = c2.custid")

As long as the column names in the tabular results match column properties of your entity class, LINQ to SQL creates your objects out of any SQL query.

Example 2

The ExecuteQuery method also allows for parameters. Use code such as the following to execute a parameterized query.

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
    ("SELECT contactname FROM customers WHERE city = {0}",
    "London");

    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim results As IEnumerable(Of Customer) = _
db.ExecuteQuery(Of Customer) _
("SELECT contactname FROM customers WHERE city = {0}, 'London'")

The parameters are expressed in the query text by using the same curly notation used by Console.WriteLine() and String.Format(). In fact, String.Format() is actually called on the query string you provide, substituting the curly braced parameters with generated parameter names such as @p0, @p1 …, @p(n).

See also