locked
How can i add SQL Function To EF Code First ? RRS feed

  • Question

  • User-1846805900 posted

    Hi

    i have an SQL Function that convert Numbers to text - i need to use it throw EF Code First

    So please how can i do that (sample code if possible) ?

    SQL Code:

    CREATE FUNCTION [dbo].[Tafkeet]
    
    (@TheNo NUMERIC(18,2)) RETURNS VARCHAR(1000) AS
     
    BEGIN
     		
    IF          @TheNo <= 0 RETURN N'لا يوجد'
    	 
    DECLARE     @TheNoAfterReplicate VARCHAR(15)
    
    SET         @TheNoAfterReplicate = RIGHT(REPLICATE('0',15) + CAST(FLOOR(@TheNo) AS VARCHAR(15)),15)
         
    DECLARE     @ComWithWord VARCHAR(1000), @TheNoWithDecimal AS VARCHAR(400), @ThreeWords AS INT 
    	 
    SET         @ThreeWords = 0
     
    SET         @ComWithWord = ''
    
    DECLARE		@Tafket TABLE (num INT, NoName VARCHAR(100))
         
    INSERT INTO @Tafket VALUES (0,'')  
    INSERT INTO @Tafket VALUES (1, N'واحد')
    INSERT INTO @Tafket VALUES (2, N'إثنان')
    INSERT INTO @Tafket VALUES (3, N'ثلاثة')
    INSERT INTO @Tafket VALUES (4, N'أربعة')
    INSERT INTO @Tafket VALUES (5, N'خمسة')
    INSERT INTO @Tafket VALUES (6, N'ستة')
    INSERT INTO @Tafket VALUES (7, N'سبعة')
    INSERT INTO @Tafket VALUES (8, N'ثمانية')
    INSERT INTO @Tafket VALUES (9, N'تسعة')
    INSERT INTO @Tafket VALUES (10, N'عشرة')
    INSERT INTO @Tafket VALUES (11, N'إحدى عشر')
    INSERT INTO @Tafket VALUES (12, N'إثنى عشر')
    INSERT INTO @Tafket VALUES (13, N'ثلاثة عشر')
    INSERT INTO @Tafket VALUES (14, N'أربعة عشر')
    INSERT INTO @Tafket VALUES (15, N'خمسة عشر')
    INSERT INTO @Tafket VALUES (16, N'ستة عشر')
    INSERT INTO @Tafket VALUES (17, N'سبعة عشر')
    INSERT INTO @Tafket VALUES (18, N'ثمانية عشر')
    INSERT INTO @Tafket VALUES (19, N'تسعة عشر')
    INSERT INTO @Tafket VALUES (20, N'عشرون')
    INSERT INTO @Tafket VALUES (30, N'ثلاثون')
    INSERT INTO @Tafket VALUES (40, N'أربعون')
    INSERT INTO @Tafket VALUES (50, N'خمسون')
    INSERT INTO @Tafket VALUES (60, N'ستون')
    INSERT INTO @Tafket VALUES (70, N'سبعون')
    INSERT INTO @Tafket VALUES (80, N'ثمانون')
    INSERT INTO @Tafket VALUES (90, N'تسعون')
    INSERT INTO @Tafket VALUES (100, N'مائة')
    INSERT INTO @Tafket VALUES (200, N'مائتان')
    INSERT INTO @Tafket VALUES (300, N'ثلاثمائة')
    INSERT INTO @Tafket VALUES (400, N'أربعمائة')
    INSERT INTO @Tafket VALUES (500, N'خمسمائة')
    INSERT INTO @Tafket VALUES (600, N'ستمائة')
    INSERT INTO @Tafket VALUES (700, N'سبعمائة')
    INSERT INTO @Tafket VALUES (800, N'ثمانمائة')
    INSERT INTO @Tafket VALUES (900, N'تسعمائة')
     
    INSERT INTO @Tafket SELECT FirstN.num + LasteN.num, LasteN.NoName + N' و ' + 
                FirstN.NoName FROM (SELECT * FROM @Tafket WHERE num BETWEEN 20 AND 90) 
                FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 9) LasteN
                
    INSERT INTO @Tafket SELECT FirstN.num + LasteN.num, FirstN.NoName + N' و ' + 
    			LasteN.NoName FROM (SELECT * FROM @Tafket WHERE num BETWEEN 100 AND 900) 
    			FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 99) LasteN 
    			
    			IF LEFT(@TheNoAfterReplicate,3) > 0 SET @ComWithWord = @ComWithWord + 
    			ISNULL((SELECT NoName FROM @Tafket WHERE num = LEFT(@TheNoAfterReplicate,3)),'') + N' ترليون'
    			
    			IF LEFT(RIGHT(@TheNoAfterReplicate,12),3) > 0 AND LEFT(@TheNoAfterReplicate,3) > 0 SET @ComWithWord = @ComWithWord + N' و ' 
    			
    			IF LEFT(RIGHT(@TheNoAfterReplicate,12),3) > 0 SET @ComWithWord = @ComWithWord + 
                ISNULL((SELECT NoName FROM @Tafket WHERE num = LEFT(RIGHT(@TheNoAfterReplicate,12),3)),'') + N' بليون'
                
    IF LEFT(RIGHT(@TheNoAfterReplicate,9),3) > 0 		
    BEGIN                                          
    			SET @ComWithWord = @ComWithWord + 
    			
    			CASE 
    				WHEN @TheNo > 999000000 THEN N' و' 
    				ELSE '' 
    			END
    			
    			SET @ThreeWords = LEFT(RIGHT(@TheNoAfterReplicate,9),3) SET @ComWithWord = @ComWithWord +
    			
    			ISNULL((SELECT 
    					CASE 
    						WHEN @ThreeWords > 2 THEN NoName 
    					END
    			FROM @Tafket WHERE num = LEFT(RIGHT(@TheNoAfterReplicate,9),3)),'') +
    			
    			CASE 
    				WHEN @ThreeWords = 2 THEN N' مليونان'
    				WHEN @ThreeWords BETWEEN 3 AND 10 THEN N' ملايين' 
    				ELSE N' مليون' 
    			END 
    END 
    			
    
    IF LEFT(RIGHT(@TheNoAfterReplicate,6),3) > 0			
    BEGIN                                        	
    			SET @ComWithWord = @ComWithWord +
    			
    			CASE 
    			    WHEN @TheNo > 999000 THEN N' و' 
    			    ELSE '' 
    			END
    			
    			SET @ThreeWords = LEFT(RIGHT(@TheNoAfterReplicate,6),3) SET @ComWithWord = @ComWithWord + 
    			
    			ISNULL((SELECT 
    					CASE 
    						WHEN @ThreeWords>2 THEN NoName 
    					END
    			FROM @Tafket WHERE num = LEFT(RIGHT(@TheNoAfterReplicate,6),3)),'') +
    			 
    			CASE 
    				WHEN @ThreeWords=2 THEN N' الفان'
    				WHEN @ThreeWords BETWEEN 3 AND 10 THEN N' آلاف' ELSE N' الف'
    			END 
    END
    			
    IF RIGHT(@TheNoAfterReplicate,3) > 0		
    BEGIN
    			IF ROUND(@TheNo,0) > 999 
    			BEGIN
    				SET @ComWithWord = @ComWithWord + N' و' 
    			END
    END
    			
    			SET @ThreeWords = RIGHT(@TheNoAfterReplicate,2) SET @ComWithWord =  @ComWithWord  +  
    			ISNULL((SELECT  NoName FROM @Tafket WHERE num = RIGHT(@TheNoAfterReplicate,3)),'')
    			
    -- set @ComWithWord =  @ComWithWord  +   ISNULL((select  NoName  from @Tafket where @ThreeWords>2 AND num=right(@TheNoAfterReplicate,3)),'')
    			
    			SET @ComWithWord = @ComWithWord + ' ' +
    			
    			CASE 
    				WHEN @ThreeWords = 2 THEN N' جنيهان' 
    				WHEN @ThreeWords BETWEEN 3 AND 10 THEN N' جنيهات' 
    				ELSE N'جنيها' 
    			END 
    			
    			IF RIGHT(RTRIM(@ComWithWord),1) = ',' SET @ComWithWord = SUBSTRING(@ComWithWord, 1, LEN(@ComWithWord) - 1) 
    			
    IF RIGHT(@TheNo, LEN(@TheNo) - CHARINDEX('.', @TheNo)) > 0 AND CHARINDEX('.', @TheNo) <> 0	
    BEGIN      
    			SET @ThreeWords = LEFT(RIGHT(ROUND(@TheNo,2),2),2) SELECT @TheNoWithDecimal = N' و ' + 
    			ISNULL((SELECT NoName FROM @Tafket WHERE num = LEFT(RIGHT(ROUND(@TheNo,2),2),2) AND @ThreeWords > 2),'')     
    			SET @TheNoWithDecimal = @TheNoWithDecimal +
    		   
    			CASE 
    				WHEN @ThreeWords = 2 THEN N' قرشان' 
    				WHEN @ThreeWords BETWEEN 3 AND 10 THEN N' قروش' 
    				ELSE N' قرشا ' 
    			END
    			
    			SET @ComWithWord = @ComWithWord + @TheNoWithDecimal 
    END 
    			
    			SET @ComWithWord = @ComWithWord RETURN LTRIM(RTRIM(@ComWithWord)) 
    END
    
    
    GO

    Monday, October 5, 2015 7:56 PM

Answers