Subsonic Join issue

Question

For those of you who are decent with subsonic!

        TblNewsCollection col =
            new Select().From(Tables.TblNews)
                .InnerJoin(Tables.TblContent)
                .Paged(currentPage, pageSize)
                .OrderDesc(TblContent.Columns.PubDate)
                .ExecuteAsCollection<TblNewsCollection>();

The above works, no problem, but when I try to add a where clause

        TblNewsCollection col =
            new Select().From(Tables.TblNews)
                .InnerJoin(Tables.TblContent)
                .Where(TblContent.Columns.UserFK)
                .IsEqualTo(guidUserFK)
                .Paged(currentPage, pageSize)
                .OrderDesc(TblContent.Columns.PubDate)
                .ExecuteAsCollection<TblNewsCollection>();

I get this message

System.InvalidCastException: Object must implement IConvertible. at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) System.InvalidCastException: Failed to convert parameter value from a Guid to a String.

I've tried it from other fields, for example a bit field in a database it says it can't convert from bool to bit!

Seems to only be an issue on where statements after joins

Thanks for help in advance

Answer 1

Northwind.CustomerCollection customersByCategory = new Select() .From(Northwind.Customer.Schema) .InnerJoin(Northwind.Order.Schema) .InnerJoin(Northwind.OrderDetail.OrderIDColumn, Northwind.Order.OrderIDColumn) .InnerJoin(Northwind.Product.ProductIDColumn, Northwind.OrderDetail.ProductIDColumn) .Where("CategoryID").IsEqualTo(5) .ExecuteAsCollection();

there's an example that supposedly works, if that helps anyone help me!

Answer 2

Not sure if this helps

link text

Answer 3

I found that joins work better using the TableColumnSchema as in the above Northwind example as opposed to the column name.