How to use Scalar Value functions in EntityFramework Query

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

Question

I have a scalar valued function as below:

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

I would like to use this function in the Entity Framework Query. The query I would like to achieve is below:

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

My entity framework query is:

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

Some suggested to use a project and have the GetCustomersTime function in the EF query. But I could not find a real example. Can anyone help with this?

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

Accepted Answer

Looks like I should have searched it better. I found the solution suggested here. The github link is CodeFirstFunctions.

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

Popular Answer

Try the following:

  1. Declare a function in the (SSDL) of your .edmx file. The name of the function must be the same as the name of the function declared in the database.
  2. Add a corresponding method to a class in your application code and apply a EdmFunctionAttribute to the method.
  3. Call the method in a LINQ to Entities query.

For more details check this link.



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