Link to home
Start Free TrialLog in
Avatar of Robert Treadwell
Robert TreadwellFlag for United States of America

asked on

vb.net linq concatenate columns lastname suffix

When I add the following line [legalName = If(String.IsNullOrEmpty(b.Suffix), b.FullName, Convert.ToString(b.FullName) & " " & Convert.ToString(b.Suffix)),] to my query I receive the following error.

Exception Details: System.InvalidOperationException: Could not translate expression 'ToString($VB$It1.$VB$It1.b.Suffix)' into SQL and could not treat it as a local expression.

Here is how I place code in my query.
Dim query As Object = (From b In db.ROLCBasicInfos Order By b.LastName, b.FirstName
                                   Join ui In db.ROLCUserInfos On b.RecordID Equals ui.memberID Where ui.memberStatus <> "Discharged"
                                   Join ma In db.ROLCMemberAddresses On b.RecordID Equals ma.memberID
                                   Join ai In db.ROLCAdditionalInfos On b.RecordID Equals ai.memberID
                                   Select b.RecordID,
                            b.LastName,
                            b.FullName,
                            legalName = If(String.IsNullOrEmpty(b.Suffix), b.FullName, Convert.ToString(b.FullName) & " " & Convert.ToString(b.Suffix)),                            
                            ma.fullAddress,
                            workPhone = If(String.IsNullOrEmpty(ma.homePhone), ma.mobilePhone, ma.homePhone),
                            ma.emailAddress,
                            ui.memberStatus,
                            ai.dob)

Open in new window


I'm not sure what is causing the problem.  Logically, it should only concatenate if "Suffix" is not Null or Empty, so converting an empty string should not occur.  But if I'm reading the error correctly, that is what it is saying.  

Am I correct in my understanding?
What am I missing?
How do I correct this issue?
Avatar of Elvio Lujan
Elvio Lujan
Flag of Argentina image

You can't use Convert.ToString in a lambda expression. are FullName and Suffix not of type string?
Is this an Entity Framework or Linq To SQL query? If Entity Framework which version? What version of Visual Studio are you using?
Avatar of Robert Treadwell

ASKER

Full name and Suffix are stored as string variables.
This is a Linq to seql
So then don't use ToString
So you are not using Linq To Entity Framework, correct?
No using Linq To SQL
I'll try without Convert ToString again.  When I return back to office.  Thank you.
This is why I went to the Convert ToString because of this error I received.

System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the add operator.

Not clear on the error.  any help is appreciated.
SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Robert;

Glad you posted your comment because I just tried to reproduce the issue with no luck and was just about to post and ask you to post the schema of the table so I could use the same schema and column types.

Habe a great day.
It provides a link to actual resolution of issue.