This project is read-only.

Issue w/Custom SProcs

Mar 17, 2011 at 4:57 AM

I finally found something that I can't figure out how to do in Entity SQL :), so I'm trying to do it via a custom sproc.

I was able to get one set up. However, the generated code for the wrapper function isn't correct. I don't know if that's because I configured something wrong in the schema or because there's a bug in the code generator or because I'm using the wrapper function incorrectly.

My sproc takes a single integer parameter which has no default value and cannot be null. The generated code is as follows:

 

public ObjectResult<spTest> spTest(int forumid)
{
	var forumidParameter = new ObjectParameter("forumid", typeof(int));
	return base.ExecuteFunction<spTest>("spTest", forumidParameter);
}

 

But that of course won't work because the value of the ObjectParameter doesn't get set. If I manually edit the code to be:

public ObjectResult<spTest> spTest(int forumid)
{
	var forumidParameter = new ObjectParameter("forumid", forumid);
	return base.ExecuteFunction<spTest>("spTest", forumidParameter);
}

the wrapper works as expected.

- Mark

Mar 17, 2011 at 6:13 PM

BTW, something else I noticed this morning is that the DataReader used internally when the sproc is called from within the entity context is "strict" in its mapping of the column names returned by the sproc. It's been a while since I last called a DataReader directly, but, if possible, I wonder if it's possible to relax the mapping constraints.

I could think of several ways to do that. One would be to match the returned column name to the mapped property name case insensitive. Another would be to do the mapping "positionally" if there's only one column being returned, although that might be too narrow a case to bother with, and would probably introduce the potential for some difficult to track down bugs in application code.

OTOH, relaxing case sensitivity might make sense since (I think) T-SQL is not case sensitive itself. So there's an argument that mapping from a case-insensitive domain to a case-sensitive domain ought not to impose case sensitivity.

Mar 17, 2011 at 8:09 PM

Perhaps we are using different versions of the EFDAL. I have a custom stored proc and the code in the Entites.cs file looks like this

public ObjectResult<CustomStoredProcedure> CustomStoredProcedure(string name)
{
ObjectParameter nameParameter = null;
if (name != null) { nameParameter = new ObjectParameter("name", name); }
else { nameParameter = new ObjectParameter("name"typeof(string)); }
return base.ExecuteFunction<CustomStoredProcedure>("AAAA", nameParameter);
}

Can you verify that you are looking at the context class and you see something like this?

Mar 17, 2011 at 11:55 PM

I just upgraded to the latest version (3/15/2011) and here's what got generated:

/// <summary>
/// 
/// </summary>
public ObjectResult<ForumLeaves> ForumLeaves(int forumid)
{
	var forumidParameter = new ObjectParameter("forumid", typeof(int));
	return base.ExecuteFunction<ForumLeaves>("gen_ForumLeaves", forumidParameter);
}

Same as before. Here's the XML from the schema file:

<customstoredprocedures key="fdba8f9b-31c1-4fd3-82a9-8bed8f8934cb">
    <customstoredprocedure key="f829c11d-f5e8-4477-9918-19754a9aac56" name="ForumLeaves" databaseobjectname="" isexisting="False" id="3" createdDate="2011-03-17 09:14:47">
        <columns key="7d8e85a2-cce4-437a-b2e7-523b3e81589a">
            <Reference key="9d2b1c36-5abd-4def-9bdf-a79f17ec8ffc" ref="3" refType="6" />
        </columns>
        <parameters key="1bc56b59-bb8b-44ff-b567-eb857e961abd">
            <Reference key="1c11602d-6073-4490-bb74-1b8d0d0db467" ref="3" refType="5" />
        </parameters>
        <sql><![CDATA[WITH forum_cte AS 
	(SELECT forum_id, parent_id
		FROM dbo.forum 
		WHERE (forum_id = @forumID) 
	UNION ALL
	SELECT t1.forum_id, t1.parent_id 
		FROM dbo.forum AS t1 
		INNER JOIN forum_cte AS h ON t1.parent_id = h.forum_id
	)

-- the cte always includes @forumID, whether or not it is
-- a leaf node itself. we deal with that by always excluding
-- it, and then adding it back if and only if it is a leaf
-- node
   SELECT result.forum_id
	FROM forum_cte AS result
		LEFT OUTER JOIN forum AS x ON result.forum_id = x.parent_id
	WHERE (result.forum_id <> @forumID) AND (x.parent_id IS NULL)

	UNION SELECT a.forum_id
		FROM forum AS a 
			LEFT OUTER JOIN forum AS b ON a.forum_id = b.parent_id
		WHERE (a.forum_id = @forumID) AND (b.parent_id IS NULL)]]></sql>
    </customstoredprocedure>
</customstoredprocedures>

So what am I doing wrong?

Mar 18, 2011 at 2:27 PM

We found the offending code snippet. It was for non-nullable parameters. This has been fixed and a new version of EFDAL with some other enhancements will be released this weekend most likely.

Mar 18, 2011 at 3:05 PM

Great! Thanks.

Mar 21, 2011 at 1:56 AM

Please get the latest version and download new generators.