Subsonic 3.0 Left Join

Question

Trying to do a left join in subsonic using linq but it doesn't seem to work, I get a big error.

I'm pretty sure the query is correct as I've done it a few times with objects and Linq2Sql.

            var post = from p in Post.All()
                        join q in Quote.All() on p.ID equals q.PostID into pq
                        where p.ID == id.Value
                        from qt in pq.DefaultIfEmpty()
                        select new {p, qt};

It just seems subsonic isn't able to generate the required SQL from left join linq queries.

Am I doing something wrong here? Is there a work around?

Update: I'm using subsonic 3.0.0.2 here is the error I get when I try a left join with subsonic

Expression of type 'System.Collections.Generic.IEnumerable1[GetAQuote.Post]' cannot be used for parameter of type 'System.Linq.IQueryable1[GetAQuote.Post]' of method 'System.Linq.IQueryable1[<>f__AnonymousType22[GetAQuote.Post,System.Collections.Generic.IEnumerable1[GetAQuote.Quote]]] GroupJoin[Post,Quote,Int32,<>f__AnonymousType22](System.Linq.IQueryable1[GetAQuote.Post], System.Collections.Generic.IEnumerable1[GetAQuote.Quote], System.Linq.Expressions.Expression1[System.Func2[GetAQuote.Post,System.Int32]], System.Linq.Expressions.Expression1[System.Func2[GetAQuote.Quote,System.Int32]], System.Linq.Expressions.Expression1[System.Func3[GetAQuote.Post,System.Collections.Generic.IEnumerable1[GetAQuote.Quote],<>f__AnonymousType22[GetAQuote.Post,System.Collections.Generic.IEnumerable`1[GetAQuote.Quote]]]])'

Answer 1

I think its a bug and not going to be supported by Subsonic. I had the same issue when doing other things here

Answer 2

t 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.

Answer 3

Reviving an old topic here, but for those that come searching along later, there is a different syntax that seems to work correctly in SubSonic3 for a left outer join.

Instead of the Linq expression from the original post:

        var post = from p in Post.All()
                    join q in Quote.All() on p.ID equals q.PostID into pq
                    where p.ID == id.Value
                    from qt in pq.DefaultIfEmpty()
                    select new {p, qt};

Rewrite it to:

        var post = from p in Post.All()
                    from q in Quote.All().Where(x => x.PostID == p.ID).DefaultIfEmpty()
                    where p.ID == id.Value
                    select new {p, q};

Hopefully this helps someone!

Answer 4

I have a fork for left join that I'll be pulling in the next few days - gimme a week and I'll push another release with this.