Web Design Nottingham Header

LINQ Example

Publication Date: Mon 24 Mar 2008

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>

<asp:GridView ID="GridView1" CssClass="grid" Caption="Categories" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="LinqDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" Visible="False" />
<asp:BoundField DataField="Category1" HeaderText="Name" SortExpression="Category1" />
<asp:CheckBoxField DataField="IsDeleted" HeaderText="Delete" SortExpression="IsDeleted" />
</Columns>
<AlternatingRowStyle CssClass="altRow" />
</asp:GridView>

Hopefully this will give you an idea as to how efficient LINQ is in enabling data driven software applications

Quotes

"A relief to find top-notch IT skills combined with efficiency and integrity. Thank you."
John Mankey - FFSoftware Ltd

Latest Projects

Angel Wings web design Labarde web design Internet Gardener web design Stonesilver web design Build Investment Jewellery web design

Developer Certifications

MCPD logo

Partners

Comodo logo protx logo

Coding Standards

XHTML CSS

Agilis Software Limited | 2 Lynemouth Court, Arnold, Nottingham. NG5 8TY
Registered in England No. 5688723. VAT No. 886 4538 70
Tel: 07973 766989
Email: stephen@agilissoftware.co.uk