Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

infoblog » Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Tue, 02 Dec 2008 19:56:54 GMT

PingBack from http://blog.a-foton.ru/index.php/2008/12/02/migrating-from-linq-to-sql-to-the-entity-framework-stored-procedures-for-data-retrieval/

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

John — Tue, 02 Dec 2008 21:29:16 GMT

NO ONE CARES. EF is an overcomplicated piece of junk compared to L2S. If we're going to migrate our L2S DAL over to something else, it won't be Entity Framework (which you'll probably abandon next year in favor of something else anyway!)

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Chris — Wed, 03 Dec 2008 00:12:18 GMT

Why migrate your L2S DAL over to something else if it is working for you? I don't understand.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Faisal Mohamood — Wed, 03 Dec 2008 02:29:22 GMT

Chris,

If LINQ to SQL works for you, by all means you should feel free to continue to use it.

The two technologies are fundamentally very different in how they approach database access and conceptual modeling. Because of the differences, there are some common issues that customers run into when migrating from LINQ to SQL to the Entity Framework and we are using this series to provide hints and recommendations on how to work through some of those challenges.

Faisal Mohamood

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

PaulG — Wed, 03 Dec 2008 02:44:26 GMT

I am very happy with the current LINQ to SQL. I have not looked at EF because L2S does everything I need - and really really well. I wish you all the best with your quest to improve EF for folks that L2S doesn't work for, but please don't ever kill L2S.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Chris — Wed, 03 Dec 2008 07:46:44 GMT

Faisal,

Sorry for the misunderstanding. I was questioning the pervasive doom-and-gloom attitude of some current L2S users toward EF, and specifically the first comment by John. I understand the different problem domain that EF is looking to serve, and I like what I see coming up with v2. I also appreciate the efforts that are being made to educate and inform those interested in making the switch. I just don't understand the vitriol coming from some of the L2S user base.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Jens — Wed, 03 Dec 2008 10:29:04 GMT

Chris,

The doom and goom is because we're all very happy with L2S because 't just works' That does not mean that we have nothing left on our wishlist, and now it becomes clear that we're never going to get that. Migrating to EF is no option because a) I can't justify the time and effort to do that, b) It doesn't 'just work', c) It needs a whole lot of configuration, d) It would render al that we have built to extend L2S useless, and e) The performance is far from there...

Maybe you now understand why we are pissed.

So, what John said counts for me too, if I ever need to move away from L2S because it's featureset is still that of 2008, it won't be to EF, I won't be that dumb anymore ...

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Matt Brooks — Wed, 03 Dec 2008 12:56:37 GMT

Stored Procedures that return multiple result sets

"This is a manual coding process and is not supported by either the LINQ to SQL class designer or SQL Metal command-line tool."

I don't think this is entirely true. You can open the DBML file and specify multiple result types in the XML <Function> element. The correct code for multiple result sets is then generated in the DataContext.designer.cs file. It is just the DBML designer itself that has the limitation.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Sean — Fri, 05 Dec 2008 22:35:14 GMT

"The Entity Framework does not automatically create return types whilst mapping stored procedures into functions."

This is such a glaring omission. For this reason alone I have to stick with LINQ->SQL. This was a such a time saver in LINQ->SQL.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Aaron — Tue, 09 Dec 2008 23:02:21 GMT

i would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application....

They need to ensure an upgrade utility to ef.

man what a nightmare...

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Griffith Townsend — Wed, 10 Dec 2008 19:22:59 GMT

The ability to map and fire scalar and NonQuery SPs easily is a big omission, IMO, as existing data structures won't migrate to EF easily if they execute any stored procedures that don't map directly to entity types. Tie that in with the inability to have abstract entities that don't have a mapped table, but instances that map to several tables, or the ability to define interfaces that can be mapped to several entities, and you've got a robust framework waiting to happen.

I did some extension methods that tie on to the ObjectContext and allow you to execute all types of stored procedures, as long as they are added via Function Import:

http://grifftownsend.blogspot.com/2008/08/small-extension-to-entity-framework.html

Take a look, and let me know what you think!

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

SAY — Fri, 12 Dec 2008 07:44:32 GMT

Am I hearing it right? Microsoft killing LINQ to SQL? What a dumb decision!!!! With LINQ to SQL and slew of .NET technologies, I thought Microsoft finally become a enterprise class plaform for application development. I was happy that they dumped ADO. Why is Microsoft re-inventing wheel and in the process wasting time and valuable resources during hard economic times. They looking like failed big 3 auto companies i.e., investing in fuel inefficient SUV(Entity Framework) instead of a lean and clean LINQ to SQL. If LINQ to SQL lacks features, you should enhance it instead of dumping it. ADO was always overly complicated. Microsoft wake up and do the right thing. Industry can not wait for stupid ideas to mature and perform after 3-4 years. If I were you, I will junk ADO .NET and make big improvements to LINQ to SQL which is already tried and tested by many.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Laughing John — Sat, 13 Dec 2008 03:58:10 GMT

DAO, ADO, ODBC, OLEDB, Entity Framework, Linq to Sql, anyone remember the webstore in Exchange? WinFs anyone?

I think it's fair to say the choice for data access has been overwhelming and confusing over the years. Will tomorrows entity framework be yesterdays DAO or Ling to Sql for that matter?

I understand that EF is more than a data access layer, but given the state of version 1.0 I think it's fair enough for people to moan a bit. The EF looks promising for lots of reasons, but the simple fact is it doesn't do what we want right now and given the inevitable lag in adopting VS2010 it's unlikely to be adopted for some time in most shops, even if it is a stated direction (as were some of those other deprecated or lost technologies)

I can't help feeling that all this effort would be better spent on proper OO database development. Or is entity framework a step in that direction ....?

considerable issues moving from Linq to SQL to Entity Framework

Thomas DeMille Interactive — Wed, 17 Dec 2008 20:12:14 GMT

considerable issues moving from Linq to SQL to Entity Framework

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

KellenF — Wed, 17 Dec 2008 22:36:45 GMT

There are a lot of headaches to adopting Entity framework as far as I can tell... L2S 'just worked' as many people already said, you didn't even have to understand it to be fully functional, you just drag what you want into the designer, and code against it how you expect.

Entity framework has a lot of issues when you want to do more than just read a table... renaming a table and then updating the designer doesn't work, not being able to do scaler/nonquery/arbitrary type SPs out of the box is a nasty issue. These things can be worked around, but it was a headache, and it took some research/digging.

I like the concept, it just isn't ready yet.

Entity Framework:entity client 调用存储过程

davin — Sat, 20 Dec 2008 19:14:38 GMT

如果对于sp在EDM的映射有一些了解的话，可以很清楚的明白，在EDM中映射一个sp返回的类型包括：Entity Type，Scalars Type,None；这三中类型分别对应的sp返回内容对应到数据库，表的层面就是：（Entity）一张表的所有字段或若干字段(必须包括主键）;（Scalars）返回一个常量（int, varchar）可以是数据库的一个字段，可以是sum,count,left...等函数操作的结果;(none)自然是什么都不返回.这里自然就有一个严重的问题，对于返回（multiple

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Moon — Thu, 01 Jan 2009 22:19:51 GMT

Linq to Entities simply does NOT work in a real world environment.

Don't bother trying to use it with stored procedures that return nothing.

I have to wait for VS2010 to get something that will supposedly work?

THIS was supposed to work. You told us to move from linq to sql to linq to entities and I did all the conversions for a piece of junk that has put me weeks behind in development and still doesn't work!

I'm ready to give up and simply use an ORM that actually works and actually has support and working examples.

Why release a technology and tell us to use it when you know it is crippled?!!!!

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Moon — Thu, 01 Jan 2009 23:03:31 GMT

In using your example:

Executing Stored Procedures Directly as Commands

It doesn't work!

I run the stored procedure, I even used:

objectContext.SaveChanges()

And upon completion of the changes made to the database, I use a linq to entities query to pull back the data, and it returns the data prior to the running of the stored procedure.

It does not UPDATE!

If I return the same query using ado.net SQLCommand, it does return the latest changes.

Why isn't the entity model updating with the stored procedure changes?????

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Kevin Gallagher (Oregon Department of Revenue) — Wed, 07 Jan 2009 17:19:24 GMT

I can not use LINQ-SQL because IBM doesn't have the capabilities to do DataContext using a designer. But that doesn't stop me from using LINQ on other sections of code so hopefully LINQ is here to stay. Also hope IBM gets on the ball and gets a proper provider out to mirror SQL-Server provider and designer.

ADO.NET EF不方便之处（BUG）收集帖

lovecherry — Thu, 15 Jan 2009 10:49:44 GMT

考虑在项目中使用ADO.NETEF，但是怎么都用不顺手，甚至比L2S少了很多关键功能。

我先列几个让我很郁闷的：

1）存储过程自动封装是一个很常用的功能，在ADO.NETEF中可以和L2S一样...

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Paul — Fri, 23 Jan 2009 18:31:03 GMT

I'm currently trying to use the Entity Framework for a business application. The task of trying to insert a row into a many to many table with no primary key seems mind bogglingly complicated. From what I've read on numerous forums, if I want to achieve this I will need to:

a) Create a stored procedure containing a basic insert statement

b) Create a function import to my model

c) Because my stored procedure doesn't return anything I'll then need to create a partial class where I need to write code which is very similar to standard ADO.NET code

Is this correct?

I find myself writing more code than I did when all we had was the basic ADO objects.

My personal opinion so far (and I'm not one to base my opinions on anything I've read) is that the EF is slow, clumsy and shoddily put together. The designer is also a nightmare and the entire model is almost unworkable in any kind of team using source control.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Yuriy Gizhitsa — Wed, 04 Feb 2009 23:01:45 GMT

I bloged stored peoc usage here:http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.entry. You might find it interesting

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Yuriy Gizhitsa — Wed, 04 Feb 2009 23:02:02 GMT

I blogged stored peoc usage here:http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.entry. You might find it interesting

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Rabeeh Abla — Tue, 10 Mar 2009 18:25:00 GMT

Hi there,

We use database store procedures for many cases, mostly when we want to operate on portion of data on the database without sending these data to the client application.

We set permissions on stored procedure to enable specified database users to execute them.

For CRUD actions a generated SQL script from Entity model solves the case, but this is not always the case, this solves case for simple databases.

We would like more support for Store procedures. We want to call the stored procedure directly from context object and without the need for function import & mapping these functions to an entity.

Best Regards

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Matt F. — Fri, 27 Mar 2009 03:52:40 GMT

I am using VS 2008 SP1 with .Net 3.5 SP1 and this just does not work. I created a stored procedure for each function type and even validated the methods creation in the XML in the edmx files. I get a good compile but nothing even close to my function name is available in the namespace or class. Nothing on the web indicates how to correct this problem. This is extremely frustrating when you can't even get a simple stored procedure to work in a new technology.

Has anyone gotten any stored procedures to work in VS 2008 and ADO.Net Entity Framework?

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

medyum — Fri, 22 May 2009 13:55:30 GMT

would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application....

They need to ensure an upgrade utility to ef.

man what a nightmare...

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

hikaye — Sat, 04 Jul 2009 22:26:39 GMT

This is fantastic. I am going to add it to my sites. Thanks!

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

radyo dinle — Sun, 19 Jul 2009 17:36:26 GMT

Is it possible to have the same field in both entities? In your example, I would like to have Name both in Product and in ProductImages.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

ssk sorgulama — Thu, 23 Jul 2009 17:26:15 GMT

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

zerrin egeliler — Thu, 23 Jul 2009 19:43:27 GMT

would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application....

They need to ensure an upgrade utility to ef.

man what a nightmare...

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Scott Buchanan — Tue, 20 Oct 2009 15:29:20 GMT

I must be in the minority because I am not a happy L2S user. Our database is accessed strictly via stored procs. After living with poor support in VS2008 for object changes (had to use Huagati add-in) we just realized that all SPs are run as text, not SP calls. If EF fixes that, I would migrate immediately.

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

Waleed — Wed, 21 Oct 2009 21:31:23 GMT

re: Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

ankara parke — Sat, 19 Dec 2009 11:14:19 GMT

would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application....

They need to ensure an upgrade utility to ef.

man what a nightmare...