New Page 1
A couple of interesting notes about using using
DLINQ:
- You can use SQL Server 2000 (my June CTP installation of SQL 2005 is
fried).
- You should pay close attention to what actual SQL commands are being
issued using SQL Profiler.
The first thing I did was create a new LINQ project in Visual Studio 2005.
Then I ran the sqlmetal.exe utility to create my DLINQ-based data structures for
me automatically from the Visual Studio command prompt:
C:\Program Files\LINQ Preview\Bin>sqlmetal
/server:. /user:blah /password:yesofcourseIwontpostthis /pluralize
/code:Northwind.cs /database:NorthWind /namespace:UnboxedSolutions.Linq.Demo.Common
Then I created the following code...
namespace UnboxedSolutions.Linq.Demo.UI
{
class Program
{
static void Main(string[] args)
{
// Use a standard connection string
using(SqlConnection connection = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=."))
{
DataContext db = new DataContext(connection);
connection.Open();
Table<Customer> Customers = db.GetTable<Customer>();
var custs =
from c in Customers
where c.CompanyName.StartsWith("a")
select c;
foreach(Customer cust in custs)
{
Console.WriteLine("ID={0}, CompanyName={1}", cust.CustomerID, cust.CompanyName);
}
}
}
}
}
Here's what the output was in SQL Profiler:
exec sp_executesql
N'SELECT [t0].[Address], [t0].[City], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Country], [t0].[CustomerID], [t0].[Fax],
[t0].[Phone], [t0].[PostalCode], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0', N'@p0 nvarchar(2)', @p0 = N'a%'
This is great because SQL Server uses a LIKE comparison in the where clause
for me. But, I can pretty easily create bad SQL code by referencing a field in
the Association entity set "Orders". For example let's say I change my
Console.WriteLine method call to the following
Console.WriteLine("ID={0}, CompanyName={1},
NumOrders={1}", cust.CustomerID, cust.CompanyName,
cust.Orders.Count);
I get the following queries issued against the database:
exec sp_executesql
N'SELECT [t0].[Address], [t0].[City], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Country], [t0].[CustomerID], [t0].[Fax],
[t0].[Phone], [t0].[PostalCode], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0', N'@p0 nvarchar(2)', @p0 = N'a%'
go
exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[EmployeeID],
[t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0', N'@p0 nvarchar(5)', @p0 = N'ALFKI'
go
exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[EmployeeID],
[t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0', N'@p0 nvarchar(5)', @p0 = N'ANATR'
go
exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[EmployeeID],
[t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0', N'@p0 nvarchar(5)', @p0 = N'ANTON'
go
exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[EmployeeID],
[t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0', N'@p0 nvarchar(5)', @p0 = N'AROUT'
go
Probably *not* exactly what I want performance-wise. But that aside, how is it
that we could reference Orders from Customers? Well, so far all I can say is
that sqlmetal.exe utility is pretty slick. :-) Here's what it did inside
the Customer entity class it created:
[Association(Name="FK_Orders_Customers", Storage="_Orders", OtherKey="CustomerID")]
In terms of functionality, yeah this can be pretty cool.
LLBLGen Pro
does this for you if you use the Self-Service model which nobody uses because
the Adapter architecture lends itself much better in terms of scalability and
architectural control, but I digress. Tomorrow I will continue on this topic...
