Robert Treadwell
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.FullNam e) & " " & Convert.ToString(b.Suffix) ),] to my query I receive the following error.
Exception Details: System.InvalidOperationExc eption: 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.
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?
Exception Details: System.InvalidOperationExc
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)
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?
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?
ASKER
Full name and Suffix are stored as string variables.
This is a Linq to seql
This is a Linq to seql
So then don't use ToString
So you are not using Linq To Entity Framework, correct?
ASKER
No using Linq To SQL
ASKER
I'll try without Convert ToString again. When I return back to office. Thank you.
ASKER
This is why I went to the Convert ToString because of this error I received.
System.Data.SqlClient.SqlE xception: The data types text and nvarchar are incompatible in the add operator.
Not clear on the error. any help is appreciated.
System.Data.SqlClient.SqlE
Not clear on the error. any help is appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
It provides a link to actual resolution of issue.