JOINTURE EXTERNE GAUCHE dans LINQ

Question

Comment effectuer une jointure externe gauche en C#, LINQ to objects, sans l'aide d' join-on-equals-into clauses? Est-il possible de le faire avec whereclause? Corriger le problème: Pour la jointure interne est facile et j'ai une solution à ceci

List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                             select new JoinPair { LeftId = l.Id, RightId = r.Id})

mais pour jointure externe gauche j'ai besoin d'une solution. Le mien est quelque chose comme ça, mais ça ne fonctionne pas

List< JoinPair> leftFinal = (from l in lefts from r in rights
                             select new JoinPair { 
                                            LeftId = l.Id, 
                                            RightId = ((l.Key==r.Key) ? r.Id : 0
                                        })

où JoinPair est une classe:

public class JoinPair { long leftId; long rightId; }

Answer 1

Comme indiqué sur:

101 LINQ Échantillons - jointure externe Gauche

var q =
    from c in categories
    join p in products on c equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

Answer 2

En fait, si vous ne

  from c in categories
    join p in products on c equals p.Category into ps
    from p in ps.DefaultIfEmpty()

c'est assez mal lisible.

Un vrai left join va comme ceci:

from maintable in Repo.T_Whatever 
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

exemple:

var query2 = (
    from users in Repo.T_User
    from mappings in Repo.T_User_Group
         .Where(mapping => mapping.USRGRP_USR == users.USR_ID).DefaultIfEmpty()
    from groups in Repo.T_Group
         .Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP).DefaultIfEmpty()
    //where users.USR_Name.Contains(keyword)
    // //|| mappings.USRGRP_USR.Equals(666)  
    //|| mappings.USRGRP_USR == 666 
    //|| groups.Name.Contains(keyword)

    select new
    {
         UserId = users.USR_ID
        ,UserName = users.USR_User
        ,UserGroupId = mappings.USRGRP_GRP
        ,GroupName = groups.Name
    }

    );


var xy = (query2).ToList();

Ce qui se traduit très bien et très lisible dans

SELECT 
     users.USR_ID AS UserId 
    ,users.USR_User AS UserName 
    ,mappings.USRGRP_GRP AS UserGroupId 
    ,groups.Name AS GroupName 
FROM T_User AS Users

LEFT JOIN T_User_Group AS mappings
   ON mappings.USRGRP_USR = users.USR_ID

LEFT JOIN T_Group AS groups
    ON groups.GRP_ID == mappings.USRGRP_GRP

Omettre le DefaultIfEmpty, et vous avez une jointure interne.

Answer 3

En utilisant la lambda expression

db.Categories    
  .GroupJoin(
      db.Products,
      Category => Category.CategoryId,
      Product => Product.CategoryId,
      (x, y) => new { Category = x, Products = y })
  .SelectMany(
      xy => xy.Products.DefaultIfEmpty(),
      (x, y) => new { Category = x.Category, Product = y })
  .Select(s => new
  {
      CategoryName = s.Category.Name,     
      ProductName = s.Product.Name   
  })

Answer 4

Jetez un oeil à cet exemple. Cette requête devrait fonctionner:

var leftFinal =
        from l in lefts
        join r in rights on l equals r.Left into lrs
        from lr in lrs.DefaultIfEmpty()
        select new { LeftId = l.Id, RightId = ((l.Key==r.Key) ? r.Id : 0 };

Answer 5

Une mise en œuvre de la jointure externe gauche par les méthodes d'extension qui pourrait ressembler à

public static IEnumerable<Result> LeftJoin<TOuter, TInner, TKey, Result>(
  this IEnumerable<TOuter> outer, IEnumerable<TInner> inner
  , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector
  , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> comparer)
  {
    if (outer == null)
      throw new ArgumentException("outer");

    if (inner == null)
      throw new ArgumentException("inner");

    if (outerKeySelector == null)
      throw new ArgumentException("outerKeySelector");

    if (innerKeySelector == null)
      throw new ArgumentException("innerKeySelector");

    if (resultSelector == null)
      throw new ArgumentException("innerKeySelector");

    return LeftJoinImpl(outer, inner, outerKeySelector, innerKeySelector, resultSelector, comparer ?? EqualityComparer<TKey>.Default);
  }

  static IEnumerable<Result> LeftJoinImpl<TOuter, TInner, TKey, Result>(
      IEnumerable<TOuter> outer, IEnumerable<TInner> inner
      , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector
      , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> comparer)
  {
    var innerLookup = inner.ToLookup(innerKeySelector, comparer);

    foreach (var outerElment in outer)
    {
      var outerKey = outerKeySelector(outerElment);
      var innerElements = innerLookup[outerKey];

      if (innerElements.Any())
        foreach (var innerElement in innerElements)
          yield return resultSelector(outerElment, innerElement);
      else
        yield return resultSelector(outerElment, default(TInner));
     }
   }

Le resultselector ensuite a prendre soin des éléments null. Fx.

   static void Main(string[] args)
   {
     var inner = new[] { Tuple.Create(1, "1"), Tuple.Create(2, "2"), Tuple.Create(3, "3") };
     var outer = new[] { Tuple.Create(1, "11"), Tuple.Create(2, "22") };

     var res = outer.LeftJoin(inner, item => item.Item1, item => item.Item1, (it1, it2) =>
     new { Key = it1.Item1, V1 = it1.Item2, V2 = it2 != null ? it2.Item2 : default(string) });

     foreach (var item in res)
       Console.WriteLine(string.Format("{0}, {1}, {2}", item.Key, item.V1, item.V2));
   }