Microsoft is working very hard on Visual Studio codename "Orcas", and recently released LINQ CTP (May) and ADO.NET vNext. This article is based on the LINQ May 2006 CTP.
In order to run the samples and follow the article, you must have:
After you have installed LINQ, you will have LINQ related templates in VS2005. So create a new project as follows:
LINQRelations in the Name field. 
Well� now we have a new Windows Forms application with an empty form. Let's generate the code for the database objects.
MovieCollection class hierarchy, annotated with primary key and foreign key designations, by entering the following command: "C:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:.\SQLExpress /database:" MovieCollection " /pluralize /code:MovieCollection.cs. Now, we have generated entities and they are ready to be queried.
Load event, we have to write some code to load data from the server.
private void Form1_Load(object sender, EventArgs e)
{
MovieCollection colllection =
new MovieCollection(@"Data Source=.\SQLEXPRESS;Database" +
@" = MovieCollection;Integrated Security=True;");
Table<Movie> movies = colllection.Movies;
movieBindingSource.DataSource = movies;
}
Execute the project to confirm results. (I added a movie so the table won't be empty). So far so good. Microsoft confirmed that one-to-one and one-to-many relations are supported in the SQL Metal tool (the tool we used to generate code in MovieCollection.cs). What if we are anxious and we would like to support it?
In this database, we have two entity tables (Movie and Category) and a junction table, MovieCategory, for many-to-many support.
We have to write some code to support it, but it's worth the effort.
We have to get all objects from one instance. In this way, data consistency is guaranteed. (For more info, see DLinq Hands on Lab for C#/VB.NET form LINQ CTP). In the Program.cs file, write:
private static MovieCollection dataContext = null;
internal static MovieCollection DataContext
{
get
{
if (null == dataContext)
{
dataContext =
new MovieCollection(@"Data Source=.\SQLEXPRESS;" +
@"Database = MovieCollection;" +
@"Integrated Security=True;");
}
return dataContext;
}
}
We will use this Singleton implementation to access the database only from one point. To ensure this, we have to change the Form_Load event to:
private void Form1_Load(object sender, EventArgs e)
{
movieBindingSource.DataSource = Program.DataContext.Movies;
}
The next thing to do is to extend the Movie class to return all Categories. I prefer to use partial classes in .NET 2.0, so we can differentiate from tool generated code in case we have to re-generate it. In Visual Studio 2005, add a new file called MovieCollectionExtend.cs. Do not forget to surround classes in MovieCollection.cs in the namespace LINQRelations. Now, let's put in action the magic of partial classes. Fill MovieCollectionExtend.cs with the following content:
public partial class Movie
{
protected EntitySet<Category> categories;
/// <summary>
/// Return assigned categories to current movie
/// </summary>
public EntitySet<Category> Categories
{
get
{
categories = new EntitySet<Category>(onAddCategory, onRemoveCategory);
var cats = from c in Program.DataContext.Categories
join mc in Program.DataContext.MovieCategories
on c.CategoryID equals mc.CategoryID
where mc.MovieID == this.MovieID
select c;
//add to result
foreach (var c in cats)
categories.Add(c);
return categories;
}
}
protected void onAddCategory(Category arg)
{
//add entry to MovieCategories table
MovieCategory mc = new MovieCategory();
mc.MovieID = this.MovieID;
mc.CategoryID = arg.CategoryID;
Program.DataContext.MovieCategories.Add(mc);
}
protected void onRemoveCategory(Category arg)
{
}
}
Believe it or not, this is all we need to extract all the categories to a certain movie.
Let's explain the code step by step. We created a new property for the Movie class called Categories. Inside the setter, we do create a new instance for the Categories collection that will be filled and which will be returned on request. In the constructor are defined two delegates � one that is executed when a new element is added to collection, and one when an element is deleted.
categories = new EntitySet Category (onAddCategory, onRemoveCategory);
The next statement performs a LINQ query against the objects. Unfortunately, we must have access to the DataContext instance at that time.
var cats = from c in Program.DataContext.Categories
join mc in Program.DataContext.MovieCategories
on c.CategoryID equals mc.CategoryID
where mc.MovieID == this.MovieID
select c;
This query is translated to SQL as:
SELECT [t0].[CategoryID], [t0].[CategoryName]
FROM [Category] AS [t0], [MovieCategory] AS [t1]
WHERE ([t1].[MovieID] = 1) AND ([t0].[CategoryID] = [t1].[CategoryID])
The next two lines are easy: fill the private collection and return it as the result.
//add to result
foreach (var c in cats)
categories.Add(c);
return categories;
The delegate onAddCategory is responsible to add a new row in the junction table MovieCategory when assigning a new category to the movie, and onRemoveCategory to delete a row. And this is it.
Let's create another DataGridView to ensure we achieved the goal.
DataGridView control on the form and call it categoriesDataGridView. Form_load event, add a line to auto-generate columns:
categoriesDataGridView.AutoGenerateColumns = true;
movieBindingSource for the CurrentChanged event. private void movieBindingSource_CurrentChanged(object sender, EventArgs e)
{
categoriesDataGridView.DataSource =
(movieBindingSource.Current as Movie).Categories;
}
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
