Writing LINQ Trying to create a LINQ to run in C# program

Question

I'm currently creating a dropdown and hoping to find help writing a LINQ query...

Here is the SQL I'm trying to replicate:

select  
IIf  
    (  
        TA.[LeaseDeptSubmittal] IS NOT NULL  
        AND TA.[LeaseDeptComplete]  IS NULL, 'Lease Dept',  
        IIf  
        (  
            TA.[CompleteDate]              IS NULL  
            AND TA.[RequestAddtnlInfo] IS NOT NULL, 'Need Info',  
            IIf  
            (  
                TA.[RequestDate] IS NULL, '',  
                IIf  
                (  
                    TA.[RequestDate]  IS NOT NULL  
                    AND TA.[CompleteDate] IS NULL, 'Pending', 'Complete'  
                )  
            )  
        )  
    ) , 
Id 
from Tasks TA

Ok. So what's your question? What have you tried? What problem are you facing? This is not a free write-my-code service, but we will help you with your own attempt at solving your problem. Also, which DBMS is this written for? Use of `IIf` suggests maybe Access or SQL Server? But it would help us to be certain. — ADyson, Sep 11 '18 at 16:14
my apologize I am new at all of this,( the ship dropped my off yesterday and I still have alien brain cells, and will send $ via paypal if you can help); correct I am trying to convert Access db to a mvc5 web site. I've done a few LINQ queries which involved multiple joins, but this is the 1st I've seen looking as it does, so guess I'm just confused. — snj013, Sep 11 '18 at 17:06
This is what i have so far, (but I'm trying to use linq): public string GetStatus(Task Task) { string result = string.Empty; if (Task.LeaseDeptSubmittal != null && Task.LeaseDeptComplete != null) { result = "Lease Dept"; } else { if (Task.Completedate == null && Task.RequestAddtnlInfo != null) { result = "Need Info"; } — snj013, Sep 11 '18 at 17:06
else { if (Task.RequestDate == null) { result = string.Empty; } else { if (Task.RequestDate != null && Task.Completedate == null) { result = "Pending"; } else { result = "Complete"; } } } } return result; } — snj013, Sep 11 '18 at 17:07
The `edit` button is useful for adding information to your question. Don't do that in comments. — NetMage, Sep 11 '18 at 17:41

NetMage · Answer 1 · 2018-09-11T17:57:28.863

You can translate IIF pretty directly into the C# (LINQ) ternary conditional operator, and then you can just follow my SQL to LINQ Recipe.

var ans = from TA in Tasks
          select new {
            Status = (TA.LeaseDeptSubmittal != null &&
                      TA.LeaseDeptComplete == null)
                        ? "Lease Dept"
                        : (TA.CompleteDate == null &&
                           TA.RequestAddtnlInfo != null)
                                ? "Need Info"
                                : TA.RequestDate == null
                                    ? ""
                                    : (TA.RequestDate != null && TA.CompleteDate == null) ? "Pending" : "Complete",
            TA.Id
          };

I wouldn't normally indent ?: this way, but the nesting is quite deep (and unnecessarily redundant in one case) so I moved everything left.

I would re-work it like this to remove redundant tests and clear up the logic some:

var ans = from TA in Tasks
          select new {
            Status = (TA.LeaseDeptSubmittal != null &&
                      TA.LeaseDeptComplete == null)
                        ? "Lease Dept"
                        : TA.CompleteDate != null
                            ? "Complete"
                            : TA.RequestAddtnlInfo != null
                                ? "Need Info"
                                : TA.RequestDate != null
                                    ? "Pending"
                                    : "",

            TA.Id
          };

Writing LINQ Trying to create a LINQ to run in C# program

1 Answers1