How to perform a table UNION in SubSonic

subsonic

I have a query that performs a UNION between two tables. I'm trying to implement the same thing with SubSonic. I have two related questions:

What is the SubSonic syntax to perform a union.
How do I rename a column. Assuming the UNION will require to match the column names between the two tables

Best Answer

There isn't a UNION syntax in SubSonic. You could use an inline query as dicussed here e.g.

MyTableCollection MyTable = new InlineQuery().ExecuteAsCollection<MyTableCollection>(
    “SELECT Col1,Col2 FROM TableA Union SELECT Col1,Col2 FROM TableB");

The best solution I've found is to implement the UNION inside a view and then get SubSonic to generate a model for that view instead.

Related Solutions

Subsonic : Same column name different tables

What version are you using? In 2.2 You can use the TableColumn object to get around this (it may be the same for 2.1 as well. So instead of using the struct, as you're doing, you can use the object (Table2.SidColumn).

If push comes to shove - you can override everything with a string - so in your case you could use "Table1.Sid" and "Table2.Sid" right in the Where() method.

R – Search query with Subsonic

First question:

return new SubSonic.Select().From(Visit.Schema)
        .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
        .Where(InfopathArchive.XmlDocColumn).Like("%" + keywords + "%")
        .ExecuteTypedList<Visit>();

Second question:

Pass a List of words in your query to a function that builds a SubSonic query as follows

SqlQuery query = DB.Select().From(Visit.Schema)
        .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
        .Where("1=1");

foreach(string wordInQuery in wordsInQueryList)
{
  query = query.And(InfopathArchive.XmlDocColumn).Like("%" + wordInQuery + "%")
}

return query.ExecuteTypedList<Visit>();

Obviously this is untested but it should point you in the right direction.

Best Answer

Related Solutions

Subsonic : Same column name different tables

R – Search query with Subsonic

Related Topic