Simulating an outer non-equijoin in LINQ.

[Tom Bombadil]

Question: Can an outer non-equijoin be simulated using LINQ?

It's quite unfortunate that LINQ doesn't support literal joins based on
anything other than equality. I know that this the most common scenario, and
although the efficiency of other types of joins is often debatable, they are
often darned useful. In T-SQL I have dozens of such useful queries with
joins based on such operations as prefix or suffix comparisons (using the
LIKE operator) and even bitmask comparisons.

In LINQ, one can clearly achieve the following:
(a) An outer equality-based join can be simulated using the "join" operator
in conjunction with the DefaultIfEmpty() method.

(b) One can simulate an inner join based on something other than equality,
for example:

string[] words ={"walking,"bouncing","bounced","bounce","talked","running"};

string[] suffixes = {"ing","ed","er","iest"};

....and we wish to return all words paired with their suffixes, we could use:
var pairs = from a in words from b in suffixes where a.EndsWith(b) select
new {a, b};

- but we'd only get an inner join. The word "bounce" would not be returned.
(In an outer join "bounce" would be returned paired with a null.)

Is there any way, in LINQ, of at least simulating an outer join based on
something other than equality?

 

[Jon Skeet [C# MVP]]

Question: Can an outer non-equijoin be simulated using LINQ?

<snip>

Can you say *exactly* what you'd want the sequence of results to be?
If you could put it together in a LINQ to Objects short but complete
program, that would be even better.

I'm sure it's possible, but:

1) Using the standard query operators it's probably ugly
2) You could write your own extensions to make it more pleasant in
LINQ to Objects
3) I've no idea whether the standard query operators version would
work in LINQ to SQL

When you've specified exactly what you want the results to be (and
when I'm at home) I'll have a look.

(My guess is that you want all the matching suffixes, or null if there
aren't any, that using a "group by" and then DefaultIfEmpty() would
work - that's a way of doing something approaching an outer join.)

Jon

 

[Tom Bombadil]

In the example given what I would want to retrieve from query would be:

{ a = walking, b = ing }
{ a = walked, b = ed }
{ a = bouncing, b = ing }
{ a = bounced, b = ed }
{ a = bounce, b = (null) }
{ a = talked, b = ed }
{ a = running, b = ing }

This is typical outer join behavior - all parent records are returned even
if there is no match, in which case any selected child fields are returned as
nulls.

 

[Jon Skeet [C# MVP]]

In the example given what I would want to retrieve from query would be:

{ a = walking, b = ing }
{ a = walked, b = ed }
{ a = bouncing, b = ing }
{ a = bounced, b = ed }
{ a = bounce, b = (null) }
{ a = talked, b = ed }
{ a = running, b = ing }

This is typical outer join behavior - all parent records are returned even
if there is no match, in which case any selected child fields are returned as
nulls.

Okay - will look at it later. Definitely doable.

 

[Jon Skeet [C# MVP]]

In the example given what I would want to retrieve from query would be:

{ a = walking, b = ing }
{ a = walked, b = ed }
{ a = bouncing, b = ing }
{ a = bounced, b = ed }
{ a = bounce, b = (null) }
{ a = talked, b = ed }
{ a = running, b = ing }

This is typical outer join behavior - all parent records are returned even
if there is no match, in which case any selected child fields are returned as
nulls.

Done:

using System;
using System.Collections.Generic;
using System.Linq;

class Test
{
static void Main()
{
string[] words = {"walking","bouncing","bounced",
"bounce","talked","running"};
string[] suffixes = {"ing","ed","er","iest"};

var query = from word in words
from suffix in suffixes.Where(x =>word.EndsWith(x))
.DefaultIfEmpty()
select new { a = word, b = suffix };

foreach (var result in query)
{
Console.WriteLine (result);
}
}
}

 

[Tom Bombadil]

Thanks. That does, in fact, do the trick.
It does answer my question as a basic proof-of-concept, and it's an example
that's missing from the current literature.

Now a more difficult practicality arises when attempting this particular
sort of join between a local object (as parent) and a SQL table (as child).
When such an attempt is made, the following exception is generated: "Only
arguments that can be evaluated on the client are supported for the
String.EndsWith method." An attempt to use the
not-actually-supported-for-direct-use SqlMethods.Like() static method almost
does the trick, but not quite due to null-object issues...I'll keep working
on it.

Thanks again.

In the example given what I would want to retrieve from query would be:

{ a = walking, b = ing }
{ a = walked, b = ed }
{ a = bouncing, b = ing }
{ a = bounced, b = ed }
{ a = bounce, b = (null) }
{ a = talked, b = ed }
{ a = running, b = ing }

This is typical outer join behavior - all parent records are returned even
if there is no match, in which case any selected child fields are returned as
nulls.

Done:

using System;
using System.Collections.Generic;
using System.Linq;

class Test
{
static void Main()
{
string[] words = {"walking","bouncing","bounced",
"bounce","talked","running"};
string[] suffixes = {"ing","ed","er","iest"};

var query = from word in words
from suffix in suffixes.Where(x =>word.EndsWith(x))
.DefaultIfEmpty()
select new { a = word, b = suffix };

foreach (var result in query)
{
Console.WriteLine (result);
}
}
}

 

[Jon Skeet [C# MVP]]

Thanks. That does, in fact, do the trick.
It does answer my question as a basic proof-of-concept, and it's an example
that's missing from the current literature.

Now a more difficult practicality arises when attempting this particular
sort of join between a local object (as parent) and a SQL table (as child).
When such an attempt is made, the following exception is generated: "Only
arguments that can be evaluated on the client are supported for the
String.EndsWith method." An attempt to use the
not-actually-supported-for-direct-use SqlMethods.Like() static method almost
does the trick, but not quite due to null-object issues...I'll keep working
on it.

Hmm... that suggests that you can't use String.EndsWith for two fields
even outside a join, for instance:

from user in users
where user.FullName.EndsWith(user.Surname)
select user;

Does that work?

 

[Tom Bombadil]

Indeed, as it turns out that is a problem a rather didn't expect. I can
almost understand the problem with mixed data sources, but I have would
expected that in a pure LINQ-toSQL scenario a SELECT with a server-side LIKE
clause would have been generated.

The previously-mentioned SqlClient.SqlMethods.Like() method actually works
in that case, though.

I wonder if LINQ-to-Entities (which I don't have installed) would have this
problem.

 

[Jon Skeet [C# MVP]]

Indeed, as it turns out that is a problem a rather didn't expect. I can
almost understand the problem with mixed data sources, but I have would
expected that in a pure LINQ-toSQL scenario a SELECT with a server-side LIKE
clause would have been generated.

Indeed. Admittedly I can't remember using such a thing myself, but I
can well believe that it's useful

The previously-mentioned SqlClient.SqlMethods.Like() method actually works
in that case, though.

I wonder if LINQ-to-Entities (which I don't have installed) would have this
problem.

I haven't installed it yet either... but it'll be interesting to see.

 

[Frans Bouma [C# MVP]]

Thanks. That does, in fact, do the trick.
It does answer my question as a basic proof-of-concept, and it's an
example that's missing from the current literature.

Now a more difficult practicality arises when attempting this
particular sort of join between a local object (as parent) and a SQL
table (as child). When such an attempt is made, the following
exception is generated: "Only arguments that can be evaluated on the
client are supported for the String.EndsWith method." An attempt to
use the not-actually-supported-for-direct-use SqlMethods.Like()
static method almost does the trick, but not quite due to null-object
issues...I'll keep working on it.

That's not possible. What you need in that case is either:
1) a temptable in where you insert the in-memory objects, then join
with that temptable and select from that join
or
2) use an IN query (.Contains(set)) with PK values and filter on the
child's fk field values.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------