Linq to SQL Join and Contains Operators

Question

in the following query

  var restrictions = from p in dcTrad.quop_restricted_items
                           where p.entry_status == 'P' && p.batch == "PRODUCTION" && p.problem != null
                           from q in dcTrad.model_companies
                           where   q.co_name != null && p.brimsec == q.primary_bsec                            
                           select new { Company = q.co_name, Restriction = p.comment ?? "Restricted without comments", Portfolio = p.problem };

I need to replace

p.brimsec == q.primary_bsec 

with

p.brimsec.StartsWith ( q.primary_bsec  )

but I get the following error:

Only arguments that can be evaluated on the client are supported for the String.StartsWith method

How can I make this work?

Answer 1

Basically the linq to sql does not know how to convert startswith to Sql. This is because internally at run time your linq is code generated to sql.

You may go about achieving this by creating a UDF (user defined function in sql) and using it from your linq statement.

The article is as below: http://msdn.microsoft.com/en-us/library/bb399416.aspx

Andrew