new with Store Functions - how can i make it works please ?

Nov 12, 2015 at 7:12 PM
Hi All

i install the package, and i try to add it to my project but i can't add it as it should be (i missed something):
 public partial class MashobatTillSepContext : DbContext
    {
        public MashobatTillSepContext()
            : base(nameOrConnectionString: "name=MashobatTillSepContext")
        {
        }


        // here i set Function to take number as parameter but i don't know how to make it return with text i got from it ?
        [DbFunction("MashobatTillSepContext", "Tafkeet")]
        public IQueryable<MashobatTillSepContext> Tafkeet(float TheNo)
        {
            ObjectParameter numberToConvert = TheNo != null ? 
                new ObjectParameter(name: "TheNo", value: TheNo): 
                new ObjectParameter(name: "TheNo", type: typeof(float));

            //return ((IObjectContextAdapter)this).ObjectContext
            //    .CreateQuery<Customer>(
            //        string.Format("[{0}].{1}", GetType().Name,
            //            "[Tafkeet](@TheNo)"), numberToConvert);
        }

        public virtual DbSet<Data_2015> Data_2015 { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Add(new FunctionsConvention<MashobatTillSepContext>(defaultSchema: "dbo"));
        }
    }
but it's not working with my sql function:
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
so please what is the problem in my c# code ? and how i can make it working ?
Coordinator
Nov 15, 2015 at 2:53 AM
Your function does is not a TVF but a scalar function. Therefore you can't map it as a TVF (i.e. return IQueryable). Take a look at this post in which I show how to map scalar functions.Having said that - I looked a little bit at your function and I am not entirely sure why it is a database function. It seems to me it would be much easier to implement it just in C#.

Pawel