Home > Software Development > Linq to Sql and Self-Referencing tables

Linq to Sql and Self-Referencing tables

Today I ran into an issue of Eager loading a self referencing Sql table.  I searched the internet to see what others are doing and everything I found was intricate Linq statements that were generating large sql statements.  My goal was to try a different route based on two database queries.
 
Here’s the code I came up with.  This is an excerpt for a sample I’m working on.  In this example I’m also allowing my end point to get all employees or filter by employeeIds.  Obviously this is going against the Northwind database.
 
One other bit of information is this endpoint is in the BLL of a WCF service.  I have my own Employee Datacontract and you will see calls so the Employee Assembler

EmployeeAssembler<Employee>.AssembleContract.  You can add your own or do something else here.

public IEnumerable<Employee> GetEmployees(IEnumerable<int> employeeIds)

{

List<Employee> employees = new List<Employee>();

using (DAL.NWDBDataContext db = new DAL.NWDBDataContext(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))

{

IQueryable<DAL.Employee> dalEmployees;

if (employeeIds != null && employeeIds.Count() > 0)

{

dalEmployees =

from e in db.Employees.Where(e => employeeIds.Contains(e.EmployeeID))

select e;

}

else

{

dalEmployees =

from e in db.Employees

select e;

}

// L2S doesn’t support eager loading with LoadOptions so the ReportsTo needs to be extracted.

// Here we got the employees from above, for each one looping through and getting the managers

// as well.

Collection<int> reportToIds = new Collection<int>();

List<DAL.Employee> listEmployees = dalEmployees.ToList();

foreach(DAL.Employee emp in listEmployees)

{

if (emp.ReportsTo.HasValue)

{

if(reportToIds.IndexOf(emp.ReportsTo.Value) < 0)

{

reportToIds.Add(emp.ReportsTo.Value);

}

}

}

IQueryable<DAL.Employee> managers = default(IQueryable<DAL.Employee>);

List<DAL.Employee> listManagers = new List<DAL.Employee>();

if (reportToIds.Count > 0)

{

managers =

from e in db.Employees.Where(e => reportToIds.Contains(e.EmployeeID))

select e;

}

if (managers != null && managers.Count() > 0)

{

listManagers.AddRange(managers.ToList());

}

foreach (DAL.Employee emp in listEmployees)

{

Employee employee = (EmployeeAssembler<Employee>.AssembleContract(emp));

if(emp.ReportsTo.HasValue)

{

DAL.

Employee manager = listManagers.Find(lm => lm.EmployeeID == emp.ReportsTo.Value);

if(manager != null)

{

employee.Manager =

EmployeeAssembler<Employee>.AssembleContract(manager);

}

}

employees.Add(employee);

}

}

return employees;

}

 

 
Advertisements
Categories: Software Development
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: