I'm seeing a lot of buzz around LINQ to SQL lately and how it can help to quickly create a Silverlight 2 data app.
I'll be giving a talk around this at the upcoming Code Camp 9 in Waltham, MA, and I wanted to share how I am doing basic CRUD operations in LINQ to SQL, as I've seen this question come up a few times.
You may remember my Silverlight 1.1 Alpha demo app, "Andy's Fridge." I'll be using a similar Beer Database which has a very simple schema containing Beers and Brewers like so:
It's important to note that the RowVersionId columns in the diagram are Timestamp columns. This will allow LINQ to SQL to generate more efficient Update statements, based on the unique Row Id (timestamp) instead of Where clauses with lots of logical "AND's" for each original value.
So, given that schema is like that above, our CRUD operations for the Beer entity would exist in our web service like so:
public class BeerService : IBeerService
{
public List<Beer> GetAllBeers()
{
DataClassesDataContext db = new DataClassesDataContext();
var beers = from b in db.Beers
orderby b.BeerName
select b;
return beers.ToList<Beer>();
}
public void SaveBeer(Beer beer)
{
DataClassesDataContext db = new DataClassesDataContext();
if (beer.BeerId > 0)
{
// this is an update
db.Beers.Attach(beer, true);
}
else
{
// this is an insert
db.Beers.InsertOnSubmit(beer);
}
db.SubmitChanges();
}
public void DeleteBeer(Beer beer)
{
DataClassesDataContext db = new DataClassesDataContext();
db.Beers.Attach(beer, false);
db.Beers.DeleteOnSubmit(beer);
db.SubmitChanges();
}
}
Note that I piggybacked the SaveBeer method to do either an Insert operation or Update operation, based on whether or not a primary key is available in the passed entity.
I guess until ADO.NET Data Services (Astoria) and the Entity Framework are officially available, we will have to settle with LINQ to SQL for Data Access! But I don't think we will be waiting long :)
What can we look forward to with Astoria and the EF?
- no need to write tedious web service methods for CRUD operations like shown above
- serialization of Parent and Child records (LINQ to SQL cannot pass related child records across the wire with parent rows)
- Access to other Database Providers other than SQL Server.
- The ability to flexibly map Conceptual model elements such as class properties to Physical columns in the database (LINQ to SQL provides only a 1-to-1 mapping)