CODEWITHSUNDEEP
subsonicsubsonic3subsonic-active-record
Sonic Beard
Sonic Beard

Reputation: 525

LINQ Left Join Causes error in SubSonic.Core

I am using the latest version of SubSonic 3 and ActiveRecord. I am trying to do a left join in LINQ. It fails with an error somewhere down in SubSonic.

I have a Vehicle object and a VehicleImage object The Vehicle can have multiple images, but it is not required. so a left join is appropriate.

This is what I have

var vehicle = from v in Vehicle.All()
              join dl in DealerLocation.All() on v.DealerLocationID equals dl.ID
              join vi in VehicleImage.All() on v.ID equals vi.VehicleID into VehicleImages
              from vij in VehicleImages.DefaultIfEmpty()
              && vij.IsPrimary
              select new
              {
                  v, vij.Image
              };

This is the error I get

The expression of type 'System.Linq.IQueryable`1[<>f__AnonymousType1`2[<>f__AnonymousType0`2[Project.Data.Vehicle,Project.Data.DealerLocation],System.Collections.Generic.IEnumerable`1[Project.Data.VehicleImage]]]' is not a sequence

This is the stack trace

   at SubSonic.Linq.Translation.QueryBinder.ConvertToSequence(Expression expr)
   at SubSonic.Linq.Translation.QueryBinder.VisitSequence(Expression source)
   at SubSonic.Linq.Translation.QueryBinder.BindSelectMany(Type resultType, Expression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at SubSonic.Linq.Translation.QueryBinder.VisitMethodCall(MethodCallExpression m)
   at SubSonic.Linq.Structure.ExpressionVisitor.Visit(Expression exp)
   at SubSonic.Linq.Structure.DbExpressionVisitor.Visit(Expression exp)
   at SubSonic.Linq.Translation.QueryBinder.Visit(Expression exp)
   at SubSonic.Linq.Translation.QueryBinder.Bind(QueryMapping mapping, Expression expression)
   at SubSonic.Linq.Structure.QueryMapping.Translate(Expression expression)
   at SubSonic.Linq.Structure.DbQueryProvider.Translate(Expression expression)
   at SubSonic.Linq.Structure.DbQueryProvider.GetExecutionPlan(Expression expression)
   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.SystemCore_EnumerableDebugView`1.get_Items()

Thanks in advance for any insight.

Upvotes: 0

Views: 1014

Answers (3)

Aytek
Aytek

Reputation: 11

Trying Fluent Query can be a solution for now maybe. Something like:

var DB = new myDB();
IList<LiteObject> myLiteObject = DB.Select
    .From<Table1>()
    .InnerJoin<Table2>()
    .LeftOuterJoin<Table3>()
    .Where(Table1.IdColumn).IsEqualTo(1)
    .And(Table2.IdColumn).IsEqualTo(2)
    .ExecuteTypedList<LiteObject>();

where LiteObject includes fields from all tables.

Upvotes: 1

Brian
Brian

Reputation: 4984

It seems there still isn't a fix in place for this. A simple fix (dirty one albeit) is to create a view that handles the left join and fills the empty data from the right with default data and have SubSonic do a simple join on that view.

I know it's terrible but its a fix for now. I couldn't see dropping SubSonic due to this limitation. I'm sure it will be fixed soon.

Upvotes: 1

sparks
sparks

Reputation: 1294

Looks like a SubSonic bug. You might want to post this on SubSonic's github's issues page: http://github.com/subsonic/SubSonic-3.0/issues

Upvotes: 3

Related Questions