Filter List from comma separated Field using another comma separated string using Linq in ASP.Net

Last Reply 29 days ago By dharmendr

Posted 29 days ago

it is working for me but pls guide me what is the cost of this query if i have more than 1 lac records looping through each record is good idea or not

TimeStamp_DHMS d = new TimeStamp_DHMS();
List<Product> result = new List<Product>();

//first shorlist the record by date 
var searchbydate = (from p in db.TruckPosts
                    where p.searchdate.Contains(TextBox1.Text)
                    select p);

//then loop thur above shortlisted record and search comma seperated 
for (int i = 0; i < txtequipment.Text.Trim().Split(',').Length; i++)
{
    string cat = txtequipment.Text.Trim().Split(',')[i];
    var q = (from p in searchbydate
                where p.TruckType.Contains(cat)
                select new Product
                {
                    ID = p.TruckPostId.ToString(),
                    equipment = p.TruckType,
                    date = p.searchdate,
                    insertdate = p.InsertDate.ToString(),
                    SearchDate = p.searchdate
                }).ToList();

    result.AddRange(q);
}
result = result.GroupBy(p => p.equipment).Select(g => g.FirstOrDefault()).ToList();
//then add the column on run time
var b = result.AsEnumerable().Select(a => new
{

    equipment = a.equipment,
    ID = a.ID,
    AgeResult = d.timecalreturn(a.insertdate.ToString(), DateTime.Now.ToString()),
    SearchDate = a.SearchDate

});


GridView1.DataSource = b;
GridView1.DataBind();

 

Posted 29 days ago Modified on 28 days ago

Hi nauna,

Since you are using same property of the class there is no need to convert to AsEnumerable.

You can assign AgeResult variable inside the select query.

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

SQL

CREATE TABLE ProductTable
(
    ID INT PRIMARY KEY IDENTITY,
    Category VARCHAR(100),
    ProductName VARCHAR(100)
)

INSERT INTO ProductTable VALUES('Fashion, Men, Toys','Product A')
INSERT INTO ProductTable VALUES('Women, Fashion','Product B')
INSERT INTO ProductTable VALUES('Men, Kids','Product C')

HTML

<asp:TextBox runat="server" ID="txtequipment" />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<br /><br />
<asp:GridView runat="server" ID="GridView1" />

Code

C#

protected void Search(object sender, EventArgs e)
{
    ProductEntities db = new ProductEntities();
    string[] input = txtequipment.Text.Trim().Split(',');
    var result = (from p in db.ProductTables
                    where input.Any(c => p.Category.Contains(c))
                    select new Product
                    {
                        ID = p.ID,
                        Catyegory = p.Category,
                        Name = p.ProductName,
                        AgeResult = "15"
                    });
                        
    GridView1.DataSource = result;
    GridView1.DataBind();
}

public class Product
{
    public int ID { get; set; }
    public string Catyegory { get; set; }
    public string Name { get; set; }
    public string AgeResult { get; set; }
}

VB.Net

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim db As ProductEntities = New ProductEntities()
    Dim input As String() = txtequipment.Text.Trim().Split(","c)
    Dim result = (From p In db.ProductTables
                  Where input.Any(Function(c) p.Category.Contains(c))
                  Select New Product With {
                    .ID = p.ID,
                    .Catyegory = p.Category,
                    .Name = p.ProductName,
                    .AgeResult = "15"
                  })
    GridView1.DataSource = result
    GridView1.DataBind()
End Sub

Public Class Product
    Public Property ID As Integer
    Public Property Catyegory As String
    Public Property Name As String
    Public Property AgeResult As String
End Class

Screenshot