Geek Answers Handbook

How to use Entity Framework in CLR stored procedure?

I look forward to moving all the logic (which is implemented as manipulating Entity Framework 4 objects) to the server. It seems that it will be simple (due to the structure of the application) and useful (since I have one old laptop as a client and one hard server that runs SQL Server 2008, and creating a separate service for the logic may just introduce more delay if comparing it with the database data).

So, how to properly use the Entities Framework inside the CLR stored procedure and make it using the SqlContext provided by the server?

Suddenly I could not find examples on the Internet. Nobody seems to have neutralized this yet. Does this mean that the task is ridiculous, and I definitely shouldn't? It is not very convenient to use T-SQL and access tables directly instead of EF classes, because my model makes heavy use of inheritance and has a very complex table type structure for many very simple tables.

+3
source share
4 answers

No, you can’t. Visual Studio won't even let you add a file or project type. (This is too bad; I also wanted to do this to handle really complex logic.)

+1
source

You cannot - at least not at this time. The CLR contained in SQL Server 2005 through 2008 R2 is the .NET 2.0 CLR, and the Entity Framework 4 requires the .NET 4 platform.

So, now that you are doing the material inside the SQL-CLR method, you are limited only to direct ADO.NET 2.0.

Then the big question remains: why do you want to use EF4 inside the SQL-CLR function? They are designed to store proc, user-defined functions, user-defined aggregates - but, of course, not full-blown database applications, really ...

+2
source

Some of us do not seek to maintain database schema metadata in two or more places. EF is great for this, but if you want to take advantage of SQL-CLR performance, the schema must be defined in its context. This currently means that custom metadata codes in the SQL-CLR assembly generate DDL to define the database and import it into EF.

+2
source

It looks like with SQL Server 2008 R2 or higher, you can use .NET ver. 4.0. See this blog .

I run the following command against my SQL Server 2010 Eeeition developer:

select value from sys.dm_clr_properties where name = 'version'

and got the following output:

v4.0.30319

+1
source

More articles:


All Articles