How to use Scalar Value functions in EntityFramework Query

c#-6.0 entity-framework-6 sql-server-2014

Question

I have the following scalar valued function:

CREATE FUNCTION [dbo].[GetCustomersTime]
(
    @TimeSpanType char(1),
    @TimeSpan int,
    @CurrentUtc datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @CustomersTime DATETIME; 

    SELECT @CustomersTime = CASE UPPER(@TimeSpanType)
       WHEN 'S' THEN DATEADD(Second, @TimeSpan, @CurrentUtc)
       WHEN 'I' THEN DATEADD(Minute, @TimeSpan, @CurrentUtc)
       WHEN 'H' THEN DATEADD(Hour, @TimeSpan, @CurrentUtc)
       WHEN 'D' THEN DATEADD(Day, @TimeSpan, @CurrentUtc)
       WHEN 'W' THEN DATEADD(Week, @TimeSpan, @CurrentUtc)
       WHEN 'M' THEN DATEADD(Month, @TimeSpan, @CurrentUtc)
       WHEN 'Q' THEN DATEADD(Quarter, @TimeSpan, @CurrentUtc)
       WHEN 'Y' THEN DATEADD(Year, @TimeSpan, @CurrentUtc)
       ELSE DATEADD(SECOND, @TimeSpan, @CurrentUtc)
    END;

    RETURN @CustomersTime
END

This method is what I want to utilise in the Entity Framework Query. Below is the goal I'm trying to reach:

SELECT c.CustomerId, c.FirstName, c.LastName, c.OrderDateUtc
FROM dbo.Customer c
--Other INNER JOINs
WHERE dbo.GetCustomersTime(c.[TimeSpanType], c.[TimeSpan], c.OrderDateUtc) > GetUtcDate()
--Additional Conditions

The entity framework I'm using is

(from c in _context.Customers
                 //additional joins
             where //how to use function
             select new
             {
                 c.CustomerId,
                 c.FirstName,
                 c.LastName,
                 c.OrderDateUtc
             }).ToList();

A project and the GetCustomersTime method in the EF query were proposed by several. But I was unable to locate a true instance. Can somebody assist me with this?

1
1
3/24/2018 12:05:22 AM

Accepted Answer

It seems that I should have done more research. I liked the recommended answer, here. The URL is CodeFirstFunctions on github.

0
3/25/2018 5:14:15 AM

Popular Answer

ZZZ_tmp


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow