SubSonic 2.1: Need explanation of SqlQuery.ExecuteJoinedDataSet()

Question

Hi there,

I have a SqlQuery that looks like this:

            SqlQuery query = 
            DB.Select(
               Order.Schema.TableName + ".*",
               OrderDetail.Schema.TableName + ".*")
                .From<Order>()
                    .InnerJoin<OrderDetail>()
            .Where(Order.IdColumn).IsEqualTo(1);

Now I would expect the Method SqlQuery.ExecuteJoindDataSet() to generate a DataSet for me, that contains 2 DataTables (one for Orders, one for OrderDetails) and put a DataRelation into the DataSet, so I don't have to do this all by hand.

But ExecuteJoinedDataSet() only generates one Table containing all the data from Order but not from OrderDetail:

    // Order = 104 Columns
    // OrderDetail = 74 Columns
    query.ExecuteJoinedDataSet().Tables.Count => 1
    query.ExecuteJoinedDataSet().Tables[0].Columns.Count => 104
    query.ExecuteDataSet().Tables[0].Columns.Count => 177

I think I am on the right way, but can someone please tell me, what I am doing wrong?

The purpose of this is that the Printing Component I use in my project does not accept generic objects, but DataSet's as a DataSource.

Answer 1

ExecuteJoinedDataSet actually uses all the table columns from the first table and replaces the value in any column that has a foreign key with the first non-forgeign-key value from the corresponding row in the foreign table. It does inner joins for non-null foreign-key columns, and left joins for nullable ones.

So for this schema

create table tblBaseType
(
id int not null primary key identity(1,1),
name not null varchar(100) unique
)

create table tblBaseLocation
(
id int not null primary key identity(1,1),
name not null varchar(100) unique
)

create table tblBase
(
id int not null primary key identity(1,1),
name varchar(100) not null unique,
baseTypeID int not null references tblBaseType(id),
baseLocationID int null references tblBaseLocation(id)
)

and a SqlQuery like

SqlQuery q = new Select().From(TblBase.Schema).Where(TblBase.IdColumn).IsEqualTo(1);
DataSet ds = q.ExecuteJoinedDataSet();

this approximate sql would be generated:

select tblBase.Id,
tblBase.Name,
tblBaseType.Name as baseTypeId,
tblBaseLocation.name as baseLocationId
from tblBase
inner join tblBaseType on tblBase.baseTypeID = tblBaseType.id
left join tblBaseLocation on tblBase.baseLocationID = tblBaseLocation.id

The actual sql is fully qualified, this is just a rough from-scratch approximation.