Dinesh Kulkarni, Luca Bolognese, Matt Warren, Anders Hejlsberg, Kit George
Visual Studio Code Name "Orcas"
.Net Framework 3.5
Summary: LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically. (119 printed pages)
A Quick Tour
Creating Entity Classes
Querying Across Relationships
Modifying and Saving Entities
The Entity Lifecycle
Entity Classes In-Depth
Interoperating with ADO.NET
Change Conflict Resolution
Stored Procedures Invocation
The Entity Class Generator Tool
Generator Tool DBML Reference
NET Framework Function Support and Notes
Most programs written today manipulate data in one way or another and often this data is stored in a relational database. Yet there is a huge divide between modern programming languages and databases in how they represent and manipulate information. This impedance mismatch is visible in multiple ways. Most notable is that programming languages access information in databases through APIs that require queries to be specified as text strings. These queries are significant portions of the program logic. Yet they are opaque to the language, unable to benefit from compile-time verification and design-time features like IntelliSense.
Of course, the differences go far deeper than that. How information is represented—the data model—is quite different between the two. Modern programming languages define information in the form of objects. Relational databases use rows. Objects have unique identity as each instance is physically different from another. Rows are identified by primary key values. Objects have references that identify and link instances together. Rows are left intentionally distinct requiring related rows to be tied together loosely using foreign keys. Objects stand alone, existing as long as they are still referenced by another object. Rows exist as elements of tables, vanishing as soon as they are removed.
It is no wonder that applications expected to bridge this gap are difficult to build and maintain. It would certainly simplify the equation to get rid of one side or the other. Yet relational databases provide critical infrastructure for long-term storage and query processing, and modern programming languages are indispensable for agile development and rich computation.
Until now, it has been the job of the application developer to resolve this mismatch in each application separately. The best solutions so far have been elaborate database abstraction layers that ferry the information between the applications domain-specific object models and the tabular representation of the database, reshaping and reformatting the data each way. Yet by obscuring the true data source, these solutions end up throwing away the most compelling feature of relational databases; the ability for the data to be queried.
LINQ to SQL, a component of Visual Studio Code Name "Orcas", provides a run-time infrastructure for managing relational data as objects without losing the ability to query. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define. Your application is then free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.
* LINQ to SQL is designed to be non-intrusive to your application.
o It is possible to migrate current ADO.NET solutions to LINQ to SQL in a piecemeal fashion (sharing the same connections and transactions) since LINQ to SQL is simply another component in the ADO.NET family. LINQ to SQL also has extensive support for stored procedures, allowing reuse of the existing enterprise assets.
* LINQ to SQL applications are easy to get started.
o Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns. Of course, it is not even necessary to do this by hand. A design-time tool is provided to automate translating pre-existing relational database schemas into object definitions for you.
Together, the LINQ to SQL run-time infrastructure and design-time tools significantly reduce the workload for the database application developer. The following chapters provide an overview of how LINQ to SQL can be used to perform common database-related tasks. It is assumed that the reader is familiar with Language-Integrated Query and the standard query operators.
LINQ to SQL is language-agnostic. Any language built to provide Language-Integrated Query can use it to enable access to information stored in relational databases. The samples in this document are shown in both C# and Visual Basic; LINQ to SQL can be used with the LINQ-enabled version of the Visual Basic compiler as well.
A Quick Tour
The first step in building a LINQ to SQL application is declaring the object classes you will use to represent your application data. Let's walk through an example.
Creating Entity Classes
We will start with a simple class Customer and associate it with the customers table in the Northwind sample database. To do this, we need only apply a custom attribute to the top of the class declaration. LINQ to SQL defines the Table attribute for this purpose.
public class Customer
public string CustomerID;
public string City;
ToString(..., Int32) any overload ending with an Int32 toBase
* Versions with the IFormatProvider parameter.
* Methods that involve an array (To/FromBase64CharArray, To/FromBase64String).
TimeSpan (year, month, day)
TimeSpan (year, month, day, hour, minutes, seconds)
TimeSpan (year, month, day, hour, minutes, seconds, milliseconds)
Comparison operators: <,==, and so on in C#; <, =, and so on in Visual Basic
* Static (Shared in Visual Basic) methods:
* Non-static (Instance) methods / properties:
Ticks, Milliseconds, Seconds, Hours, Days
TotalMilliseconds, TotalSeconds, TotalMinutes, TotalHours, TotalDays,
Duration() [= ABS], Negate()
TimeSpan FromDay(Double), FromHours, all From Variants
DateTime(year, month, day)
DateTime(year, month, day, hour, minutes, seconds)
DateTime(year, month, day, hour, minutes, seconds, milliseconds)
DateTime – DateTime (gives TimeSpan)
DateTime + TimeSpan (gives DateTime)
DateTime – TimeSpan (gives DateTime)
* Static (Shared) methods:
* Non-static (Instance) methods / properties:
Day, Month, Year, Hour, Minute, Second, Millisecond, DayOfWeek
Difference from .NET
SQL's datetime values are rounded to .000, .003 or .007 seconds, so it is less precise than those of .NET.
The range of SQL's datetime starts at January 1st, 1753.
SQL does not have a built-in type for TimeSpan. It uses different DATEDIFF methods that return 32-bit integers. One is DATEDIFF(DAY,...), which gives the number of days; another is DATEDIFF(MILLISECOND,...), which gives the number of milliseconds. An error results if the DateTimes are more than 24 days apart. In contrast, .NET uses 64-bit integers and measures TimeSpans in ticks.
To get as close as possible to the .NET semantics in SQL, LINQ to SQL translates TimeSpans into 64-bit integers and uses the two DATEDIFF methods mentioned above to calculate the number of ticks between two dates.
DateTime UtcNow is evaluated on the client when the query is translated (like any expression that does not involve database data).
FromBinary(Long), FileTime, FileTimeUtc, OADate
DataContext provides methods and properties to obtain the SQL generated for queries and change processing. These methods can be useful for understanding LINQ to SQL functionality and for debugging specific problems.
DataContext Methods to Get Generated SQL
Log Prints SQL before it is executed. Covers query, insert, update, delete commands. Usage:
db.Log = Console.Out;
db.Log = Console.Out
GetQueryText(query) Returns the query text of the query without of executing it. Usage:
GetChangeText() Returns the text of SQL commands for insert/update/delete without executing them. Usage: