Error using QueryExtender with an EntityDataSource - 'FirstName' is not a member of type 'System.Data.Common.DbDatRecord'

Question

The following GridView with an EntityDataSource that grabs 3 fields from the Surveyors table (which contains more fields) works, but of course it shows me every Surveyor.

<asp:GridView ID="gvwSurveyors" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="3" DataSourceID="edsSurveyors">
  <Columns>
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" SortExpression="FirstName" />
    <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" SortExpression="LastName" />
    <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
  </Columns>
</asp:GridView>
<asp:EntityDataSource ID="edsSurveyors" runat="server"
  ConnectionString="name=PLSOEntities"
  DefaultContainerName="PLSOEntities"
  EnableFlattening="False"
  EntitySetName="Surveyors"
  Select="it.[FirstName], it.[LastName], it.[ID]"
  AutoGenerateOrderByClause="true">
  <OrderByParameters>
    <asp:Parameter DefaultValue="LastName" />
  </OrderByParameters>
</asp:EntityDataSource>

So in order to get the initial page load to not show everything I added the OnSelecting event to the EntityDataSource and have it cancel the query if it is Not a postback.

protected void edsSurveyors_Selecting(object sender, EntityDataSourceSelectingEventArgs e) {
  if (!Page.IsPostBack)
    e.Cancel = true;
}

I have two text boxes named txtFirstName and txtLastName that I want the user to be able to search using a SQL query LIKE style. Some reading on the web pointed me toward the QueryExtender. I changed the code to the following:

<asp:GridView ID="gvwSurveyors" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="3" DataSourceID="edsSurveyors">
  <Columns>
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" SortExpression="FirstName" />
      <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" SortExpression="LastName" />
      <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
    </Columns>
  </asp:GridView>
  <asp:EntityDataSource ID="edsSurveyors" runat="server"
    ConnectionString="name=PLSOEntities"
    DefaultContainerName="PLSOEntities"
    EnableFlattening="False"
    EntitySetName="Surveyors"
    Select="it.[FirstName], it.[LastName], it.[ID]"
    AutoGenerateOrderByClause="true" onselecting="edsSurveyors_Selecting">
    <OrderByParameters>
      <asp:Parameter DefaultValue="LastName" />
    </OrderByParameters>
  </asp:EntityDataSource>
  <asp:QueryExtender ID="qexSurveyor" runat="server" TargetControlID="edsSurveyors">
    <asp:SearchExpression SearchType="Contains" DataFields="FirstName">
      <asp:ControlParameter ControlID="txtFirstName" />
    </asp:SearchExpression>
  </asp:QueryExtender>

So now when I click the button I get the error: 'FirstName' is not a member of type 'System.Data.Common.DbDataRecord'

What can I do to allow a Contains? Once it works, I will then added a LastName parameter that does the same.

Answer 1

I also ran into the same thing when using AdventureWorks as a data source. I had to remove the 'Select=' from my EntityDataSource. For a workaround, I went to my GridView and set AutoGenerateColumns="False". I then manually added each column I wanted to display in the Gridview, e.g.

        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
        <asp:BoundField DataField="SalesPerson" HeaderText="Sales Person" />
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />
        <asp:BoundField DataField="CompanyName" HeaderText="Company Name" />
        <asp:BoundField DataField="EmailAddress" HeaderText="Email Address" />
        <asp:BoundField DataField="Phone" HeaderText="Phone" />

As an additional note, it seems another workaround, albeit programatically, is to apply a LINQ Query to the EntityDataSource using the onquerycreated event. In this case, you don't need to declaratively use the Query Extender supposedly. Full Article Here -> http://msdn.microsoft.com/en-us/library/ee404748.aspx