[Solved]-LINQ to SQL query in C#-LINQ,C#

if you want to do a right outer join of table a and b, simply exchange these tables and you can do a left outer join.

a left outer join is a groupjoin followed by a selectmany. in my experience i use the groupjoin far more often than i use the left outer join. especially in one-to-many relations.

for example: suppose you have a table of schools and a table of students. every school has zero or more students, every student studies at exactly one school, using foreign key schoolid: a straightforward one-to-many relation.

give me all schools with all their students

var result = dbcontext.schools
    .groupjoin(dbcontext.students,          // groupjoin schools and students
    school => school.id,                    // from every school take the primary key
    student => student.schoolid,            // from every student take the foreign key
    (school, studentsonthisschool) => new   // from every school with all its students
    {                                       // make one new object

        // select only the school properties i plan to use
        id = schoolid,
        name = school.name,

        olderstudents = studentsonthisschool

            .select(student => new
            {
                // select only the student properties i plan to use:
                id = student.id,
                name = student.name,
                ...

                // not needed, i already know the value:
                // schoolid = student.schoolid,
            });

the result will be a sequence like:

school 1 with students a, b, c, d.
school 2 with students e, f,
school 3 without any students
school 4 with students g, h, i,
...

this seems to me much more useful than the result of the left outer join:

school 1 with student a,
school 2 with student e,
school 3 with null student,
school 1 with student b,
school 2 with student f,
school 1 with student c,
...

but hey, it's your choice.

i have a tbltrade, which contains trades. every trade has at least properties bt, posguid and rowtype. i also have a tblsechedule which contains schedules. every schedule has at least properties bt and posguid. give me all trades with rowtype 4 with all zero or more schedules that have the same value for posguid.

var result = tbltrade
    // keep only the trades that have rowtype equal to 4:
    .where(trade => trade.rowtype == 4)

    // do the groupjoin:
    .groupjoin(tblschedule,
         trade => trade.posguid,
         schedule => schedule.posguid,
         (trade, scheduleswithsameposguid) => new
         {
             // select the trade properties you plan to use:
             tradeid = trade.id,
             posguid = trade.posguid,
             ...

             schedules = scheduleswithsameposguid.select(schedule => new
             {
                  // select the schedule properties you plan to use:
                  id = schedule.id,
                  ...

                  // not needed, you already know the value:
                  // posguid = schedule.posguid.
              })
              .tolist(),
         });

if you really want a flat left outer join, add a selectmany:

.selectmany(groupjoinresult.schedules,
(trade, schedule) => new
{
    posguid = trade.posguid,

    // the trade properties:
    trade = new
    {
        id = trade.tradeid,
        ...
    },

    schedule = new
    {
        id = schedule.id,
        ...
    },
});

if you want, you can create an extension function leftouterjoin:

public static class myqueryableextensions
{
    // version without equalitycomparer:
    public static iqueryable<tresult> leftouterjoin<t1, t2, tkey, tresult>(
       this iqueryable<t1> source1,
       iqueryable<t2> source2,
       func<t1, tkey> key1selector,
       func<t2, tkey> key2selector,
       func<t1, t2, tresult) resultselector)
    {
        return leftouterjoin(source1, source2,
            key1selector, key2selector, resultselector, null);
    }

version with equalitycomparer:

public static iqueryable<tresult> leftouterjoin<t1, t2, tkey, tresult>(
   this iqueryable<t1> source1,
   iqueryable<t2> source2,
   func<t1, tkey> key1selector,
   func<t2, tkey> key2selector,
   func<t1, t2, tresult) resultselector,
   iequalitycomparer<tkey> comparer)
{
    if (comparer == null) comparer = equalitycomparer<tkey>.default;

    // groupjoin followed by selectmany:
    return groupjoin(source1, source2, key1selector, key2selector,
        (source1item1, source2itemswithsamekey) => new
        {
            source1item = source1item,
            source2items = source2itemswithsamekey,
        })
        .selectmany(groupjoinresult => groupjoinresult.source2items,
           (groupjoinresult, source2item) =>
               resultselector(groupjoinresult.source1item, source2item));

    }
}

usage:

var result = tbltrade
    .where(trade => trade.rowtype == 4)
    .leftouterjoin(tblschedule,
    trade => trade.posguid,
    schedule => schedule.posguid,
    (trade, schedule) => new
    {
       // select the trade and schedule properties that you plan to use
       // for example the complete trade and schedule:
       trade = trade,
       schedule = schedule,

       // or only some properties:
       commonposguid = trade.posguid,
       trade = new
       {
           id = trade.id,
           ...
       }

       schedule = new
       {
           id = trade.id,
           ...
       }
    })