1

I am trying to build the following query:

`new Select("GTekst = ArrGruppe.Tekst", "GLTekst = ArrGruppeLinie.Tekst")
.From(ArrGruppeLinie.Schema)
.InnerJoin(ArrGruppe.IdColumn, ArrGruppeLinie.ArrGruppeIDColumn)
.Where(ArrDeltager.Columns.Kategori).IsLessThan(20)
.And("Arrgruppe.Tekst").Like("mytext");`

It generates a flawed query because of the .And() because I have aliases attached on the two columns with the same name - the And-operator is here:

... AND (ArrGruppe.Tekst LIKE @ArrGruppe.Tekst1
)',N'@Kategori0 tinyint,@ArrGruppe.Tekst1 varchar(10)',@Kategori0=20,@ArrGruppe.Tekst1='mytext'

I haven't been able to find anything on Google which could solve this problem. How do I write the Subsonic query to generate a valid SQL parameter for ArrGruppe.Tekst ??

EDIT: Problem was solved with an update from 2.1 Final to version 2.2.

flowerpowerdad
  • 1,247
  • 1
  • 9
  • 17

1 Answers1

1
new Select("GTekst = ArrGruppe.Tekst", "GLTekst = ArrGruppeLinie.Tekst")
  .From(ArrGruppeLinie.Schema)
  .InnerJoin(ArrGruppe.Columns.Id, ArrGruppeLinie.Columns.ArrGruppeID)
  .Where(ArrDeltager.Columns.Kategori).IsLessThan(20)
  .And(Arrgruppe.Columns.Tekst).Like("mytext");

If not try upgrading to the latest version of SubSonic http://code.google.com/p/subsonicproject/downloads/list because you may be hitting the following issue (fixed in 2.2)

Google Issue 31 - Where Expression not formatting correctly with qualified column name

Adam Cooper
  • 8,077
  • 2
  • 33
  • 51
  • I have a situation where I have two tables with matching column names "ArrGruppe.Tekst" and "ArrGruppeLinie.Tekst" that is. The question is: How to write a SubSonic query in which I generate aliases for the column names to make some valid SQL - and in the same query create an AND-filter based on one of the columns I've just created an alias for? I tried your approach - the above generates the following: AND Tekst LIKE @Tekst1 ...which is invalid because Tekst is an ambiguous column because there are two of them. Please write again if you need further information :o) – flowerpowerdad May 12 '09 at 11:16
  • I've edited the query above so it should fix the ambiguous column name error. Let me know if it doesn't – Adam Cooper May 12 '09 at 11:55
  • Sorry. It generates the following AND-clause: ...AND ArrGruppe.Tekst LIKE @ArrGruppe.Tekst1 which is invalid because you can't have a dot in the parameter name. – flowerpowerdad May 12 '09 at 12:11
  • That is very strange, which version of SubSonic are you using? – Adam Cooper May 12 '09 at 19:05
  • 2.1 Final... Should I file a bug report on subsonicproject.com? – flowerpowerdad May 12 '09 at 20:11
  • Try 2.2 and see if you get the same problem. http://code.google.com/p/subsonicproject/downloads/list – Adam Cooper May 13 '09 at 08:53
  • Works like a charm - I can see in the release notes that there has been an issue with aliases on WHERE so I suppose it has been fixed between 2.1 and 2.2. Thanks for helping me out on this one - I really appreciate it :o) – flowerpowerdad May 13 '09 at 09:25