Linq Choose Where Specific Column Has Value

Question

I am trying to dynamically choose a table's column based off the day of the week of a specified date. I have seen posts for dynamically choosing a column, but have not had any success.

My table has a column for each day of the week, and in this column can either be a NULL value or a number (count), along with some other columns I am querying against.

+------+------+------+------+------+------+------+
| Mon  | Tue  | Wed  | Thur | Fri  | Sat  | Sun  |
+------+------+------+------+------+------+------+
| NULL | NULL | NULL | 1867 | NULL | NULL | NULL |
+------+------+------+------+------+------+------+

In my Linq, I am passing in a specific date (01/17/2019 12:00 am). Based off the day of the week for this date (Thursday in this instance), I need to see if there is a value in the [Thu] column or if it is null along with some other WHERE conditions. Ultimately looking for a count of rows where these conditions are true and there is a value in the specific day of the week column. In SQL I used a case statement, but it was ugly and didn't translate to Linq well.

Below is my query without my needed WHERE clause. For this example I would expect my query to return a value of 1. If the date were 1/18/2019 12:00 am, I would expect it to return 0.

int numOfRecords = biMarketEntities.DistRuleNews
    .Where(x => DMCodeDate.CompanyCode.Contains(x.CompanyCode) &&
    DMCodeDate.DMCode == x.DMCode && 
    DMCodeDate.Date.Date >= x.StartDate && DMCodeDate.Date.Date <= x.EndDate) 
    .Count();

EDIT: Attached is the SQL query that I am trying to translate to Linq. My current Linq statement is missing the lengthy CASE statement in my WHERE clause and is the added condition I'm trying to succinctly add to my Linq query.

SELECT COUNT(*)
FROM [BIMarket].[dbo].[DISTRULENEW]
WHERE [DISTRULENEW].[companycode] IN ('LVT')
    AND [DISTRULENEW].[dmcode] IN ('LVTTMC')
    AND '1/17/2019 12:00:00 AM' BETWEEN [DISTRULENEW].[startdate]
        AND [DISTRULENEW].[enddate]
    AND CASE Datename([DW], '1/17/2019 12:00:00 AM')
        WHEN 'Monday'
            THEN CASE 
                    WHEN [DISTRULENEW].[mon] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[mon]
                    END
        WHEN 'Tuesday'
            THEN CASE 
                    WHEN [DISTRULENEW].[tue] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[tue]
                    END
        WHEN 'Wednesday'
            THEN CASE 
                    WHEN [DISTRULENEW].[wed] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[wed]
                    END
        WHEN 'Thursday'
            THEN CASE 
                    WHEN [DISTRULENEW].[thu] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[thu]
                    END
        WHEN 'Friday'
            THEN CASE 
                    WHEN [DISTRULENEW].[fri] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[fri]
                    END
        WHEN 'Saturday'
            THEN CASE 
                    WHEN [DISTRULENEW].[sat] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[sat]
                    END
        WHEN 'Sunday'
            THEN CASE 
                    WHEN [DISTRULENEW].[sun] = [DISTRULENEW].[geoid]
                        THEN [DISTRULENEW].[zip]
                    ELSE [DISTRULENEW].[sun]
                    END
        END IS NOT NULL

I don't see where you are looking at the day of week? Is that a column in DistRuleNews? Show the model. You may be looking for something like [this](https://stackoverflow.com/questions/36313234/linq-query-datetime-date-dayofweek)? — Steve Greene, Jan 02 '19 at 20:41
@SteveGreene Sorry, I knew it was hard for me to write what I was asking. That part is missing from my Linq query. I updated the question to include the SQL query I am trying to replicate if that helps. — B-Ray, Jan 02 '19 at 20:52

score 0 · Accepted Answer · answered Jan 02 '19 at 21:23

You don't give a lot of detail but guessing you want something like this (in you code you are checking for NULL -- I'm not quite clear how that works but you can see how to "switch" based on day of week here.

int numOfRecords = biMarketEntities.DistRuleNews
    .Where(x => DMCodeDate.CompanyCode.Contains(x.CompanyCode) 
             && DMCodeDate.DMCode == x.DMCode 
             && DMCodeDate.Date.Date >= x.StartDate 
             && DMCodeDate.Date.Date <= x.EndDate) 
    .Where(x => {
       var retVal = x.Zip;
       switch (DMCodeDate.Date.DayOfWeek)
       {
          case DayOfWeek.Monday:
            retVal = x.Monday;
            break;
          case DayOfWeek.Tuesday:
            retVal = x.Tuesday;
            break;
          case DayOfWeek.Wednesday:
            retVal = x.Wednesday;
            break;
           // repeat for each day.
       }     

       return retVal; 
    }).Count();

Linq Choose Where Specific Column Has Value

1 Answers1