Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select does not work RSS

• • Reply
  

  pallone

  Member

  165 Points

  160 Posts

  Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select does no...

  Mar 23, 2012 11:09 AM|LINK

  Hi,

  I am trying to limit the number of properties (columns) returned from a LINQ to Entity query so that it only returns the SiteClientID and ClientName since I will use the data to populate a DropDown list box and so do not need all the properties in the object.

  I am getting this error:

  Unable to cast the type 'System.Data.Common.DbDataRecord' to type 'DocketHubTest.SiteClient'. LINQ to Entities only supports casting Entity Data Model primitive types.

  Server Error in '/' Application. QueryCreated event returned a query of type 'ObjectQuery`1' when type 'ObjectQuery`1' is required. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

  Exception Details: System.InvalidOperationException: QueryCreated event returned a query of type 'ObjectQuery`1' when type 'ObjectQuery`1' is required.

  +++++++++++++++++++++++++++++++++++++++

  This is the code I am using:  

  protected void SiteEDS_QueryCreated(object sender, QueryCreatedEventArgs e)  

  {        

      var sites = e.Query.Cast<SiteClient>();         
      e.Query = from c in sites 
      select new SiteClient {
                          SiteClientID = c.SiteClientID, ClientName = c.ClientName
                      };  

  }  

  Could someone please shed some light about what is wrong?  

  I have tried using the EntityDataSource.Select property, like so:

  <asp:EntityDataSource Select="it.[SiteClientID], it.[ClientName]" (other properties...)>

  However, since I have set the EntityDataSource to allow Update and deleteit does not allow me to use the Select statement.

  Select cannot be set if EnableDelete, EnableInsert, or EnableUpdate is enabled.

  Cheers   C

• • Reply
  

  robwscott

  Star

  8079 Points

  1491 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 23, 2012 12:35 PM|LINK

  don't use EntityDS - just bind whenever you need it - less hastle =)

  just populate the dropdownlist when you need it, such as PageLoad or what not

  PageLoad(..)
  {
      if(!Page.IsPostBack)
      {
            this.LoadDDL();
      }
  }
  
  private void LoadDDL()
  {
        using (YourEDMX context = new YourEDMX())
        {
                var query = context.sites.Select(x => new { x.SiteClientID, x.ClientName }).ToList();
                this.DropDownList1.DataSource = query;
                this.DropDownList1.DataTextField = query.ClientName;
                this.DropDownList1.DataValueField = query.SiteClientID;
                this.DropDownList1.DataBind();
         }
  }

  Please post your code for us to help!!
  Mark Answered if it helps - Good luck!
  Cheers!
  Design And Align
  - Rob
  
  • Edited by robwscott on Mar 23, 2012 12:37 PM
• • Reply
  

  pallone

  Member

  165 Points

  160 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 23, 2012 07:43 PM|LINK

  Hi Rob,

  Thanks a lot for your reply and source code.

  It had not occured to me that I could create an anonynous type. However, there are occasions when I think we need to use the EntityDataSoruce so that we can include other objects and then use the navigation properties to fetch more data. 

  Is my assumption right?

  Suppose we have a datagrid and we want to populate it with SiteClientID and ClientName from Sites as before but also want to grab only two properties from the SiteClientMailing (SiteClientMailingID and SiteCleintMailingaName) which is a navigation property.

  Could you please shed some light on how we could achieve that?

  This is not working:

  var query = context.sites.Select(x => new { x.SiteClientID, x.ClientName }).Include("SiteClientMailing").select(m => new{m.SiteClientMailingID, m. SiteCleintMailingaName}).ToList(); 
  

  using (YourEDMX context = new YourEDMX()) 
        { 
                var query = context.sites.Select(x => new { x.SiteClientID, x.ClientName }).Include("SiteClientMailing")ToList(); 
                this.DropDownList1.DataSource = query; 
               this.DataGridDataBind(); 
         } 
   

  Cheers

  C

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 25, 2012 12:58 AM|LINK

  Hello pallone：）

  In my mind，I think you don't need to use Include，if your entity model object really includes the public property of "SiteClientMailingID"……See something like this：

  using (YourEDMX context = new YourEDMX())
        {
                var query = context.sites.Select(x => new {ID= x.SiteClientMailingID, Name = x.SiteClientMailingName}).ToList();
                this.DropDownList1.DataSource = query;
                Dropdownlist1.DataTextField="Name";
                Dropdownlist1.DataValueField="Name";
               this.DataGridDataBind();
         }

• • Reply
  

  pallone

  Member

  165 Points

  160 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 25, 2012 12:01 PM|LINK

  Hi Decker,

  Thanks for the reply.

  Maybe the way I expalained the issue was not clear. I will try again.

  My data grid will display data from 2 tables. Site and SiteClientMailing. (one to many - Site ------> SiteClientMailing)

  From Site I need SiteID and SiteName and from SiteClientMailing I need SiteClientMailingID and SiteClientMailingName.

  The Site and SiteClientMailing objects have many more properties but I want to limit the results to only return those four.

  Your query is only returning SiteClientMailingID and SiteClientMailingName from SiteClientMailing. However, I also need to return the two properties from Site.

  So how do I grab the whole object graph in my query?

  Cheers

  C

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 26, 2012 01:22 AM|LINK

  pallone

  Your query is only returning SiteClientMailingID and SiteClientMailingName from SiteClientMailing. However, I also need to return the two properties from Site.

  Yes，they are two properties that you want in an anoymous class……I don't understand what you really want……

• • Reply
  

  robwscott

  Star

  8079 Points

  1491 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 26, 2012 03:57 AM|LINK

  well you need something to connect the two, i'm assuming it's SiteId = SiteClientMailingId  ????

  therefore, why would you need both SitdID and SiteClientMailingID? basically what connects the two tables??

  var query = (from site in context.site
              join mail in context.siteclientmailing on site.SiteID == mail.SiteClientMailingID
              select new {
                        Id = site.SiteID,
                        SiteName = site.SiteName,
                        MailingName = mail.SiteClientMailingName
              }).ToList();

  Please post your code for us to help!!
  Mark Answered if it helps - Good luck!
  Cheers!
  Design And Align
  - Rob
  
• • Reply
  

  pallone

  Member

  165 Points

  160 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 26, 2012 12:23 PM|LINK

  Thanks Rob,

  Based on your query I created this one that is working:

  DropDownList check = sender as DropDownList;
              var SiteClientID = int.Parse(check.SelectedValue);
  
              var query = (from siteclient in context.SiteClients
                           where siteclient.SiteClientID == SiteClientID
                           from mail in siteclient.SiteClientMailings
                           select new
                           {
                               SiteClientId = siteclient.SiteClientID,
                               SiteClientName = siteclient.ClientName,
                               ClientMailingId = mail.ClientMailingID,
                               ClientName = mail.ClientName,
                               PlannedMailingDate = mail.PlannedMailingDate,
                               MailingStatus = mail.MailingStatus,
                               ActiveFlag = mail.ActiveFlag,
                               TotalPrice = mail.TotalPrice
                           }).AsQueryable();
  
              grdSiteClientMailing.DataSource = query;
              grdSiteClientMailing.DataBind();

   

  However, when I click the edit button I am getting an error that I did not implement the RowEditing event.

  When using the EntityDataSource the edit, delete all work because I think it uses the SaveChanges() method of the EF.

  Do you have any ideas on how to fix this?

   

  Cheers

  C

  • Edited by pallone on Mar 26, 2012 12:27 PM
• • Reply
  

  robwscott

  Star

  8079 Points

  1491 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 26, 2012 12:31 PM|LINK

  i would assume this is your datagrid's row editing event, meaning you do not have one, yet you have an edit button for it. post your aspx and code behind - relating to your datagrid 

  also is there a line number that this breaks on? and if so, could you specify where in the code it is?

  Please post your code for us to help!!
  Mark Answered if it helps - Good luck!
  Cheers!
  Design And Align
  - Rob
  
  • Edited by robwscott on Mar 26, 2012 12:32 PM
• • Reply
  

  pallone

  Member

  165 Points

  160 Posts

  Re: Limit number of properties returned by LINQ to Entity query using EntityDataSoruce Select doe...

  Mar 26, 2012 03:52 PM|LINK

  Hi Rob,

  Here is the full source code.

  Please note that I am trying to use DynamicField for the properties even though I am not using the EntityDataSource for the Grid as you suggested.

  I think the Anonymous type could mess thing up with the Page Init event since I need to set a type for the DataGird. Is there a work around so that I can still use DynamicFields?

  Cheers

  C

   

  public partial class LabellerNoService : System.Web.UI.Page
      {
          private DocketHubEntities context = new DocketHubEntities();
          
          protected void Page_Load(object sender, EventArgs e)
          {
           
          }
  
          protected void Page_Init(object sender, EventArgs e)
          {
              grdSiteClientMailing.EnableDynamicData(typeof(SiteClientMailing));
            
          }
  
        
          protected void dllSite_SelectedIndexChanged(object sender, EventArgs e)
          {
              
              
              
              DropDownList check = sender as DropDownList;
              var SiteClientID = int.Parse(check.SelectedValue);
  
              var query = (from siteclient in context.SiteClients
                           where siteclient.SiteClientID == SiteClientID
                           from mail in siteclient.SiteClientMailings
                           select new
                           {
                               SiteClientId = siteclient.SiteClientID,
                               SiteClientName = siteclient.ClientName,
                               ClientMailingId = mail.ClientMailingID,
                               ClientName = mail.ClientName,
                               PlannedMailingDate = mail.PlannedMailingDate,
                               MailingStatus = mail.MailingStatus,
                               ActiveFlag = mail.ActiveFlag,
                               TotalPrice = mail.TotalPrice
                           }).AsQueryable();
  
              grdSiteClientMailing.DataSource = query;
              grdSiteClientMailing.DataBind();
                   
          }
  
          protected void grdSiteClientMailing_RowEditing(object sender, GridViewEditEventArgs e)
          {
                        
              grdSiteClientMailing.EditIndex = e.NewEditIndex;
             
          }
  
          protected void grdSiteClientMailing_RowDeleting(object sender, GridViewDeleteEventArgs e)
          {
  
          }

  }

  ASPX

  <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
      <asp:EntityDataSource ID="SiteEDS" runat="server" ContextTypeName="DocketHubTest.DocketHubEntities"
          EnableFlattening="false" EntitySetName="SiteClients" 
      Select="it.[SiteClientID], it.[ClientName]" onquerycreated="SiteEDS_QueryCreated"
          
          >
      </asp:EntityDataSource>
      Select a Site:
      <asp:DropDownList ID="dllSite" runat="server" DataSourceID="SiteEDS" DataTextField="ClientName"
          DataValueField="SiteClientID" AutoPostBack="True" 
          onselectedindexchanged="dllSite_SelectedIndexChanged">
      </asp:DropDownList>
      
   
      <br />
      <br />
      Client Mailing
      <asp:UpdatePanel ID="up" runat="server">
          <ContentTemplate>
              <asp:ValidationSummary ID="StudentsValidationSummary" runat="server" ShowSummary="true"
                  DisplayMode="BulletList" Style="color: Red" />
              <asp:GridView ID="grdSiteClientMailing" runat="server" AutoGenerateColumns="False"
                  DataKeyNames="ClientMailingID" 
                  onrowediting="grdSiteClientMailing_RowEditing" onrowdeleting="grdSiteClientMailing_RowDeleting"
                   >
                  <Columns>
                      <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
                      <asp:DynamicField DataField="ClientName" HeaderText="Client Name" ItemStyle-VerticalAlign="Top" />
                      <asp:DynamicField DataField="PlannedMailingDate" HeaderText="Planned Mailing Date"
                          ItemStyle-VerticalAlign="Top" />
                      <asp:DynamicField DataField="MailingStatus" HeaderText="Mailing Status" ItemStyle-VerticalAlign="Top" />
                      <asp:DynamicField DataField="ActiveFlag" HeaderText="ActiveFlag" ItemStyle-VerticalAlign="Top" />
                      <asp:BoundField DataField="TotalPrice" HeaderText="Total Price" />
                  </Columns>
              </asp:GridView>
          </ContentTemplate>
          <Triggers>
              <asp:AsyncPostBackTrigger ControlID="dllSite" EventName="SelectedIndexChanged" />
          </Triggers>
      </asp:UpdatePanel>
  </asp:Content>
  

   

  • Edited by pallone on Mar 26, 2012 03:53 PM
  • Edited by pallone on Mar 26, 2012 03:54 PM
  • Edited by pallone on Mar 26, 2012 03:54 PM