Databinding expression for retrieving value of related collection using LINQ

Question

I have a GridView that is bound to a LINQDataSource control that is returning a collection of customers.

Within my DataGrid I need to display the home phone number of a customer, if they have one. The phone numbers of a customer are stored in a separate table with a foreign key pointing to the customer table.

The following binding expression gets me the first phone number for a customer:

<asp:TemplateField HeaderText="LastName" SortExpression="LastName">
   <ItemTemplate>
      <asp:Label ID="PhoneLabel" runat="server" Text='<%# Eval("Phones[0].PhoneNumber") %>'></asp:Label>
   </ItemTemplate>
</asp:TemplateField>

I need to figure out how to get the home phone number specifically (filter based on phone type) and handle the scenario where the customer does not have a home phone in the database. Right now it's throwing an out of range exception if the customer does not have any phone numbers.

I've tried using the Where operator with a lambda expression to filter the phone type but it doesn't work:

<%# Eval("Phones.Where(p => p.PhoneTypeId == 2).PhoneNumber") %>

Solutions or links to any good articles on the subject would be much appreciated.

Answer 1

Well, I don't know where the phone type is coming, but what I might do is to use a method in code behind to retrieve the phone number you want.

That method should be of type string, here you can check if really exists a phone number for the type of phone number and customer you are suplying.

Something like:

public string GetPhoneNumber(int phoneTypeId, int customerId)
{
    string result = "";
    if (existsPhoneFor(phoneTypeId, customerId)
      result = GetPhoneFor(phoneTypeId, customerId);
    return result;
}

and inside the aspx page:

<%= GetPhoneNumber(p.PhoneTypeId, c.CustomerId) %>

Hope this help.

Answer 2

I haven't used the linqdatasource (always through linq code), but the first I would do is change the expression to:

Phones.Select(p=>p.PhoneNumber).FirstOrDefault(p => p.PhoneTypeId == 2) ")

2 things:

• Where will give you a list, and you are interesting in a single optional result, which is either FirstOrDefault or SingleOrDefault
• If you use only FirstOrDefault and then .PhoneNumber directly, it would fail because of the null reference when no phone is found

I am unsure about this, but you will probably have to change to something like:

<%# ((IQueryable(Phone)) Eval("Phones")).Select...

That said, it would cause a roundtrip for each row. So look into changing the select instead, so it grabs the phone. In that case, you would just bind to the retrieved field. Of course, you would need to use an expression like the one above, but inside the select :)