Query Concatenated Field (using SubSonic)

Question

Is there a way to query against a concatenated field using MS SQL? For instance, what I want to do is something like:

Select FirstName+' '+LastName as FullName from Attendees where FullName like '%Joe Schmoe%'

The above doesn't work. What I have found works is:

Select * from Attendee where FirstName+' '+LastName like '%Joe Schmoe%'

but I can't figure out how to do that using a SubSonic SqlQuery. I have a number of joins and OR statements added dynamically that I don't want to have to write out the sql manually.

Any help/ideas?

Answer 1

how about this:

SELECT FullName 
FROM (SELECT FirstName+' '+LastName as FullName 
      FROM Attendees) X
WHERE FullName LIKE '%Joe Schmoe%'

Answer 2

You can just specify your condition in the Where part of your query as follows:

List<Attendee> cardHolders = new YourDB()
  .Select
  .From<Attendee>()
  .Where(AttendeeTable.FirstNameColumn + " + ' ' + " + AttendeeTable.SurnameColumn + " AS FullName")
  .Like("%Joe Schmoe%")
  .ExecuteTypedList<Attendee>();

Answer 3

It seems it isn't possible in SubSonic