CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
jibran musa
jibran musa

Reputation: 213

Filtering data by LINQ to SQL

Hi I have a problem while filtering my data.

I have two tables one is the main table where all the data is stored. There is another table where the keywords are stored to filter data from Main table.

The problem i am facing is that i am not able to put up the exact query which is required as the keywords which arrive in the Main Table Data are required to be filtered out.

For example if there are 10 words in Keyword table then Main table data have to be searched thorough all these keywords so that it can be filtered out correctly.

I hope i have made the question clear.

Also i am using LINQ TO SQL solution in it is most welcome

Upvotes: 2

Views: 1929

Answers (2)

Muhammad Umar
Muhammad Umar

Reputation: 3781

Suppose you have two Datatable mainDataTable and keyWordTable. Use linq given below.

var matched = from mainTable in mainDataTable.AsEnumerable()
                      join keyTable in keyWordTable.AsEnumerable() on mainTable.Field<int>("ID") equals keyTable.Field<int>("ID")
                      where !mainTable.Field<string>("Description").Contains(keyTable.Field<string>("KeyWord"))
                      select mainTable;
        if (matched.Count() > 0)
        {
            DataTable finalTable = matched.CopyToDataTable();
        }

Alternate

Also otherway which seems a bit dirty.

List<string> keywordList = new List<string>();
foreach (DataRow row in keyWordTable.Rows)
{
     keywordList.Add(row["KeyWord"].ToString());
}

DataTable finalFilteredTable = mainDataTable.Clone();
bool check = false;
foreach (DataRow row in mainDataTable.Rows)
{
     check = false;
     string description = row["Description"].ToString();

     foreach (string s in keywordList)
     {
          if (description.Contains(s))
          {
              check = true;
              break;
          }
      }

      if (!check)
      {
          finalFilteredTable.ImportRow(row);
      }
}

Upvotes: 2

GrahamHull
GrahamHull

Reputation: 325

Personally I would approach this in a different way.

I would grab the keywords from the table where the keywords are stored. Then iterate through that list and build up a List of your results. 

List<tableName>() values = new List<tableNames>();

//Grab Keywords into a list
List<tableWithKey> tableWithKeyWords  = database.tableWithKey.Where(which ones you need....).ToList();
//go through keyword list and pull back all the matches into a list of lists
for(int i = 0; i < tableWithKeyWords.Count(); i ++)
{
    values.addrange(database.tableName.Where(j => j.KeyWord = tableWithKeyWords[i].keyword).ToList());
}

Upvotes: 1

Related Questions