Concatenate values in a List with LINQ

I had a stored procedure that accepted a comma delimited string of id’s as a parameter. My source was a generic list of "Employee" objects from which I had to get the id’s and create a comma delimited string.

image

To accomplish this I used a LINQ Aggregate Query:

  1. Dim employees As New List(Of Employee)
  2.  
  3. Dim e1 As New Employee() With { _
  4.     .EmployeeId = Guid.NewGuid, _
  5.     .FirstName = "John", _
  6.     .LastName = "Smith", _
  7.     .Position = "Engineer"}
  8.  
  9. Dim e2 As New Employee() With { _
  10.     .EmployeeId = Guid.NewGuid, _
  11.     .FirstName = "Bob", _
  12.     .LastName = "Johnson", _
  13.     .Position = "Business Analyst"}
  14.  
  15. employees.Add(New Employee() with
  16.  
  17. Dim employeeIds As String = _
  18.         employees.Select(Function(x) x.EmployeeId.ToString()) _
  19.                     .Aggregate(Function(y, z) y & "," & z)

In the example above, I used a comma as my separator.

LINQ Lazy Loading

I was experiencing some performance issues in my Silverlight application and got it narrowed down to the mapping of the domain objects.  After running SQL profiler and doing some research I figured out that it was due to Linq lazy loading.

As an example if I have the following data structure:

Employee_EmployeePosition

I would have an “EmployeePosition” domain object and an “Employee” domain object.  I will use the following Linq statement to bring back all Employee Positions:

  1. using (var dc = new DataClasses1DataContext())
  2. {
  3.     var postiions = dc.EmployeePositions.ToList();
  4. }

Linq lazy loading is enabled by default, so by executing the above query Linq will only bring back Employee Positions.  If I am only looking for Employee Positions everything works as expected.  The issue appears if I want to bring back the Employee information for each position.  Since I have a foreign key relationship between “Employee” and “EmployeePosition”, my dbml has “one to many” association between them.  This allows me to write the following mapping to my object that I return to my Silverlight application:
  1. var myEmployees = postiions.Select(x => new EmployeeWithPosition
  2.                     {
  3.                         FirstName = x.Employee.FirstName,
  4.                         LastName = x.Employee.LastName,
  5.                         Position = x.Position
  6.                     }).ToList();

When executing above code, there is a separate call made to the database to get the “FirstName” then another call to get the “LastName” for each of the “EmployeePosition” objects due to LINQ lazy loading (default).  So if I have 10 employee positions, there are 20 additional calls made to the database.

There are a few solutions to solve this issue:

    1. Disable lazy loading for the entire dbml file. 
    2. Specify “DataLoadOptions” on the Data Context.  This is a valid option if you do not re-use the Data Context.
    3. Use projection to load only necessary data.

The best option for my situation was to use projection to load my data.  To do this I extended my existing Employee domain object by creating a partial “Employee” class and adding a string “Position” property.  Then I used projection to load my data to the Employee domain object.

  1. using (var dc = new DataClasses1DataContext())
  2. {
  3.     var myEmployees = dc.EmployeePositions.Select(x => new Employee
  4.     {                    
  5.         FirstName = x.Employee.FirstName,
  6.         LastName = x.Employee.LastName,                    
  7.         Position = x.Position
  8.     }).ToList();
  9. }

When I executed above code I got the following run-time error:

Explicit construction of entity type ‘Employee’ in query is not allowed.

After doing a little research I discovered that projection to a domain object is not allowed. As a work around, I used my “EmployeeWithPosition” custom object which only contains properties that I am interested in from the “Employee” and “EmployeePosition” domain objects to project my data.

  1. using (var dc = new DataClasses1DataContext())
  2.             {
  3.                 var myEmployees = dc.EmployeePositions.Select(x => new EmployeeWithPosition
  4.                 {                    
  5.                     FirstName = x.Employee.FirstName,
  6.                     LastName = x.Employee.LastName,                    
  7.                     Position = x.Position
  8.                 }).ToList();
  9.             }

Casting IQueryable List

Occasionally I run into situations where I have a List of objects that I need to cast to a list of specific types. One option would be to iterate through the list of objects and cast each item individually and add them to a new list of the specific type. 

  1. Dim countries As New List(Of Country)
  2. For Each item As Object In items
  3.     countries.Add(DirectCast(item, Country))
  4. Next

Another more cleaner option would be to use a Linq statement.

  1. Dim countries As New List(Of Country)
  2. countries = items.Cast(Of Country).ToList()