Performing 1-to-1 Left Outer Join in LINQ using Lambda expression

Question

I have the below LINQ query:

dbContext.NAVSummaries
        .Join(dbContext.NAVSummaries.DefaultIfEmpty(),
                current => new
                {
                    current.Portfolio,
                    PD = SqlFunctions.DatePart("dw", current.ValueDate) == 2 ? DbFunctions.AddDays(current.ValueDate, -3).Value :
                            SqlFunctions.DatePart("dw", current.ValueDate) == 1 ? DbFunctions.AddDays(current.ValueDate, -2).Value :
                                                                                DbFunctions.AddDays(current.ValueDate, -1).Value
                },
                previous => previous == null ? null : new { previous.Portfolio, PD = previous.ValueDate },
                (outer, inner) => new { outer, inner }
        )
        .Where(n => !dateStart.HasValue || n.outer.ValueDate.CompareTo(dateStart.Value) >= 0)
        .Where(n => !dateEnd.HasValue || n.outer.ValueDate.CompareTo(dateEnd.Value) <= 0)

There are x number of records for a given ValueDate, and x-1 number of records on it's previous business date. The NAVSummaries DbSet is self-joined on the ValueDate column with the ValueDate item matching its previous business date. However, the output results a count of only x-1. I wish to perform a left-outer join so that all the x records of the left table/collection are returned.

EDIT: There will be only one record/item matching to the previous business date. So it has to be kind of One-To-One mapping.

NavSummary Entity :

public class NAVSummary
    {
        [Key, Column(Order = 0)]
        public string Portfolio { get; set; }
        [Key, Column(Order = 2)]
        public DateTime ValueDate { get; set; }
        public decimal BackOfficeNAV { get; set; }
        public decimal FrontOfficeNAV { get; set; }
        public decimal DifferencePercent { get; set; }
        public decimal Threshold { get; set; }
        public int ExtractId { get; set; }
        public string ExtractStatus { get; set; }
        public string PortfolioOwner { get; set; }
        public DateTime DateTimeModified { get; set; }
        public int MostCorrectNAV { get; set; }
        public virtual IList<NAVComment> Comments { get; set; }
        public virtual IList<NAVStatus> Statuses { get; set; }
        public virtual IList<NAVExtract> Extracts { get; set; }
        [JsonIgnore]
        [NotMapped]
        public bool IsChange { get; set; }
        [NotMapped]
        public decimal DayOverDayChange { get; set; }
        [JsonIgnore]
        [NotMapped]
        public DateTime PreviousValueDate { get; set; }
        [JsonIgnore]
        [NotMapped]
        public decimal PreviousDP { get; set; }
    }

it is a bit confusing, why you are trying to do with LEFT JOIN, inside of an entity, can you show how you want the result of this query? — Lucian Bumb, Mar 15 '16 at 18:58
possible duplicate: http://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods — Jakotheshadows, Mar 15 '16 at 19:04

score 1 · Accepted Answer · answered Mar 15 '16 at 19:31

When dealing with complex queries including joins, group by etc., I would suggest you using the LINQ query syntax for most of the parts, since it's more natural and readable due to transparent identifiers and let expressions. The different type joins are described here join clause (C# Reference).

Here is your query using the query syntax:

var query =
    from current in db.NAVSummaries
    let currentWD = SqlFunctions.DatePart("dw", current.ValueDate)
    let currentPD = DbFunctions.AddDays(current.ValueDate, currentWD == 2 ? -3 : currentWD == 1 ? -2 : -1).Value
    join previous in db.NAVSummaries
    on new { current.Portfolio, PD = currentPD }
    equals new { previous.Portfolio, PD = previous.ValueDate }
    into previousGroup
    from previous in previousGroup.DefaultIfEmpty() // LEFT OUTER JOIN
    select new { outer = current, inner = previous };
if (dateStart.HasValue)
    query = query.Where(e => e.outer.ValueDate >= dateStart.Value);
if (dateEnd.HasValue)
    query = query.Where(e => e.outer.ValueDate <= dateEnd.Value);

thanks for rewriting the query for me. that worked :) – Lucifer Mar 17 '16 at 19:05 — Lucifer, Mar 17 '16 at 19:05

Performing 1-to-1 Left Outer Join in LINQ using Lambda expression

1 Answers1