Simple LINQ to SQL Nested Select

Question

I'm working on the following

var result = 
    from ap in db.Appraisals
    join at in db.AppraisalTypes 
         on ap.AppraisalType_ID equals at.AppraisalType_ID
    join w in db.Workers 
         on ap.Worker_ID equals w.Worker_ID
    where ap.Worker_ID == WorkerID
          && ap.DateDue != null
          && ap.DateCompleted == null
    select new
    {
        ap.Worker_ID,
        ap.Appraisal_ID,
        at.Description,
        ap.DateDue,
        ap.DateCompleted,
        CompletedBy = from ap2 in db.Appraisals
                      join w2 in db.Workers 
                           on ap2.CompletedBy equals w2.Worker_ID
                      select new
                      {
                          name = w2.Surname + ", " + w2.FirstName
                      }                                 
    };

As you can probably see I am using two worker objects, one being the worker that is having their annual/interim employment appraisal and the other object is their line manager.

This select is used to bind a summary list view control but on the CompletedBy column I am trying to assign will always have the most up to date entry as being null then historic data will be populated with the line managers name when an appraisal has been completed. I have done a quick search on this question and there are many posts w/answers but I'm not very experienced as a developer and looking for a simple answer. My other concern is that I am trying to assign a column with something that I know will always have a null value in the list.

Although the compiler recognises this as being syntactically correct, on the bind my label text property has the following...

System.Collections.Generic.List`1[<>f__AnonymousType9`1[System.String]]

I am just looking to any possible caveats I am building here and to fill in the huge gaps to my very limited knowledge of LINQ.

<asp:ListView ID="lvwProbations" runat="server" 
     OnSelectedIndexChanged="lvwProbations_SelectedIndexChanged"  
     OnPagePropertiesChanged="lvwProbations_PagePropertiesChanged"
     OnPagePropertiesChanging="lvwProbations_PagePropertiesChanging" 
     DataKeyNames="Worker_ID,Appraisal_ID">

     <ItemTemplate>
         <tr class="tableItemStyle" 
             onmouseover="this.style.backgroundColor='Silver'"    
             onmouseout="this.style.backgroundColor='#EAFFFF'">

            <td>
                <asp:Label ID="lblWorkerID" runat="server" 
                     Text='<%# Bind("Worker_ID") %>' Visible="false" />
            </td>
            <td>
                <asp:Label ID="lblProbation" runat="server" Width="200" 
                     Text='<%# Bind("Description") %>' />
            </td>
            <td align="center">
                <asp:Label ID="lblDateDue" runat="server" Width="100" 
                     Text='<%# Bind("DateDue", "{0:d}") %>' />
            </td>
            <td align="center">
                <asp:Label ID="lblDateCompleted" runat="server" Width="100" 
                     Text='<%# Bind("DateCompleted") %>' />
            </td>
            <td align="center">
                <asp:Label ID="lblCompletedBy" runat="server" Width="100" 
                     Text='<%# Bind("CompletedBy") %>' />
            </td>
            <td>
                <asp:ImageButton ID="btnSelect" runat="server" 
                     SkinID="selecttimesheet" CommandName="Select"  />
            </td>
         </tr>
     </ItemTemplate>

     <AlternatingItemTemplate>
         <tr class="tableAlternatingItemStyle"  
             onmouseover="this.style.backgroundColor='Silver'"        
             onmouseout="this.style.backgroundColor='#CCFFFF'">
            <td>
                 <asp:Label ID="lblWorkerID" runat="server" 
                      Text='<%# Bind("Worker_ID") %>' Visible="false" />
            </td>
            <td>
                 <asp:Label ID="lblProbation" runat="server" Width="200" 
                      Text='<%# Bind("Description") %>' />
            </td>
            <td align="center">
                 <asp:Label ID="lblDateDue" runat="server" Width="100" 
                      Text='<%# Bind("DateDue", "{0:d}") %>' />
            </td>
            <td align="center">
                 <asp:Label ID="lblDateCompleted" runat="server" Width="100" 
                      Text='<%# Bind("DateCompleted") %>' />
            </td>
            <td align="center">
                 <asp:Label ID="lblCompletedBy" runat="server" Width="100" 
                      Text='<%# Bind("CompletedBy") %>' />
            </td>
            <td>
                 <asp:ImageButton ID="btnSelect" runat="server" 
                      SkinID="selecttimesheet" CommandName="select" />
            </td>
          </tr>
      </AlternatingItemTemplate>

      <EmptyDataTemplate>
           <table id="Table1" runat="server" style="">
             <tr id="Tr2" runat="server" class="tableHoursHeaderStyle">
                 <th id="Th1" runat="server" style="width:0px;"></th>
                 <th id="Th2" runat="server">Appraisal</th>
                 <th id="Th3" runat="server">Date Due</th>
                 <th id="Th13" runat="server">Date Completed</th>
                 <th id="Th4" runat="server">Completed By</th>
                 <th id="Th6" runat="server" style="width:0px;"></th>
             </tr>
             <tr> 
                 <td colspan="4">
                      <p>No Probations available</p>
                 </td>
             </tr>
         </table>
     </EmptyDataTemplate>

     <LayoutTemplate>
         <table id="Table2" runat="server">
            <tr id="Tr1" runat="server">
                <td id="Td1" runat="server">
                   <table ID="itemPlaceholderContainer" runat="server" 
                          border="0" class="TimeSheet_Table" style="">
                     <tr id="Tr2" runat="server" 
                         class="tableHoursHeaderStyle">
                          <th id="Th1" runat="server" style="width:0px;"></th>
                          <th id="Th2" runat="server">Appraisal</th>
                          <th id="Th3" runat="server">Date Due</th>
                          <th id="Th13" runat="server">Date Completed</th>
                          <th id="Th5" runat="server">Completed By</th>
                          <th id="Th7" runat="server" style="width:0px;"></th>
                     </tr>
                     <tr ID="itemPlaceholder" runat="server"></tr>
                   </table>
                 </td>
              </tr>
           </table>
      </LayoutTemplate>
</asp:ListView>

Edited to show markup/bindings on request

Apologies, basically the nested select binds the System.Collections code snippet as above. I need to get the line managers name knowing that on every occasion the most up to date appraisal for a specific employee will have a null return for the CompletedBy field. What does the code snippet mean, what am I not understanding about my attempt to return the data records/columns? — Splunk
– Splunk, Commented Jul 3, 2012 at 9:22

Brad Rem · Accepted Answer · 2012-07-03 14:46:24Z

2

Your CompletedBy field is actually typed as a collection of a class that has a single string field. Because you are using a select new { name = ... } you are actually creating a class that looks like this which is not what you want at all:

class Anonymous9
{
    public name { get; set; }
}

And, in addition, you are creating a IEnumerable<Anonymous9> which is collection of those objects.

Instead, you just want a single string. To get it to be just a single string, get rid of the new {} and wrap it with FirstOrDefault():

CompletedBy = (from ap2 in db.Appraisals 
                  join w2 in db.Workers  
                       on ap2.CompletedBy equals w2.Worker_ID 
                  select w2.Surname + ", " + w2.FirstName 
                  ).FirstOrDefault()

edited Jul 3, 2012 at 14:46

answered Jul 3, 2012 at 14:40

Brad Rem

6,0262 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Splunk Over a year ago

+1 w/Answer accepted :) Thanks a lot. I need to understand a lot about some basic concepts I am aware of this.

Collectives™ on Stack Overflow

Simple LINQ to SQL Nested Select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related