LinqToEntities use less than (<) within LEFT OUTER JOIN

Question

I'm working with a colleague and we're trying to reproduce the following SQL Query using LinqToEntities (Entity Framework v4.0)

SELECT t1.*
FROM [dbo].LocaleStringResource AS t1
  LEFT OUTER JOIN [dbo].LocaleStringResource AS t2
    ON (t1.ResourceName = t2.ResourceName AND t1.AccountId < t2.AccountId)
WHERE t2.ResourceName IS NULL;

Basically it's saying only return us the highest AccountIds where there is more than one matching ResourceName. We have got this so far...

localeStringResource = from e1 in localeStringResource
  join e2 in localeStringResource
  on new { e1.ResourceName, e1.AccountId } equals new { e2.ResourceName, e2.AccountId }
  where e2.ResourceName == null
  select e1;

But we don't seem to be able to work out how to achieve the equivalent less than (<) within the LEFT OUTER JOIN?

ON (t1.ResourceName = t2.ResourceName AND t1.AccountId < t2.AccountId)

Could anyone please advise if this is possible or if we're barking up the wrong tree? We have attempted to make the initial query as simple as possible hoping it will make the equivalent LinqToEntities query easier to build.

Note:

AccountId is not unique. We have an Identity Id column on the LocalResourceString table. However there is a Unique Constraint on both AccountId and ResourceName

It looks like what you really want to do is select the records that have the largest `AccountId` for each `ResourceName`. Is that correct? — Thom Smith, Oct 08 '12 at 14:54

Thom Smith · Accepted Answer · 2012-10-08T14:35:59.710

<EDIT> It looks like what you really want to do is select the records that have the largest AccountId for each ResourceName. If we assume that AccountId is unique, then:

localeStringResource =
    from e1 in localeStringResource
    group e1 by e1.ResourceName into grp
    select grp.Max(e => e.AccountID);

</EDIT>

You have a left outer join here, so your LINQ is going to need a GroupJoin-SelectMany-DefaultIfEmpty construct. Join and GroupJoin are restructed to equality, but you can add the remainder of the condition later, before the DefaultIfEmpty:

localeStringResource =
    from e1 in localeStringResource
    join e2inner in localeStringResource
        on e1.ResourceName equals e2inner.ResourceName
        into grp
    from e2 in grp
        .Where(e => e1.AccountId < e.AccountId)
        .DefaultIfEmpty()
    where e2.ResourceName == null
    select e1;

Here's the method syntax, for reference. I used Tuple to pass the data around:

localeStringResource = localeStringResource
    .GroupJoin(
        localeStringResource,
        e1 => e1.ResourceName,
        e2 => e2.ResourceName,
        Tuple.Create)
    .SelectMany(pair => pair.Item2
        .Where(e2 => pair.Item1.AccountID < e2.AccountID)
        .DefaultIfEmpty()
        .Select( e2 => Tuple.Create(pair.Item1, e2)))
    .Where(pair => pair.Item2.ResourceName == null)
    .Select(pair => pair.Item1);

Thanks Thom I think your original answer may be the ticket, although as mentioned in your EDIT our AccountId's aren't unique (this is a lookup up table, I have amended the question to add more details about the fields). I am writing a test now to see what we get back. — Lloyd Holman, Oct 08 '12 at 14:48
The edit should work if the pair {`AccountId`, `ResourceName`} is unique. There could be an issue if there were multiple distinct records with the same `AccountId` and `ResourceName`, in which case the shortened query would only return one of them. It doesn't look like that is an issue here. — Thom Smith, Oct 08 '12 at 14:52

score -1 · Answer 2 · answered Oct 08 '12 at 14:17

-1

Try using the ANSI-82 syntax putting the join criteria in the where clause:

localeStringResource = from e1 in localeStringResource
                       from e2 in localeStringResource
                       where e1.ResourceName = e2.ResourceName &&
                            e1.AccountId < e2.AccountId
                       select e1;

answered Oct 08 '12 at 14:17

Jim Wooley

10,169
1
25
43

This will perform an inner join, not a left join. – Thom Smith Oct 08 '12 at 14:27
Thanks Jim but we require to filter by `ResourceName == null` so we only return those rows that we have multiple values for. – Lloyd Holman Oct 08 '12 at 14:46

LinqToEntities use less than (<) within LEFT OUTER JOIN

2 Answers2