none
Full Outer Joins RRS feed

  • Question

  • Hello,
    how would one go about translating the following to an extension method:
     var query = (from p in dc.GetTable<Person>()
     join pa in dc.GetTable<PersonAddress>() on p.Id equals pa.PersonId and p.Address equals pa.PersonAddress into tempAddresses
     from addresses in tempAddresses.DefaultIfEmpty()
     select new { p.FirstName, p.LastName, addresses.State }).Union(
     from pa2 in dc.GetTable<PersonAddress>()
     join p2 in dc.GetTable<Person>() on pa2.PersonId equals p2.Id and pa.Address equals p.PersonAddress into tempPersons
     from persons in tempPersons.DefaultIfEmpty()
     select new { persons.FirstName, persons.LastName, pa2.State });

    I suppose that would be pretty much the same as
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/8cffc3aa-b90a-43c1-9916-b3af7951c463 with the exception that the join would be done on multiple columns.

    Thanks
    Thursday, October 1, 2009 8:55 PM

Answers

  • Hi mpaulf,

    The thread link in your post is really helpful to perform a full outer join, but it is used in LINQ to Entities instead of LINQ to SQL.  The reason is that LINQ to SQL cannot translate the Union extension method to compare the two elements in the generic class Duple. 

    In LINQ to SQL, we can consider using such extension method to do LEFT OUTER JOIN:

    ==================================================================
        public static class MyExtension

        {

            public static IQueryable<Duple<TOuter, TInner>> MyL2SLeftOuterJoin<TOuter, TInner, TKey>(

                this IQueryable<TOuter> outer,

                IQueryable<TInner> inner,

                Expression<Func<TOuter, TKey>> outerKeySelector,

                Expression<Func<TInner, TKey>> innerKeySelector)

                where TOuter : class

                where TInner : class

            {

                return outer.GroupJoin(inner, outerKeySelector, innerKeySelector, (o, i) => new { Outer = o, Inner = i }).SelectMany(i => i.Inner.DefaultIfEmpty(),

                (x, i) => new Duple<TOuter, TInner>() { Element1 = x.Outer, Element2 = i });

            }

        }
    ==================================================================

    Using this extension method to perform your query:

    ==================================================================
    var query = db.Parents.MyL2SLeftOuterJoin(db.Childs, p => new { ID = p.ParentID, IsActive = p.IsActive }, c => new { ID = c.ChildID, IsActive = c.IsActive }).Select(

                        x => new { ParentName = x.Element1.ParentName, ChildName = x.Element2.ChildName }).Union(

                        db.Childs.MyL2SLeftOuterJoin(db.Parents, c => new { ID = c.ChildID, IsActive = c.IsActive },  p => new { ID = p.ParentID, IsActive = p.IsActive }).Select(

                        y => new { ParentName = y.Element2.ParentName, ChildName = y.Element1.ChildName }));
    ==================================================================

    Important to mention:
    If multiple keys are needed to make JOIN operation, anonymous type is used. 

     

    Besides, in LINQ to Entities, we can directly use the FullOuterJoin extension method like:

    ==================================================================
    var query = context.Parent.FullOuterJoin(context.Child, p => new { ID = p.ParentID, IsActive = p.IsActive }, c => new { ID = c.ChildID, IsActive = c.IsActive });
    ==================================================================

     
    Have a great day!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Thursday, October 8, 2009 1:33 AM
    Wednesday, October 7, 2009 2:23 PM
    Moderator