none
Using an EntityDataSource with Include and Select

    Question

  • I have the following EntityDataSource control...

    <asp:EntityDataSource ID="edsPhotographs" runat="server" ConnectionString="name=SJGEntities"
        DefaultContainerName="SJGEntities" EnableFlattening="False" EntitySetName="GalleryAssignments"
        AutoGenerateWhereClause="True" Include="Photograph">
        <WhereParameters>
          <asp:QueryStringParameter Type="Int32" Name="GalleryID" QueryStringField="id" />
        </WhereParameters>
      </asp:EntityDataSource>

     

    I'd like to limit the fields returned but if I include the following...

     

    Select="it.[GalleryID],it.[PhotographID]"

     

    for example I get an error...

    The result type of the query is neither an EntityType nor a CollectionType with an entity element type. An Include path can only be specified for a query with one of these result types.

    Can I use a select expression to narrow the returned fields if using an include? I'd also need a way to reference and select a field from the included table such as it.Photograph.[Reference]

    Tuesday, May 11, 2010 1:37 PM

Answers

  • This is not a possible combination of options for the EntityDataSource. You can use Select to limit the properties you are returning, but you cannot then also use Include to include related entities.

    What you can do instead is to specify an Entity SQL command in the "CommandText" property of the EntityDataSource control. For example, here is an Entity SQL command to select from the Orders table and filter the results and also include the Order's Customer:

    select it.OrderID, it.CustomerID, it.Customer from [Orders] as it

    You can data bind to the it.OrderID and it.CustomerID properties, but you will need to set up your own data binding to the properties in the Customer (for example Customer.CompanyName) because the '.' syntax is not supported out of the box.

    Jeff


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 11, 2010 4:01 PM
    Moderator

All replies

  • This is not a possible combination of options for the EntityDataSource. You can use Select to limit the properties you are returning, but you cannot then also use Include to include related entities.

    What you can do instead is to specify an Entity SQL command in the "CommandText" property of the EntityDataSource control. For example, here is an Entity SQL command to select from the Orders table and filter the results and also include the Order's Customer:

    select it.OrderID, it.CustomerID, it.Customer from [Orders] as it

    You can data bind to the it.OrderID and it.CustomerID properties, but you will need to set up your own data binding to the properties in the Customer (for example Customer.CompanyName) because the '.' syntax is not supported out of the box.

    Jeff


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 11, 2010 4:01 PM
    Moderator
  • Thanks for your reply Jeff. I'll give your suggestion a try or possibly stick with the include and allow all fields to be returned.

    Alternatively I have been toying with the idea of not using the entitydatasource but instead creating a linq to entities query to return the results in code and then programatically binding this to the repeater. Are there any pros / cons of this over using an entitydatasource?

    Wednesday, May 12, 2010 2:58 PM
  • I typically use ObjectDataSource and encapsulate the query inside of a class to give it a better abstraction but if u are writing your query in a code behind then u might as well use the EntityDataSource. What Jeff mentioned about the Include is not a limitation of the EntityDataSource control but infact how linq to entiities and esql query operates. Here are few rules to know.

    1. Include must be the last operation on the query. Any includes inside the query could be silently omitted.

    2. Include does not work over projections.

    Some of these limitations if you think about it makes sense. If you are returning an anonymous type, there is really nothing to include because Include eagerly loads a navigation property on an entity but an anonymous type does not have any navigation property that u can call Include upon.

     

    Zeeshan

    Thursday, May 13, 2010 5:28 AM
  • Has this changed for EF5? I simply want to combine two fields from two related tables into a single column to bind. 

    [Edit: Ahh, I'm stupid - you don't need to specify the "Include" statement to still combine fields from another table.

    (I just deleted the include statement and used it.[RelatedTable].Field in the Select statement and it worked).]


    • Edited by Peter_D503 Thursday, December 06, 2012 2:21 AM
    Thursday, December 06, 2012 2:10 AM