In previous few posts we discussed entity framework and how you can use it to model your database tables/views/stored procedures and use them in your application. We also touched on Linq to Entities (more on this later).
Today we will review Linq to SQL. Linq to SQL is another way to work with your database instead of using entity framework. Linq to SQL is good for simple applications as it only supports 1 to 1 mapping of tables, views, procedures or UDFs.
Let's look at an example.
1. I created a console app and added Linq to SQL Classes dbml object.
2. Add the tables / views you want to generate classes for.
3. Now we will select the data from these tables using Linq to SQL.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace LinqToSQL
{
class Program
{
static void Main(string[] args)
{
// DataContext takes a connection string
linqToSQLDataContext db = new linqToSQLDataContext();
var customerQuery = from d in db.Customers
orderby d.LastName
select d;
foreach (var cust in customerQuery)
{
Console.WriteLine("id = {0}, FirstName = {1},LastName = {2}",
cust.CustomerID, cust.FirstName, cust.LastName);
}
Console.WriteLine(Environment.NewLine);
//getting data from multiple tables
var CustomersAndAddresses = from c in db.Customers
from o in db.CustomerAddresses
select new { c.FirstName,c.LastName,
o.Address1,o.Address2,o.City,o.State };
foreach (var cust in CustomersAndAddresses)
{
Console.WriteLine("Name={0}, Address = {1},City = {2}",
cust.FirstName + ' ' + cust.LastName, cust.Address1 + ' ' +
cust.Address2, cust.City);
}
Console.Read();
}
}
}
As you can see in code above, we created two Linq to SQL queries - 1st one is getting data with just one table, the other one with both tables.
You can also add a WHERE clause similar to T-SQL. For example, I can modify above query
var CustomersAndAddresses = from c in db.Customers
from o in db.CustomerAddresses
WHERE c.CustomerID == o.CustomerID
WHERE c.CustomerID == 1
select new { c.FirstName,c.LastName, o.Address1,o.Address2,o.City,o.State };
In next post we will review how you can insert, update or delete a record using Linq to SQL.
Thank you.
