Google+ Peter Bromberg's .NET Blog | How to Map Stored Procedure with dynamic SQL In Entity Framework

Peter Bromberg's .NET Blog All Things Programming

How to Map Stored Procedure with dynamic SQL In Entity Framework

15. November 2013 12:46 by admin in

When you do a function import to a stored proc that has dynamically - generated SQL in it, Entity Framework will report that no columns were mapped.

 

Here is an easy, non-destrctive way to make sure EF gets the column metadata to complete your function import.

 

What EF does when it attempts to map your sproc is it executes the sproc with all parameters having null values.

Of course if the resultset is generated via dynamically assembled SQL or comes from a Table Variable, EF cannot "see" the results.

 

So what we do is the following. 

 

Say your sproc returns six columns. You would return each column as a null, but casting it to it's actual datatype:

 

if ( @deptId is null and @chkExemptOnly is null  and @chkActiveOnly is null and @msYearAC is null and @msdepttypeid is null and @Firstname is null and @lastName is null ) 
begin
select
cast(null as int) as empid,
cast(null as varchar(11)) as emplID,
cast(null as int) as Active,
cast(null as varchar(25)) as lastName,
cast(null as varchar(25)) as firstName,
cast(null as varchar(25) ) as middleName
end

You can put this kind of code right at the beginning of the sproc just after the AS statement, and Entity Framework will correctly map the resultset for you. You do not need to worry about leaving the above SQL block in the sproc, since it only executes when all the input parameters are NULL.

Add comment

  Country flag


Loading