Hi all

I've finally got around to testing some of the new querying functionality and have come across a bit of an issue - probably easily sorted, and more likely a problem that exists between the chair and keyboard (i.e. me) but just wanted to check what I'm doing wrong.

The query I'm trying is fairly simple, all works well if I only select one field in my sub-query (FirstName in this case), it fails though if I try to use two fields concatenated together (ala "Select [Firstname] + ' ' + [Surname] As [FullName]") - if I try to produce this it results in incorrectly formed SQL - I've posted both sets of code below - the first works fine, the second fails:

1) Working code

        Dim cust As New CustomerQuery("cust")
        Dim con As New ContactQuery("con")
        Dim per As New PersonQuery("per")
        Dim custcoll As New CustomerCollection

        With cust
            .Select(.CustomerName, .PostCode, con.Email)
            .Select(per.Select(per.FirstName).Where(con.PersonID = per.PersonID).As("Name"))
            .Where(.PostCode.Like("B1%"))
            .InnerJoin(con).On(.CustomerNumber = con.CustomerID)
        End With
        custcoll.Load(cust)

Resulting SQL

SELECT cust.[CustomerName] AS 'CustomerName',cust.[PostCode] AS 'PostCode',con.[Email] AS 'Email', (SELECT per.[FirstName] AS 'FirstName'  FROM [Person] per WHERE con.[PersonID] = per.[PersonID]) AS Name  FROM [Customer] cust INNER JOIN [Contact] con ON cust.[CustomerNumber] = con.[CustomerID] WHERE cust.[PostCode] LIKE @PostCode2

2) Failing code

        Dim cust As New CustomerQuery("cust")
        Dim con As New ContactQuery("con")
        Dim per As New PersonQuery("per")
        Dim custcoll As New CustomerCollection

        With cust
            .Select(.CustomerName, .PostCode, con.Email)
            .Select(per.Select(per.FirstName + " " + per.Surname).Where(con.PersonID = per.PersonID).As("Name"))
            .Where(.PostCode.Like("B1%"))
            .InnerJoin(con).On(.CustomerNumber = con.CustomerID)
        End With
        custcoll.Load(cust)

Resulting SQL

SELECT cust.[CustomerName] AS 'CustomerName',cust.[PostCode] AS 'PostCode',con.[Email] AS 'Email', (SELECT ((per.[FirstName]+' ')+per.[Surname]) AS ''  FROM [Person] per WHERE con.[PersonID] = per.[PersonID]) AS Name  FROM [Customer] cust INNER JOIN [Contact] con ON cust.[CustomerNumber] = con.[CustomerID] WHERE cust.[PostCode] LIKE @PostCode2

As I say, more than likely I'm misunderstanding how to use it but as all of the samples are in C# I don't have a sample to use

Cheers

Martin

Hi

Just thought I'd make it easier to spot the error in the generated SQL - it's the blank/empty alias in the subquery - if I manually added an alias to the generated query then it would work as expected.

Thanks

Martin

OK - solved now, and it's obvious really (whoops!) - I just needed to add an alias to the concatenated fields so this line...

.Select(per.Select(per.FirstName + " " + per.Surname).Where(con.PersonID = per.PersonID).As("Name"))

needed changing to this...

.Select(per.Select((per.FirstName + " " + per.Surname).As("Fullname")).Where(con.PersonID = per.PersonID).As("Name"))

And now all works well - that'll teach me to post before I give myself a proper chance to fix things!

Cheers

Martin