LinqToEntities use less than (<) within LEFT OUTER JOIN

c#, left-join, linq-to-entities, sql

Solution

<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);

Problem

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`

Original source