Return all records with select new statement in ASP.Net using Linq query

Hi sureshMGR,

You are using Linq query with Lambda expression.

Instead of using both use simple Lambda expression to select the desired column.

Also the function return type should be TypeList instead of tbl.

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

<asp:TextBox ID="txtCountry" runat="server" />
<asp:Button Text="Search" runat="server" OnClick="OnSearch" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false"
    AllowPaging="true" PageSize="5" OnPageIndexChanging="OnPageIndexChanging">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
    </Columns>
</asp:GridView>

Code

C#

public List<CustomerList> GetCustomers(string country)
{
    using (NorthwindEntities entity = new NorthwindEntities())
    {
        var t = entity.Customers
            .Where(a => a.Country == country).OrderBy(a => a.CustomerID)
            .Select(x => new CustomerList
            {
                Id = x.CustomerID,
                Name = x.ContactName.Trim(),
                City = x.City.Trim()
            }).ToList();
        return t;
    }
}

protected void OnSearch(object sender, EventArgs e)
{
    gvCustomers.DataSource = GetCustomers(txtCountry.Text.Trim());
    gvCustomers.DataBind();
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    gvCustomers.DataSource = GetCustomers(txtCountry.Text.Trim());
    gvCustomers.DataBind();
}

public class CustomerList
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
}

VB.Net

Public Function GetCustomers(ByVal country As String) As List(Of CustomerList)
    Using entity As NorthwindEntities = New NorthwindEntities()
        Dim t = entity.Customers _
        .Where(Function(a) a.Country = country) _
        .OrderBy(Function(a) a.CustomerID) _
        .Select(Function(x) New CustomerList With {
            .Id = x.CustomerID,
            .Name = x.ContactName.Trim(),
            .City = x.City.Trim()
        }).ToList()
        Return t
    End Using
End Function

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    gvCustomers.DataSource = GetCustomers(txtCountry.Text.Trim())
    gvCustomers.DataBind()
End Sub

Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    gvCustomers.DataSource = GetCustomers(txtCountry.Text.Trim())
    gvCustomers.DataBind()
End Sub

Public Class CustomerList
    Public Property Id As String
    Public Property Name As String
    Public Property City As String
End Class

Screenshot