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

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; }
    }

Answers


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);

Need Your Help

How to avoid Foreign Keys constraints for all tables in DB truncate?

sql sql-server

for designing purposes i need to truncate all DB which has lots of FK's. I cannot use DELETE command simply because some tables set with Identity of TinyInts and contain about 150 items.

Efficient way to Handle ResultSet in Java

java hashmap resultset sqlresultsetmapping

I'm using a ResultSet in Java, and am not sure how to properly close it. I'm considering using the ResultSet to construct a HashMap and then closing the ResultSet after that. Is this HashMap techni...