How to translate SQL to C#-CodePudding

I have a SQL query that looks like this:

SELECT
    a.Date,
    CASE
        WHEN a.Type = 'String 1' OR a.Type = 'String 2' 
            THEN 'foo'
            ELSE 'bar'
    END AS VisitType,
    DATEDIFF (d, (SELECT TOP 1 Date FROM dim.Date WHERE DateKey = a.StartDate), 
                 (SELECT TOP 1 Date FROM dim.Date WHERE DateKey = a.EndDate)) AS Duration

I am trying to convert it to a C# expression and so far I have something like this:

var allowedTypes = new[]{"String 1","String 2", "String 3", "String 4"}
var Data = from a in dbContext.Claim
where a.MemberId = memberId
&& a.StartDate > startDate
&& a.EndDate <= endDate
&& a.Type.Where(???t => allowedTypes.Contains(allowedTypes))  // This line I have issues with
select new
{
   Date = a.EndDate,
   VisitType = ???,
   VisitDuration = ???
}

I am having difficulty with the DateDiff concepts and doing a Contains like method with a string array. ALSO I have realized that the type of the dates are contained in a nullable int.

Thanks for all your advice so far~!

CodePudding user response：

Try moving the conditional into the result:

select new
{
   Date = a.EndDate,
   VisitType = allowedTypes.Contains(a.Type) ? "foo" : "bar",
   VisitDuration = ???
}

CodePudding user response：

var result = dbContext.Claims
    .Where (claim => claim.MemberId = memberId
                  && claim.StartDate > startDate
                  && claim.EndDate <= endDate
    .Select(claim => new
    {
        Date = claim.EndDate,
        VisitType = allowedTypes.Contains(claim.Type) ? "foo" : "bar",
        VisitDuration = claim.EndDate - claim.StartDate,
    });

In words: given values for memberId, startDate, endDate. From the table of Claims, keep only those Claims that have a value for property MemberId that equals memberId, a value for Property startDate that is higher than startDate, and a value for endDate that is smaller than endDate.

From every Claim in the sequence of remaining Claims, make one new object with three properties.

Date is the EndDate of the Claim,
Duration is the EndDate - StartDate of the claim
If the VisityType of the Claim is in allowedTypes, then the value for property VisitType is "foo", else VisitType has a value of "bar"