LInq to sql trimend' not working

Question

After I parse out a company name and contact name from an excel spreadsheet 2010, I need to query a sql server 2008 r2 database, to obtain more information that is needed for processing. My problem is the  name and/or contact name that is obtain from the file name, may be different than the actual value in the database.

For example the company name obtained from the file name may look like:
'Blue group' however the actual file company name in the database may be
'Blue (of northeast) group' or 'Blue inc'?

Thus my solution is:

1. I am using the first word of the company name (or the first and second words if the first word is 'The') to search for the company in the database.

2.I am assuming the contact name will be something like 'George Washington' or 'Ulysses S Grant', I'd just search for the last word (i.e. the surname) in the database.

I have obtained the information I am looking for using the following logic:

string company = companyName.TrimStart().Split(' ')[0].ToLower();
if (company == 'the') company += " " + companyName.Split(' ')[1].ToLower();
string[] items = contactName.TrimEnd().Split(' ');
string surname = items[items.Length - 1].ToLower();

I have tried the following 2 linqs queries but I am getting errors:

1. var query = from row in tableContext where row.CompanyName.TrimStart().ToLower().StartsWith(company) && row.ContactName.TrimEnd().ToLower().EndsWith(surname) select row;

2. var query = from row in tableContext where SqlMethods.Like(row.CompanyName.TrimStart().ToLower(), company + "%") && SqlMethods.Like(row.ContactName.TrimEnd().ToLower(), "%" + surname) select row;

Thus can you tell me show me some code on what you would change here in the linq to sql code?

Answer 1

What are the errors you are getting?

LS

---

Lloyd Sheen

Answer 2

Here are the two errors I am getting:

1.  var query = from row in tableContext where row.CompanyName.TrimStart().ToLower().StartsWith(company) && row.ContactName.TrimEnd().ToLower().EndsWith(surname) select row;

I get the error message:  ".Startswith" and ".EndsWith" does not have an sql equivalent.

2. 2. var query = from row in tableContext where SqlMethods.Like(row.CompanyName.TrimStart().ToLower(), company + "%") && SqlMethods.Like(row.ContactName.TrimEnd().ToLower(), "%" + surname) select row;

I get the error message:

$exception {"Method 'System.String TrimStart(Char[])' has no supported translation to SQL."} System.Exception {System.NotSupportedException}".

Answer 3

The errors you are getting are because Linq attempts to translate your Linq statement to a T-SQL statement.  Since there is no equivalent of TrimEnd you are getting the error.  The errors which indicate .Startswith and .EndsWith as error are just because of the first error.

To solve this you need to refactor your code.  First create a variable which will hold the row.ContactName.TrimEnd().  Then use the variable in your Linq statement and the errors should go away.  The same thing applies for the TrimStart.

Hope this helps

LS

---

Lloyd Sheen

Answer 4

Hi wendy;

I think that this should work out for you. In place of TrimStart and TrimEnd just use Trim which is supported.

var query = from row in tableContext 
            where SqlMethods.Like(row.CompanyName.Trim().ToLower(), company + "%") 
                  && SqlMethods.Like(row.ContactName.Trim().ToLower(), "%" + surname) 
            select row;

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".