How do I use this Class Extension inside of an LINQ query?

Question

How do I use this class extension inside my LINQ query. I get the error:

Method 'System.DateTime AddBusinessDays(System.DateTime, Int32)' has no supported translation to SQL.

        var CProcess = (from m in DataContext.csrcallds
                        let scheddt = FluentDateTime.DateTimeExtensions.AddBusinessDays((DateTime)m.scheduledon, 2)
                        where m.scheduledon >= earliestDate
                        && m.scheduledon <= objdate1.DateStart
                        && m.calltype == "CHQRUN"
                        && (SqlMethods.DateDiffDay(scheddt, m.completedon) > 2)
                        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}"
                        });

The method I am using: https://stackoverflow.com/a/1379158/6157660

Can you show the code of the `AddBusinessDays` extensions method? In short, you would have to add the contents of the method into the linq query directly. Depending on the code in the method, [LinqKit](http://www.albahari.com/nutshell/linqkit.aspx) might be an option. — Maarten, Oct 06 '16 at 14:47
@GiladGreen the closest I could find was this: http://stackoverflow.com/questions/4085645/call-class-method-inside-the-linq-query but I don't understand how writing my LINQ query like that can do the same thing mine is already doing (if I removed the addbusinessday function it works). — Stem Step, Oct 06 '16 at 14:55
@GiladGreen this is because my function also uses orderby and group as well as select... — Stem Step, Oct 06 '16 at 15:01
http://stackoverflow.com/questions/19390235/method-has-no-supported-translation-to-sql — ltiveron, Oct 06 '16 at 15:10

Tarek · Answer 1 · 2016-10-06T15:11:12.980

2

You are getting the proper response, since there is no SQL function of that name, if your query is not materialized yet, linq to sql will try to convert all your statments to sql statments, and of course your extention function can't be converted.

You can add the function to your instance of sql itself or materialize the query first, the call the function on your data in memory instead.

Also if your orm supports it, you can create a stored procedure in sql that includes the functionality of your function, then call the stored procedure via the orm.

edited Oct 06 '16 at 15:11

answered Oct 06 '16 at 15:09

Tarek

1,219
13
18

Yes, how do I do that is the question. – Stem Step Oct 06 '16 at 15:10
1

You can materialize the query by calling ToList() for instance then, then do your operations/function call on the data. You have to think about the implications of that though. – Tarek Oct 06 '16 at 15:12

Stem Step · Answer 2 · 2016-10-06T15:32:44.180

I've done it like this:

        var CProcess = (from m in DataContext.csrcallds.AsEnumerable()
                        where m.scheduledon >= earliestDate
                        && m.scheduledon <= objdate1.DateStart
                        && m.calltype == "CHQRUN"
                        && (SqlMethods.DateDiffDay(FluentDateTime.DateTimeExtensions.AddBusinessDays((DateTime)m.scheduledon, 2), m.completedon) > 2)
                        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 used .AsEnumerable(), and used the method within the query.

I didn't downvote, but using `AsEnumerable()` is a [very bad solution](http://stackoverflow.com/a/17996264/861716), *especially* when applied before all predicates. — Gert Arnold, Oct 06 '16 at 20:28

How do I use this Class Extension inside of an LINQ query?

2 Answers2