Search (Filter) with multiple columns using Entity Framework in ASP.Net

sureshMGR
 
on Aug 04, 2020 01:28 AM
Sample_617016.zip
1947 Views

Hi friends,

Need a code in entity framework

I need to ignore some conditions if its null. pls help

sql query

sql=select * from sgs_user_master where territory='All'
if(usergroup!=null)
begin
   sql+=and usergroup='HR'
end
if(rightslevel!=null)
begin
    sql+=and rightslevel='Y'
end

how to write above query in entity framework c#? 

db.sgs_user_master.Where(a => a.usergroup !=null && a.rightslevel == 'Y').ToList();

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 04, 2020 05:36 AM

Hi sureshMGR,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<table>
    <tr>
        <td>Country</td>
        <td><asp:TextBox runat="server" ID="txtCountry" /></td>
    </tr>
    <tr>
        <td>City</td>
        <td><asp:TextBox runat="server" ID="txtCity" /></td>
    </tr>
    <tr>
        <td><asp:Button Text="Search" runat="server" OnClick="OnSearch" /></td>
    </tr>
</table>
<hr />
<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="Id" />
        <asp:BoundField DataField="ContactName" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        NorthwindEntities db = new NorthwindEntities();
        List<Customer> customers = db.Customers.ToList();
        BindCustomerGrid(customers);
    }
}

protected void OnSearch(object sender, EventArgs e)
{
    NorthwindEntities db = new NorthwindEntities();
    List<Customer> customers = db.Customers.ToList();
    if (!string.IsNullOrEmpty(txtCountry.Text.Trim()))
    {
        customers = customers.Where(x => x.Country == txtCountry.Text.Trim()).ToList();
    }
    if (!string.IsNullOrEmpty(txtCity.Text.Trim()))
    {
        customers = customers.Where(x => x.City == txtCity.Text.Trim()).ToList();
    }
    BindCustomerGrid(customers);
}

private void BindCustomerGrid(List<Customer> customers)
{
    gvCustomers.DataSource = customers;
    gvCustomers.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim db As NorthwindEntities = New NorthwindEntities()
        Dim customers As List(Of Customer) = db.Customers.ToList()
        BindCustomerGrid(customers)
    End If
End Sub

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Dim db As NorthwindEntities = New NorthwindEntities()
    Dim customers As List(Of Customer) = db.Customers.ToList()
    If Not String.IsNullOrEmpty(txtCountry.Text.Trim()) Then
        customers = customers.Where(Function(x) x.Country = txtCountry.Text.Trim()).ToList()
    End If

    If Not String.IsNullOrEmpty(txtCity.Text.Trim()) Then
        customers = customers.Where(Function(x) x.City = txtCity.Text.Trim()).ToList()
    End If

    BindCustomerGrid(customers)
End Sub

Private Sub BindCustomerGrid(ByVal customers As List(Of Customer))
    gvCustomers.DataSource = customers
    gvCustomers.DataBind()
End Sub

Screenshot