Adding a custom function in the linq statement

Question

Hi folks
I want to search in keywords field like search key in the set.
e.g. my key is "Wing" keywords is "Wing Dress Others" with spaces what should I write instead it ?
Error : Method 'Boolean Compare(System.String, System.String)' has no supported translation to SQL.

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString.HasKeys())
    {
        DbDataContext db = new DbDataContext();
        var Query = from n in db.Products
                    where Compare(n.Keywords, Request.QueryString["key"])
                    select n;
        DataList1.DataSource = Query;
        DataList1.DataBind();
    }
}

bool Compare(string keywords,string key)
{
    string[] items = keywords.Split(' ');
    foreach (string item in items)
        if (item.Equals(key)) return true;
    return false;
}

Answer 1

Similar que/ans : Custom Method in LINQ to SQL query

Check this full article : What is and what isn't possible with linq

Following is not possible

// function used in filter
static bool MyFunc(Nwind.Product p)
{
  return p.ProductName.StartsWith("B");
}
// query that uses MyFunc
var q = 
  from p in db.Products
  where MyPriceFunc(p.UnitPrice) > 30m
  select p

It compiles with no errors, but when you execute it LINQ to SQL throws an exception saying: "Static method System.Boolean MyTest(LINQTest.Nwind.Product) has no supported translation to SQL."

The exception is actually thrown when you try to fetch results from q (for example using the foreach statement), because LINQ to SQL attempts to convert the expression trees to T-SQL only when the results are needed and the query must be executed.

To fix the example you can simply copy the code that checks whether product name starts with "B" to the where clause of the query and it would work fine.

Answer 2

In this case, in stead of your self made Compare you could use Contains, which checks if a string is in a string. this does work for LINQ

example:

    var Query = from n in db.Products
                where n.Keywords.Contains(Request.QueryString["key"])
                select n;

Contains works for arrays as well.