Forum guidelines
AuthorQuestion
C# 2008 linq to sql paramters
Posted on: 05 Jan 2013
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?

AuthorReply
Re: C# 2008 linq to sql paramters
Posted on: 06 Jan 2013  
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;

dc
  • 0
  • 0
Re: C# 2008 linq to sql paramters
Posted on: 07 Jan 2013  
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?

Re: C# 2008 linq to sql paramters
Posted on: 07 Jan 2013  
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
  • 0
  • 0
Re: C# 2008 linq to sql paramters
Posted on: 08 Jan 2013  
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  System.Data.Linq;


Thus can you tell me what you would suggest next?




Vulpes
  • 0
  • 0
accepted
Re: C# 2008 linq to sql paramters
Posted on: 08 Jan 2013   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;


dc
  • 0
  • 0
Re: C# 2008 linq to sql paramters
Posted on: 10 Jan 2013  
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.


Re: C# 2008 linq to sql paramters
Posted on: 10 Jan 2013  
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 :)

SPONSORED BY

Custom Software Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.