Issue with Linq Join (subsonic)

Question

I have this slice of code

IQueryable<Dealer> dealers =
    from dealer in Dealers
    join address in AddressesUS on dealer.DealerId equals address.OwnerId
    where dealer.Country == country && address.Owner == (int)Contact.OwnerType.Dealer
    select new Dealer()
    {
        DealerId = dealer.DealerId,
        DealerName = dealer.DealerName,
        Country = dealer.Country,
        Email = dealer.Email,
        Contact = dealer.Contact,
        Url = dealer.Url,
        IsActive = dealer.IsActive,
        IsWholesale = dealer.IsWholesale,
        Address = address == null ? null : address
    };

When I execute it (by calling ToArray or whatever), I get this error: System.InvalidCastException: Invalid cast from 'System.String' to '*.Model.Address'

Now if I change the Dealer.Address to type string, the code fail to compile as I expected becaues it should really be of type AddressUS:Address. However if I change the type of Dealer.Address to object and run the code, I see SubSonic populated it with first string column from DB. I.E. dealer.Address = "123 Fake St." There is no relation in DB for dealer and address so I am not sure if that would make a difference.

I am using .Net 3.5 and MySql 5.

Here is the stack trace

   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType)
   at SubSonic.Extensions.Objects.ChangeTypeTo(Object value, Type conversionType)
   at SubSonic.Extensions.Database.Load[T](IDataReader rdr, T item, List`1 ColumnNames)
   at SubSonic.Extensions.Database.ToEnumerable[T](IDataReader rdr, List`1 ColumnNames)
   at SubSonic.Linq.Structure.DbQueryProvider.Execute[T](QueryCommand`1 query, Object[] paramValues)
   at lambda_method(ExecutionScope )
   at SubSonic.Linq.Structure.DbQueryProvider.Execute(Expression expression)
   at SubSonic.Linq.Structure.QueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
   at SubSonic.Linq.Structure.Query`1.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at InSite.Controllers.DealerController.GetDealers() in ***Controllers\DealerController.cs:line 25
   at lambda_method(ExecutionScope , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

Answer 1

There's a bug in Subsonic ... see this http://stackoverflow.com/questions/3255193/linq-and-subsonic-returning-nested-complex-types for more information.

You can verify that it's the same problem by returning an anonymous type, instead of a Dealer, or by calling ToList() before the Select projection. If it works in those cases, then it's the same bug.