1. What do we need to work with LINQ to SQL?
2. What does happen when the foreach loop enumerates?
To work with LINQ to SQL we need that each table in a database has corresponding class decorated with attributes:
Example:
[Database(Name = "AdventureWorks")]
public class AdventureDBContext : DataContext
{
public AdventureDBContext(string connString) : base(connString) { }
}
[Table(Name="Person.Contact")]
public class Contact
{
[Column(Name="ContactID", IsPrimaryKey=true)]
public int ContactId
{
set; get;
}
[Column(Name = "FirstName")]
public string FirstName
{
set; get;
}
}
class Program
{
private const string _conString = @"Data Source=MS-D00115-80SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True";
static void Main(string[] args)
{
using (AdventureDBContext dt = new AdventureDBContext(_conString))
{
var contacts = dt.GetTable<Contact>();
dt.Log = Console.Out;
IQueryable<Contact> top10Contacts = (from c in contacts
where c.FirstName == "John"
select c).Take(10);
DbCommand command = dt.GetCommand(top10Contacts);
Trace.WriteLine(command.CommandText);
}
Console.ReadKey();
}
}
Output:
SELECT TOP (10) [t0].[ContactID] AS [ContactId], [t0].[FirstName]
FROM [Person].[Contact] AS [t0]
WHERE [t0].[FirstName] = @p0
The query variable is initialized with query expression that forms an expression tree. When the foreach loop enumerates the expression tree is used to generate the sql query using metadata from the corresponding class decorated with attributes.