IF ELSE condition in SELECT statement of LINQ query with DATEADD() logic

Question

UPDATED : I am trying to convert below SQL logic to C# code using LINQ and I have few confusion on how to handle CASE WHEN and ELSE conditions inside of SELECT statement and DATEADD fuction.

WITH tmp AS (
    SELECT  
    ID, 
    StartDate AS ReportingDate, 
    IncrementYears, 
    IncrementMonths,
     IncrementDays,
      DATEADD(YEAR, 1, GETDATE()) AS EndDate
    FROM    TransactionDetailDateFrequency
    UNION ALL
    SELECT  tmp.ID,
            CASE WHEN 
            tmp.IncrementDays > 0 
            THEN DATEADD(YEAR,tmp.IncrementYears,DATEADD(MONTH,tmp.IncrementMonths,DATEADD(DAY,tmp.IncrementDays,tmp.ReportingDate)))
            ELSE 
            EOMONTH(DATEADD(YEAR,tmp.IncrementYears,DATEADD(MONTH,tmp.IncrementMonths,tmp.ReportingDate))) 
            END AS ReportingDate,
            tmp.IncrementYears, 
            tmp.IncrementMonths, 
            tmp.IncrementDays, 
            tmp.EndDate
    FROM    tmp
    WHERE   tmp.ReportingDate < tmp.EndDate) 

    select * from tmp
    OPTION (MAXRECURSION 0)

I have converted this so far,

var calculatedDate = DateTime.Now.AddYears(1);

        var items = (from TDD in IngestionHubContext.TransactionDetailDateFrequency
                     select new { TDD.Id, ReportingDate = TDD.StartDate, TDD.IncrementYears, TDD.IncrementMonths, TDD.IncrementDays, EndDate = calculatedDate });

        var Temp = items.Concat(from T in items
                                where T.ReportingDate < T.EndDate
                                select new
                                {
                                    T.Id,
                                    ReportingDate = T.IncrementDays > 0 ? T.ReportingDate.Value.AddYears(T.IncrementYears.Value).AddMonths(T.IncrementMonths.Value).AddDays(T.IncrementDays.Value):
                                     T.ReportingDate.Value.AddYears(T.IncrementYears.Value).AddMonths(T.IncrementMonths.Value),
                                    T.IncrementYears,
                                    T.IncrementMonths,
                                    T.IncrementDays,
                                    T.EndDate
                                });

Could someone please guide how I can implement EOMONTH() logic in c# inside a LINQ

UPDATED : I have added the Linq query above but I get a error at items.Concat - 'does not contain a definition for concat and the best extension method overload'ParallelEnumerable.Concat<>'' : RESOLVED

Also any tips to get EOMONTH equivalent in C#

Any help is appreciated.

Thanks in advance.

score 2 · Answer 1 · answered Feb 28 '19 at 12:04

2

What you'd want to do is easier to understand how it's possible to perform your CASE with a little different formatting of your statement. All you need for that piece is to use a ternary operator. Utilizing the DATEADD functions requires some native functions built into Entity Framework >= 6.0.

(from TDD in IngestionHubContext.TransactionDetailDateFrequency
select new { 
    TDD.Id, 
    CalculatedDate = TDD.IncrementDays > 0 ?
        DbFunctions.AddYears(
            DbFunctions.AddMonths(
                DbFunctions.AddDays(TDD.ReportingDate, 
                                    TDD.IncrementDays), 
                TDD.IncrementMonths), 
            TDD.IncrementYears) :
        DbFunctions.AddYears(
            DbFunctions.AddMonths(TDD.ReportingDate,
                                  TDD.IncrementMonths),
            TDD.IncrementYears),
    ReportingDate = TDD.StartDate, 
    TDD.IncrementYears, 
    TDD.IncrementMonths, 
    TDD.IncrementDays, 
    EndDate = calculatedDate 
});

answered Feb 28 '19 at 12:04

krillgar

12,596
6
50
86

I am using EF core 2.2 and the DBFunctions does not contain a definition for AddYears()/AddMonths/AddDays also I need to use Concat to Union ALL as in SQL query. – PUBG Feb 28 '19 at 12:10
Sorry, that was the first thing that came up. Could you please add the tag to your question to avoid that confusion again? I'll be back in a minute if I can find how to do it in Core. – krillgar Feb 28 '19 at 12:14
1

I looked through the documentation, and I don't see any of that. Could you try just doing `TDD.ReportingDate.AddDays(TDD.IncrementDays).AddMonths(TDD.IncrementMonths).AddYears(TDD.IncrementYears)`? If they didn't include that in the same class as before by this point, it could be possible they're just translating the native C# methods built into `DateTime`. – krillgar Feb 28 '19 at 12:18
1

To be clear it's actually called the conditional operator. Ternary just refers to any operators that take 3 operands and it's currently the only one that does. – juharr Feb 28 '19 at 12:37
I have updated the question with new query but I get error at items.Concat – PUBG Feb 28 '19 at 13:37
I resolved above using answer in this : https://stackoverflow.com/questions/24501266/concat-two-iqueryables-with-anonymous-types – PUBG Feb 28 '19 at 14:05

IF ELSE condition in SELECT statement of LINQ query with DATEADD() logic

1 Answers1