Hi there,

Here's a query for a non-equi left join...

                List<AccountEntity> accounts = (from a in m.Account
                                                from ar in m.AccountReminder.Where(ar => ar.AccountId == a.AccountId &&
                                                                                         ar.Date >= DateTime.Today).DefaultIfEmpty()
                                                select a).ToList();

It gives the following error...

A DefaultIfEmpty() call was found on an entity typed sequence as one side of a join. However, the behavior of the DefaultIfEmpty can't be converted to SQL due to the lack of a predicate or filter to combine left side with right side.

Is it possible to write this Linq query any other way?

Cheers, Ian.

And it wouoldn't complain if you remove DefaultIfEmpty()?

Which runtime library versions are you using (ORMSupport & Linq classes)?

Hi,

The versions are...

Linq: 2.6.10.315 Support: 2.6.10.421

Yes the query runs if DefaultIfEmpty is removed but then it isn't an outer join. I think it would be a non-equi inner join.

I think there is a better way. What is the sql code you want to reproduce/generate exactly?

List<AccountEntity> accounts = (from a in m.Account
                                                join ar in m.AccountReminder.Where(x => x.Date >= DateTime.Today) on a.AccountId equals ar.AccountId into g
                                                from gItem in g.DefaultIfEmpty()
                                                select a).ToList();

This appears to work! But overall this query is a special case of a non-equi left outer join because the join condition still contains an equality between the keys.

I still don't see how one would do the following without a query like the one in the first post...

SELECT *
FROM A
LEFT OUTER JOIN B
ON A.Field1 > B.Field2

That small piece of SQL isn't doable in Linq. The main reason is that the Microsoft designers of Linq didn't design it as a set oriented system but as a series of sequences. 'join' was also not part of Linq till after the first CTP after massive feedback. the major mistake in Linq's join is that it doesn't support a join operator. It's always an equijoin. If you don't want that, you have to define a double from clause with a where, but that leads to a cross-join + where which is less efficient in many cases. Using a DefaultIfEmpty() (which is one of the most stupid constructs ever created in computer programming) you can bend the equ join to a left join (never a right join!)... It's really silly. If they would have allowed things like:

from c in metaData.E1 join o in metaData.E2 on c.SomeField > o.SomeField ...

everything would be great. The expression results to a binary expression and everything is parsable easily.

it's just stupid, I can't declare it otherwise. The where you specify in the join is pulled out of it, otherwise it otherwise the query isn't convertible in all cases.

I think it is doable...

var list1 = new[] { new { Id = 1, Text = "Hello1" }, new { Id = 2, Text = "Hello2" }, new { Id = 3, Text = "Hello3" } };

var q = from i1 in list1
        from i2 in list1.Where(x => i1.Id > x.Id).DefaultIfEmpty()
        select new { i1, i2 };

Can't see how to do a right join though.

Ian wrote:

I think it is doable...

var list1 = new[] { new { Id = 1, Text = "Hello1" }, new { Id = 2, Text = "Hello2" }, new { Id = 3, Text = "Hello3" } };

var q = from i1 in list1
        from i2 in list1.Where(x => i1.Id > x.Id).DefaultIfEmpty()
        select new { i1, i2 };

Can't see how to do a right join though.

I'd be very surprised if that would lead to the sql you specified. But it's been a while, I do recall that I did implement some filter conversion code for double from clauses, however it's a complex issue as it's not always possible to determine whether the where has to go to the outside scope or that it is an on-clause specification.

I'd be very surprised if that would lead to the sql you specified.

Its a very similar query to the one in the first post and that one threw an exception!

Ian wrote:

I'd be very surprised if that would lead to the sql you specified.

Its a very similar query to the one in the first post and that one threw an exception!

yes, it will likely result in the resultset you want, but not in the small SQL query (SELECT .. FROM A LEFT JOIN B ON a.SomeField > b.SomeField) you posted.

My question to you is: can we close this thread, or do you have a question left, as it's a little vague to me if this is solved now (or at least: answered)

Yes I got the query that I was after working. Thanks for the help.