EntityDataSource Where IN entity sql clause implementation

entitydatasource entity-framework entity-sql

Question

Several values should be sent into a parameter of theEntityDataSource , e.g.:

Where="it.ORDER_ID IN {@OrderIdList}" (This is an EntityDataSource attribute)

<WhereParameters>
    <asp:ControlParameter 
       Name="OrderIdList" Type="Int16" 
       ControlID="OrderFilterControl" PropertyName="OrderIdList" 
    />
</WhereParameters>

This is ineffective asORDER_ID is the kindint32 and I must enter numerous values, such as{1,2,3} etc

The next attempt I made was to set theWhere All of this works properly with the exception of data binding, however.DropDownLists a job. This refers to the fact that the bound dropdownlists in theEntityDataSource Changing Event

My perfect answer would be to employ aWhereParameter on theEntityDataSource however we welcome any assistance. Regards, Tony.


Following is a full code sample utilizing the AdventureWorks database:

Public Class EntityDataSourceWhereInClause
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    CustomersEntityDataSource.Where = WhereClause ''# reset after each postback as its lost otherwise
End Sub

Private Sub cmdFilterCustomers_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdFilterCustomers.Click
    Dim CustomerIdList As New Generic.List(Of Int32)
    For Each item As ListItem In CustomerIdCheckBoxList.Items
        If item.Selected Then
            CustomerIdList.Add(item.Value)
        End If
    Next

    Dim CustomerCsvList As String = String.Join(", ", CustomerIdList.Select(Function(o) o.ToString()).ToArray())
    WhereClause = "it.CustomerID IN {" & CustomerCsvList & "}"

    CustomersEntityDataSource.Where = WhereClause
    FormView1.PageIndex = 0

End Sub

''#  save between postbacks the custom Where IN clause
Public Property WhereClause() As String
    Get
        Return ViewState("WhereClause")
    End Get
    Set(ByVal value As String)
        ViewState.Add("WhereClause", value)
    End Set
End Property

Private Sub CustomersEntityDataSource_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.EntityDataSourceChangingEventArgs) Handles CustomersEntityDataSource.Updating
    Dim c = CType(e.Entity, EntityFrameworkTest.Customer)
    If c.Title.Length = 0 Then
        Response.Write("Title is empty string, so will save like this!")
    End If
End Sub

End Class

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="EntityDataSourceWhereInClause.aspx.vb"
Inherits="EntityFrameworkTest.EntityDataSourceWhereInClause" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
<form id="form1" runat="server">

<%''# filter control %>
<div>
    <asp:EntityDataSource ID="CustomerIdListEntityDataSource" runat="server" ConnectionString="name=AdventureWorksLT2008Entities"
        DefaultContainerName="AdventureWorksLT2008Entities" EnableFlattening="False"
        EntitySetName="Customers" Select="it.[CustomerID]" OrderBy="it.[CustomerID]">
    </asp:EntityDataSource>
    <asp:CheckBoxList ID="CustomerIdCheckBoxList" runat="server" DataSourceID="CustomerIdListEntityDataSource"
        DataTextField="CustomerID" DataValueField="CustomerID" RepeatDirection="Horizontal">
    </asp:CheckBoxList>
    <asp:Button ID="cmdFilterCustomers" runat="server" Text="Apply Filter" />
</div>

<%  
    ''# you get this error passing in CSV in the where clause
    ''# The element type 'Edm.Int32' and the CollectionType 'Transient.collection[Edm.String(Nullable=True,DefaultValue=,MaxLength=,Unicode=,FixedLength=)]' are not compatible. The IN expression only supports entity, primitive, and reference types.  Near WHERE predicate, line 6, column 15. 
    ''# so have coded it manually in code-behind Where="it.CustomerID IN {@OrderIdList}"
%>
<asp:EntityDataSource ID="CustomersEntityDataSource" runat="server" ConnectionString="name=AdventureWorksLT2008Entities"
    DefaultContainerName="AdventureWorksLT2008Entities" EnableFlattening="False"
    EnableUpdate="True" EntitySetName="Customers"
    AutoGenerateOrderByClause="false">
</asp:EntityDataSource>
<%''# updating works with DropDownLists until the Where clause is set in code %>
<asp:FormView ID="FormView1" runat="server" AllowPaging="True" CellPadding="4" DataKeyNames="CustomerID"
    DataSourceID="CustomersEntityDataSource" ForeColor="#333333">
    <EditItemTemplate>
        CustomerID:
        <asp:Label ID="CustomerIDLabel1" runat="server" Text='<%# Eval("CustomerID") %>' />
        <br />
        NameStyle:
        <asp:CheckBox ID="NameStyleCheckBox" runat="server" Checked='<%# Bind("NameStyle") %>' />
        <br />
        Title:
        <%''# the SelectedValue is not Bound to the EF object if the Where clause is updated in code-behind %>
        <asp:DropDownList ID="ddlTitleBound" runat="server" DataSourceID="TitleEntityDataSource"
            DataTextField="Title" DataValueField="Title" AutoPostBack="false" AppendDataBoundItems="true"
            SelectedValue='<%# Bind("Title") %>'>
        </asp:DropDownList>
        <asp:EntityDataSource ID="TitleEntityDataSource" runat="server" ConnectionString="name=AdventureWorksLT2008Entities"
            DefaultContainerName="AdventureWorksLT2008Entities" EnableFlattening="False"
            EntitySetName="Customers" Select="it.[Title]" GroupBy="it.[Title]" ViewStateMode="Enabled">
        </asp:EntityDataSource>
        <br />
        FirstName:
        <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("FirstName") %>' />
        <br />
        MiddleName:
        <asp:TextBox ID="MiddleNameTextBox" runat="server" Text='<%# Bind("MiddleName") %>' />
        <br />
        LastName:
        <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>' />
        <br />
        Suffix:
        <asp:TextBox ID="SuffixTextBox" runat="server" Text='<%# Bind("Suffix") %>' />
        <br />
        CompanyName:
        <asp:TextBox ID="CompanyNameTextBox" runat="server" Text='<%# Bind("CompanyName") %>' />
        <br />
        SalesPerson:
        <asp:TextBox ID="SalesPersonTextBox" runat="server" Text='<%# Bind("SalesPerson") %>' />
        <br />
        EmailAddress:
        <asp:TextBox ID="EmailAddressTextBox" runat="server" Text='<%# Bind("EmailAddress") %>' />
        <br />
        Phone:
        <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />
        <br />
        PasswordHash:
        <asp:TextBox ID="PasswordHashTextBox" runat="server" Text='<%# Bind("PasswordHash") %>' />
        <br />
        PasswordSalt:
        <asp:TextBox ID="PasswordSaltTextBox" runat="server" Text='<%# Bind("PasswordSalt") %>' />
        <br />
        rowguid:
        <asp:TextBox ID="rowguidTextBox" runat="server" Text='<%# Bind("rowguid") %>' />
        <br />
        ModifiedDate:
        <asp:TextBox ID="ModifiedDateTextBox" runat="server" Text='<%# Bind("ModifiedDate") %>' />
        <br />
        <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
            Text="Update" />
        &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False"
            CommandName="Cancel" Text="Cancel" />
    </EditItemTemplate>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <ItemTemplate>
        CustomerID:
        <asp:Label ID="CustomerIDLabel" runat="server" Text='<%# Eval("CustomerID") %>' />
        <br />
        NameStyle:
        <asp:CheckBox ID="NameStyleCheckBox" runat="server" Checked='<%# Bind("NameStyle") %>'
            Enabled="false" />
        <br />
        Title:
        <asp:Label ID="TitleLabel" runat="server" Text='<%# Bind("Title") %>' />
        <br />
        FirstName:
        <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Bind("FirstName") %>' />
        <br />
        MiddleName:
        <asp:Label ID="MiddleNameLabel" runat="server" Text='<%# Bind("MiddleName") %>' />
        <br />
        LastName:
        <asp:Label ID="LastNameLabel" runat="server" Text='<%# Bind("LastName") %>' />
        <br />
        Suffix:
        <asp:Label ID="SuffixLabel" runat="server" Text='<%# Bind("Suffix") %>' />
        <br />
        CompanyName:
        <asp:Label ID="CompanyNameLabel" runat="server" Text='<%# Bind("CompanyName") %>' />
        <br />
        SalesPerson:
        <asp:Label ID="SalesPersonLabel" runat="server" Text='<%# Bind("SalesPerson") %>' />
        <br />
        EmailAddress:
        <asp:Label ID="EmailAddressLabel" runat="server" Text='<%# Bind("EmailAddress") %>' />
        <br />
        Phone:
        <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />
        <br />
        PasswordHash:
        <asp:Label ID="PasswordHashLabel" runat="server" Text='<%# Bind("PasswordHash") %>' />
        <br />
        PasswordSalt:
        <asp:Label ID="PasswordSaltLabel" runat="server" Text='<%# Bind("PasswordSalt") %>' />
        <br />
        rowguid:
        <asp:Label ID="rowguidLabel" runat="server" Text='<%# Bind("rowguid") %>' />
        <br />
        ModifiedDate:
        <asp:Label ID="ModifiedDateLabel" runat="server" Text='<%# Bind("ModifiedDate") %>' />
        <br />
        <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
            Text="Edit" />
    </ItemTemplate>
    <PagerSettings Position="Top" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
</asp:FormView>
</form>

1
4
5/4/2010 9:34:16 PM

Popular Answer

If paging is deactivated and you're still experiencing problems, try this alternative fix:

Private Sub DetailsView1_ModeChanged(sender As Object, e As System.EventArgs) Handles DetailsView1.ModeChanged
        EntityDataSource1.Where = "it.[ID]=" & Me.lstFilter.SelectedValue  ' DetailView.Edit work-around for lost context: Reset bound EntityDataSource.Where
Sub
0
6/17/2011 3:38:02 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow