LINQ Query to Filter then to a Model

Question

How can I do this correctly?

This is failing because schedule-on does not exist within m from RR2.

        var RR = (from m in DataContext.csrcallds
                  where m.scheduledon >= earliestDate
                  && m.scheduledon <= objdate1.DateStart
                  && m.assignto == 113
                  && (SqlMethods.DateDiffDay(m.scheduledon, m.completedon) > 5)
                  select m
                   );

        var RR2 = RR.Select(x => (GetBusinessDays((DateTime)x.scheduledon, (DateTime)x.completedon)) > 5).ToList());

        var RnR = (from m in RR2
                   group m by new { m.scheduledon.Value.Year, m.scheduledon.Value.Month } into p
                   orderby p.Key.Year ascending, p.Key.Month ascending
                   select new Date1()
                   {
                       theDate = DateTime.Parse($"{p.Key.Month} - {p.Key.Year}"),
                       theCount = p.Count(),
                       theString = $"{p.Key.Month} - {p.Key.Year}"
                   });

I am trying to query all the results. Then use my GetBusinessDay function to filter out the ones I don't need, gathering only the records within 5 business days. Then put the results into my Model named Date1.

I'm trying to do it like this because I cannot use GetBusinessDays within an LINQ query. So I'm trying to filter it outside of SQL, then group the records again.

How do I go about accomplishing this task?

`"schedule-on does not exist within m"` - Well, I guess you'd need to use a property that does exist then. It's not really clear what we can do to help, we don't know your model structure. But you can definitely only filter on values that exist. — David, Oct 12 '16 at 12:45
@David I can post my model. All I need from my model is theDate, theCount, and theString which you see there. They are what you'd imagine, datetime, int, and string. — Stem Step, Oct 12 '16 at 12:48
@David there must be a way for me to use my GetBusinessDays function. Because (SqlMethods.DateDiffDay(m.scheduledon, m.completedon) > 5) gives me an incorrect result within an LINQ query. 5 days != 5 business days. — Stem Step, Oct 12 '16 at 12:49
RR2 in your code is a collection of bools - I think thats not what you want. Change "RR2 = RR.Select(...)" to "RR2 = RR.Where(...)" — Philip W, Oct 12 '16 at 12:50
Oh, you're correct @PhilipW sorry about that... I still get error Method 'Int32 GetBusinessDays(System.DateTime, System.DateTime)' has no supported translation to SQL though. — Stem Step, Oct 12 '16 at 13:01
@StemStep - Keep in mind that your code is just creating an expression and that expression must be converted into SQL before sending it to the database server. The error is telling you that there is no corresponding SQL command that is equivalent to your `GetBusinessDays` method. In other words, it doesn't know how to create the SQL command. — Chris Dunaway, Oct 12 '16 at 14:43

score 0 · Answer 1 · edited May 23 '17 at 12:34

0

You could add this to your SQL Query to filter out the weekend days.

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

Or This

select [date_created]
from table
where DATENAME(WEEKDAY, [date_created]) <> 'Saturday'
  and DATENAME(WEEKDAY, [date_created]) <> 'Sunday'

Or if you have to stick to LINQ try the ideas outlined here: Linq query DateTime.Date.DayOfWeek

DateTime firstSunday = new DateTime(1753, 1, 7); 
var bookings = from b in this.db.Bookings 
               where EntityFunctions.DiffDays(firstSunday, b.StartDateTime) % 7 == 1 
               select b;

edited May 23 '17 at 12:34

Community

1
1

answered Oct 12 '16 at 12:49

Neo

3,309
7
35
44

I'm confused about this answer because I don't see how I get the amount of business days between scheduleon and completed on, like this would: (SqlMethods.DateDiffDay(m.scheduledon, m.completedon) – Stem Step Oct 12 '16 at 13:11
I thought the intent was to get records that are Mon-Friday. If that is not the case, I screwed up. If not, you can either hydrate your initial model backed by the SQL above, or you can use on the LINQ solutions provided by the link I provided ( only copy and pasted on sample. – Neo Oct 12 '16 at 13:13

score 0 · Answer 2 · edited May 23 '17 at 10:27

Solved by using function workdays server side: https://stackoverflow.com/a/252532/6157660

Allows me to make a simple LINQ query, and gives me what I need.

I did edit the function to remove the +1 from DateDiff. as same days should be 0 not 1.

Thank you guys for your help!

        var RnR = (from m in DataContext.csrcallds
                   where m.scheduledon >= earliestDate
                   && m.scheduledon <= objdate1.DateStart
                   && m.assignto == 113
                   && (DataContext.fn_WorkDays((DateTime)m.scheduledon, (DateTime)m.completedon)) > 5
                   group m by new { m.scheduledon.Value.Year, m.scheduledon.Value.Month } into p
                   orderby p.Key.Year ascending, p.Key.Month ascending
                   select new Date1()
                   {
                       theDate = DateTime.Parse($"{p.Key.Month} - {p.Key.Year}"),
                       theCount = p.Count(),
                       theString = $"{p.Key.Month} - {p.Key.Year}"
                   });

LINQ Query to Filter then to a Model

2 Answers2