C# 2008 linq to sql paramters

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'?
Note: I am told that the combination of company name, contact name will be distinct enough so I can obtain the database rows I am looking for.
Thus can you tell me how I could use linq to sql to query the database using the company name with the value obtain from the filename where the database value could be different?

Vulpes · Accepted Answer

Again, I don't understand either of those errors.
The String.Trim() method is used in the samples I referred to earlier and, if LINQ to SQL supports it, then it should certainly support TrimStart() and TrimEnd() as it is just a combination of the two.
As I only used these methods on the off chance that the database fields might contain superflous space (probably unlikely), I'd just omit them:
var query = from row in tableContext where System.Data.Linq.SqlClient.SqlMethods.Like(row.CompanyName.ToLower(), company + "%") && System.Data.Linq.SqlClient.SqlMethods.Like(row.ContactName.ToLower(), "%" + surname) select row;

Vulpes · Answer

Although I don't understand why LINQ to SQL should support Trim() but not TrimStart() or TrimEnd(), I'm glad to hear that you've finally found a combination that works :)

dc · Answer

The following sql actually worked: var query = from row in tableContext
where SqlMethods . Like ( row . CompanyName . Trim (). ToLower (), company + "%" )
&& SqlMethods . Like ( row . ContactName . Trim (). ToLower (), "%" + surname )
select row ;
I changed trimstart and trimend to just trim.

dc · Answer

I tried what you said, however I got the error message:
"-  $exception {"Method 'System.String TrimStart(Char[])' has no supported translation to SQL."} System.Exception {System.NotSupportedException}".
To get the statement to compile, I had to use the entire namespace spelled out.
where System.Data.Linq.SqlClient. SqlMethods .Like()
Note:  I do have the following namespace listed at the top: using  S ystem.Data.Linq;
Thus can you tell me what you would suggest next?

Vulpes · Answer

I'm surprised about that since there are even LINQ to SQL samples on MSDN which use the StartsWith and EndsWith methods and they certainly can be translated to SQL using the LIKE operator:
http://msdn.microsoft.com/en-us/vstudio/bb688085.aspx
Anyway, try this query instead:
var query = from row in tableContext where SqlMethods.Like(row.CompanyName.TrimStart().ToLower(), company + "%") && SqlMethods.Like(row.ContactName.TrimEnd().ToLower(), "%" + surname) select row;

dc · Answer

I tried to use the statments, "where row.CompanyName.TrimStart().ToLower().StartsWith(company) && row.ContactName.TrimEnd().ToLower().EndsWith(surname) select row;".
However I obtained an error message saying ".Startswith" and ".EndsWith" does not have an sql equivalent.
Thus can you suggest how to change the linq code?

Vulpes · Answer

I think what I'd do in this situation is the following:
1. Just use 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. 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.
3. I'd ignore case throughout.
So the LINQ to SQL query and preliminary code would then be on the following lines:
// it's assumed that the variables companyName and contactName contain the full names in // the Excel spreadsheet);
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();
var query = from row in tableContext where row.CompanyName.TrimStart().ToLower().StartsWith(company) && row.ContactName.TrimEnd().ToLower().EndsWith(surname) select row;