LINQ Example

As promised, I am going to give you a run through of how we implemented a job search facility by utilising SQL LINQ.

First, we developed the database structure:

We then created a 'LinqToSqlClasses' file in vs2008. This allowed us to add tables onto the design surface from the server explorer window.

This is when we spotted our first naming issue. Because we named the column of some of the tables the same as the name of the table, the LINQ designer appended an indexer onto the property name. This has been noted for future projects.

We used a repeater on the jobsearch page which held the template for displaying the categories. We set its datasource through the following code:

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Category>(c => c.Jobs);
context.LoadOptions = options;
IQueryable<Category> data = context.Categories.Where(c => c.IsDeleted == false);
this.rptJobs.DataSource = data;
this.rptJobs.DataBind();

The important part of the code above is the dataload options, we needed a count of the jobs in a specific category, so we had to make sure the jobs relation was loaded, we then extended the Category class using a partial class and added a new property which provided us with a count of the jobs, this could then be directly bound to the repeater.

The jobs view was then displayed by binding a datagrid to the following:

using (JobSearchDataClassesDataContext context = new JobSearchDataClassesDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Job>(j => j.JobLocations);
options.LoadWith<JobLocation>(l => l.Location);
context.LoadOptions = options;
this.grdJobs.DataSource = from j in context.Jobs
                                                where j.IsDeleted == false && j.CategoryId == categoryId
                                                orderby j.Created descending
                                                select j;
this.grdJobs.DataBind();
}

The job details view was then built by producing a HTML layout and retrieving the data to populate it by:

jobId = Convert.ToInt32(Request.QueryString["JobId"]);
Job job = null;

using (JobSearchDataClassesDataContext context = new JobSearchDataClassesDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Job>(j => j.JobLocations);
options.LoadWith<JobLocation>(l => l.Location);
options.LoadWith<Job>(j => j.Category);
options.LoadWith<Job>(j => j.Duration);
options.LoadWith<Job>(j => j.JobType);
context.LoadOptions = options;
job = (from j in context.Jobs
            where j.Id == jobId
            select j).Single();
}

The administration part of the site was mainly built using the LinqDataSource object.
The code below shows how we created a data source and attached it to a gridview.

<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="JobSearchDataClassesDataContext" EnableInsert="True" EnableUpdate="True" TableName="Categories" Where="IsDeleted == @IsDeleted">
<WhereParameters>
<asp:Parameter DefaultValue="False" Name="IsDeleted" Type="Boolean" />
</WhereParameters>
</asp:LinqDataSource>