Comment implémenter la clause EntityDataSource Where de l'entité SQL

entitydatasource entity-framework entity-sql

Question

Je veux passer un certain nombre de valeurs dans un paramètre de EntityDataSource , par exemple:

Where="it.ORDER_ID IN {@OrderIdList}" (il s'agit d'une propriété de EntityDataSource)

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

Cela ne fonctionne pas car ORDER_ID est de type int32 et je dois passer plusieurs valeurs, par exemple {1,2,3} etc.

La chose suivante que j'ai essayée était de définir la clause Where dans code-behind et tout cela fonctionne, sauf que je ne parviens pas à obtenir la liaison de données sur DropDownLists . J'entends par là qu'aucune valeur n'est renvoyée par les EntityDataSource déroulantes liées dans l' EntityDataSource mise à jour EntityDataSource .

Ma solution idéale serait d’utiliser un WhereParameter sur EntityDataSource mais toute aide est la EntityDataSource . Merci Tony.


Un exemple de code complet suit à l’aide de la base de données AdventureWorks:

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>

Réponse populaire

Solution de rechange ci-dessous, si la pagination est désactivée et que le problème persiste:

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


Related

Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow