ASP.Net: Search comma separated column in LINQ using C# and VB.Net

Hi nauna,

Please refer below sample.

Database

CREATE TABLE [dbo].[CommaSeperatedData](
	[ID] [int] NOT NULL,
	[ProductType] [varchar](50) NULL,
	[ProductName] [varchar](100) NULL,
 CONSTRAINT [PK_CommaSeperatedData] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[CommaSeperatedData] ([ID], [ProductType], [ProductName]) VALUES (1, N'Fruit', N'Banana, Apple, Orange')
GO
INSERT [dbo].[CommaSeperatedData] ([ID], [ProductType], [ProductName]) VALUES (2, N'Vegetable', N'Potato, Onion, Tomato')
GO
INSERT [dbo].[CommaSeperatedData] ([ID], [ProductType], [ProductName]) VALUES (3, N'Vegetable', N'Onion, Patato')
GO

HTML

<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="OnSearch" />
<hr />
<asp:GridView ID="gvItems" AutoGenerateColumns="false" runat="server">
    <Columns>
        <asp:BoundField DataField="ProductType" HeaderText="Type" />
        <asp:BoundField DataField="ProductName" HeaderText="Name" />
    </Columns>
</asp:GridView>

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGridView();
    }
}

protected void OnSearch(object sender, EventArgs e)
{
    string categoryid = txtSearch.Text.Trim().ToLower();
    this.BindGridView(categoryid);
}

private void BindGridView(string searchTerm = null)
{
    TestEntities db = new TestEntities();
    var q = (from c in db.CommaSeperatedDatas.AsEnumerable()
                select c).ToList();
    if (!string.IsNullOrEmpty(searchTerm))
    {
        q = q.Where(x => x.ProductName.Replace(", ", ",").Replace(" ,", ",").ToLower().Split(',').Contains(searchTerm.ToLower()))
                .Select(x => x).ToList();
    }
    gvItems.DataSource = q;
    gvItems.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGridView()
    End If
End Sub

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Dim categoryid As String = txtSearch.Text.Trim().ToLower()
    Me.BindGridView(categoryid)
End Sub

Private Sub BindGridView(Optional ByVal searchTerm As String = Nothing)
    Dim db As TestEntities = New TestEntities()
    Dim q = (From c In db.CommaSeperatedDatas.AsEnumerable() Select c).ToList()

    If Not String.IsNullOrEmpty(searchTerm) Then
        q = q.Where(Function(x) x.ProductName.Replace(", ", ",").Replace(" ,", ",").ToLower().Split(","c).Contains(searchTerm.ToLower())).[Select](Function(x) x).ToList()
    End If

    gvItems.DataSource = q
    gvItems.DataBind()
End Sub

Screenshot