order datatable by relevance using linq by column values which are comma sperated

Question

I have a Datatable that contains a column called Tags, Tags can have values such as

row[0] = Tag1
row[1] = Tag1, Tag2
row[2] = Tag2, Tag3
row[3] = Tag1, Tag2, Tag3
row[4] = Tag4, Tag6

and are seperated by comma's

etc..

I have the value of Tags for the current document and have run a query to select all other documents that have either of the Tags in there row. for example lets say the current document Tags are (Tag1, Tag2, Tag3)

so from the example rows above all the rows above are returned apart from row[4]

Here is the bit i'm lost with, i now want to sort the datatable by how many tags are matched with the current document. so for the example i've talked about so far they should be ordered

row[3] = Tag1, Tag2, Tag3
row[1] = Tag1, Tag2
row[2] = Tag2, Tag3
row[0] = Tag1

Not used linq before but was told it could do this.

so far i have

var query = from c in dt.AsEnumerable()
                    orderby c.Field<string>("Tags").CompareTo(dr["Tags"]) ascending
                    select c;

Answer 1

I think something like this will fit the bill. Try it out:

// Here, I construct a simple table for demonstration
var table = new DataTable();
var column = table.Columns.Add("Tags", typeof(string));
table.Rows.Add("Tag1");
table.Rows.Add("Tag1, Tag2");
table.Rows.Add("Tag2, Tag3");
table.Rows.Add("Tag1, Tag2, Tag3");
table.Rows.Add("Tag4, Tag6");

// The separator is convenient for using the string.Split override
// that strips empty results
var separator = new[] { ",", " " };

// For the demo, we'll sort by number of tags matching the third row
var current = table.Rows[2];

// This splits the string into an array for convenient processing later
var currenttags = current.Field<string>("Tags")
                         .Split(separator, StringSplitOptions.RemoveEmptyEntries);

// The query splits out each tags field into an array convenient for processing,
// counts the number of tags contained in the currenttags array,
// sorts, and then selects the entire row.
var query = from row in table.AsEnumerable()
            let tags = row.Field<string>("Tags")
                          .Split(separator, StringSplitOptions.RemoveEmptyEntries)
            let count = tags.Count(t => currenttags.Contains(t))
            orderby count descending
            select row;

Answer 2

Use the intersect method. here is a sample

//test data
DataTable table = new DataTable();
        var column = table.Columns.Add("Tags", typeof(string));
        table.Rows.Add("Tag1");
        table.Rows.Add("Tag1,Tag2");
        table.Rows.Add("Tag2,Tag3");
        table.Rows.Add("Tag1,Tag2,Tag3");
        table.Rows.Add("Tag4,Tag6");
        string[] currentTags = new string[] { "Tag1", "Tag2", "Tag3" };

//actual code
        var a = from row in table.AsEnumerable()
                let cData = (row["Tags"] as string).Split(new char[] { ',' }).Intersect(currentTags)
                orderby cData.Count() descending
                select cData;

Answer 3

string[] Tags = dr["Tags"].ToString().Split(new char[] { ',' }); string SqlClause = "";

        for (int i = 0; i < Tags.Length; i++)
        {
            if (i != Tags.Length - 1)
            {
                SqlClause += "Tags LIKE '%" + Tags[i] + "%' OR ";
            }
            else
            {
                SqlClause += "Tags LIKE '%" + Tags[i] + "%'";
            }
        }

        DataTable dt = ArticleCollection(SqlClause);

        var seperator = new[] { ",", " " };
        var current = dr["Tags"].ToString();
        var currenttags = dr.Field<string>("Tags").Split(seperator, StringSplitOptions.RemoveEmptyEntries);

        DataTable query = (from row in dt.AsEnumerable()
                    let tags = row.Field<string>("Tags").Split(seperator, StringSplitOptions.RemoveEmptyEntries)
                    let count = tags.Count(t => currenttags.Contains(t))
                    orderby count descending
                    select row).CopyToDataTable();

        for (int i = 0; i < query.Rows.Count; i++)
        {
            if (query.Rows[i]["Title"].ToString() == dr["Title"].ToString())
            {
                query.Rows[i].Delete();
            }
        }

        TagsRepeater.DataSource = query;
        TagsRepeater.DataBind();

    }
}

DataTable ArticleCollection(string whereClause)
{

    DataSet ds = TreeHelper.SelectNodes("/%", false, "CriticalCare.Conclusion;CriticalCare.Literature;CriticalCare.Theory", whereClause, " ", -1, true);
    DataTable dt = new DataTable();

    if (!DataHelper.DataSourceIsEmpty(ds))
    {            
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            dt.Merge(ds.Tables[i]);
        }
        return dt;
    }
    return null;
}

}

also what i want to do is if more than one article matches the same amount of tags, is order them by there order in relation to the current document in the site tree. which bit of linq would i do that with? is it easy to do that in the same query? is there away of adding a count column to the datatable?