R – Stored procedures in entity framework

entity-frameworkstored-procedures

I am trying to use a stored procedure in the entity framework that returns nothing. The stored procedure is purely for logging.

I added a function (right click -> add -> function import) which works ONLY when the return value is set to one of the entities. When I change the return type to be Int32, Bool or nothing, or any other value than an entity, the function (method) simply vanishes as soon as I hit the build button.

Any suggestions on how I can get this sorted?

Best Answer

For now you cannot - you have to use one of the entities defined in your model (although I thought basic scalar types like INT should work, too).

Things should get a lot better with EF4 - due out in late 2009 or early 2010.

See these articles for some info on those new features:

If your stored proc is purely for logging, I would probably just surface it outside the Entity Framework context and just call the stored procedure the good old-fashioned ADO.NET way.... why even bother putting all of that into an EF context? You're not retrieving, manipulating and storing data with this logging service - just make it a static method on a static class - keep it simple!

Marc

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

Sql – Insert results of a stored procedure into a temporary table

Related Topic