LINQ to SQL query in C#

Question

I am a beginner with C# /LINQ query - I have below query. Right outer join works fine in Simple SQL, however, I am unable to implement this in LINQ, but not getting anywhere.

SELECT bt.PosGUID,s.PosGUID
    FROM tblSchedule s right Join tblTrade bt
        ON s.PosGUID = bt.PosGUID  AND  RowType = '4'

SELECT bt.PosGUID,s.PosGUID
    FROM  tblTrade bt left Join tblSchedule s 
        ON s.PosGUID = bt.PosGUID  AND  RowType = '4'

I need to understand what is the best way to the above left outer join, I guess right outer join is not possible, hence converted to left join and trying to implement.

Something like - seems to be bit complex query :

        var tQuery = from bt in m_DataContext.tblTrades
                     join bPos in m_DataContext.tblBigPositionStatics on
                         new { bt.PosGUID } equals
                         new { bPos.PosGUID }

                     join bo in m_DataContext.tblBigOrders
                         on new { bt.ClOrdID, bt.PosGUID } equals new { bo.ClOrdID, bo.PosGUID }

                     join tradingAcc in m_DataContext.tblTradingAccounts
                         on new { Entity = bPos.PosEntity, Account = bPos.PosAccount } equals
                         new { tradingAcc.Entity, tradingAcc.Account }

                     join btRef in m_DataContext.tblTrades.DefaultIfEmpty()
                         on new { bt.PosGUID, ExecID = bt.ExecRefID } equals new { btRef.PosGUID, btRef.ExecID }
                         into temp
                     from btref in temp.DefaultIfEmpty()

                     join desk in m_DataContext.tblDesks
                     on bt.PosDeskGUID equals desk.GUID

                      // JOIN not working not briging back all records from  TBLTrades
                     join ss in m_DataContext.tblSchedules on bt.PosGUID equals ss.PosGUID into temp1
                     from ss in temp1.DefaultIfEmpty()


                     where bt.CreateDateTime >= dateToRun.getDate(false)
                     && bt.CreateDateTime < dateToRun.getDate(false).AddDays(1)
                     && bo.AsOfDateTime.Date == bt.AsOfDateTime.Date
                     && bPos.HardDeleteDate == null
                      && ss.RowType == "4"


                     //&& !"1".Equals(bt.ExecTransType)
                     //&& bt.HasBeenCorrected == false
                     && deskGuidList.Contains(desk.GUID)


                     select new { bt, bo, desk, bPos, tradingAcc, btref,ss };

Answer 1

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,
           ...
       }
    })