none
Entity Framework Code First RRS feed

  • Question

  • I am using Entity Framework Code First approach.Using this,I have created Customer and some other entities.

    Now I want to create a stored procedure 'GetCustomers' using context class and pass parameters and get result set in a collection

    it has to return 2 collections as below

    create procedure getcustomer @name nvarchar(max),@zipcode int

    as

    select  id,name,zipcode from Customer where name like (@name );

    select  id,name,zipcode from Customer where zipcode =@zipcode

     I want to create a stored procedure 'GetCustomers' using context class and not manually execute in DB.I need to achieve below results:

    1.Pass name parameter alone and return first collection

    2.Pass zipcode parameter alone and return 2nd collection.

    3.Combine result collection of 1 and 2 into a single collection using merge

    • Moved by CoolDadTx Monday, August 21, 2017 3:09 PM EF related
    Monday, August 21, 2017 10:27 AM

All replies

  • Hi ams16,

    According to your description, I am not sure what is your issue, could you please describe it in detailed.

    if you want to achieve your requirement via stored procedure, you could select records via input parameters. like this:

    create procedure getcustomer @name nvarchar(max),@zipcode int
    
    as
    
    if(@name IS NOT NULL and @zipcode IS NULL )
    BEGIN
      select  id,name,zipcode from Customer where name like   (@name );
    END
    
    if(@name IS NULL and @zipcode IS NOT NULL )
    BEGIN
    select  id,name,zipcode from Customer where zipcode =@zipcode 
    END
    
    if(@name IS NOT NULL and @zipcode IS NOT NULL )
    BEGIN
     --merge statement
    END
    

    About merge statement, please refer to the following statement

    https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

    Best regards,

    Cole


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 10:52 AM
    Moderator