May 16, 2016 at 10:07 pm
Comments posted to this topic are about the item 3 Things I Wish I Knew When I Started Using Entity Framework
May 16, 2016 at 11:35 pm
Entity Framework supports SQL server stored procedures and from June 1 it would also support natively compiled stored procedures for in-memory database.
I wish that Microsift also add support for SQL server synonyms. 😎
May 17, 2016 at 12:21 am
The reason that the 3rd example parameterizes your SQL is because it is generating a reusable query plan.
If you have a WHERE created_date>'2016-05-17' this will generate a different query plan to WHERE created_date>'2016-05-16'
When parameterized it will recognise the query and reuse.
However if you are comfortable with using stored procedures then you are leaning on an open door as far as I am concerned ??
May 17, 2016 at 3:22 am
I'm going to say there is absolutely nothing wrong with the query in point 3. It's a little ungainly looking, sure, but that is here nor there regarding performance. I'd certainly argue that you should always parameterise string based queries for many good reasons (hint, security!)
Just in case anyone is reading and wants to move to the next level there are a few cases where calling stored procedures is not straightforward - output parameter and user defined table type input parameters. The latter are the best way I have found to execute arbitrary multiple updates from a stored proc. There are a few ways to do this but recently I like the EntityFrameworkExtras package from Mike Fodsuk.
May 17, 2016 at 3:52 am
With all due respect getting output parameter and return value from stored procedure via EF should not be a "black belt" coding endevour 🙁 What really escapes me that EF team consciously decided not be bothered with SQL Server improvements (like synonyms) or TVP.
May 17, 2016 at 4:00 am
Great article, thanks.
For me, point 1 hit home the most. I recently wrote an application that queried a table with a varbinary(max) column without specifying the columns that I required in the resultset. Needless to say, performance got pretty bad when the users started to populate the table. I changed the query to use the Select method to simply select the columns required for the resultset and normal service was resumed!
I also find Entity Framework great for returning lookup values. If you have an FK constraint set up in your database and this is reflected in your model, EF creates the join seamlessly for you.
CREATE TABLE [dbo].[Customer](
[CustomerID] [INT] NOT NULL,
[FullName] [NVARCHAR](255) NULL,
[CustomerTypeID] [INT] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
CREATE TABLE [dbo].[CustomerType](
[CustomerTypeID] [INT] NOT NULL,
[Name] [NVARCHAR](255) NULL,
CONSTRAINT [PK_CustomerType] PRIMARY KEY CLUSTERED
(
[CustomerTypeID] ASC
)
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [FK_Customer_CustomerType] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerType] ([CustomerTypeID])
GO
you can return the lookup value by using a query like this:
Dim query = From a In db.Customer
Select New With {
.CustomerID = a.CustomerID,
.CustomerFullName = a.FullName,
.CustomerType = a.CustomerType.Name
}
May 17, 2016 at 5:45 am
"a feature that removes a lot of the tedious boiler plate code that developers would otherwise have to write." Well, we do have code generating software for that. I still like creating my own custom DAL but I have to admit that I don't know much about Entity Framework.
May 17, 2016 at 6:31 am
Thanks for the article.
May 17, 2016 at 6:44 am
"One of the issues (features?) with Entity Framework is that by default the queries all run as SELECT *. Instead of bringing back all of the columns in a table"
I'm not experiencing this...not using Select() when I run a LINQ query, I get just the columns defined in my entity.
I'm using EF 6.1 "code first," wonder what the difference is between what you did and what I'm doing. Maybe it has something to do with the way your entity is mapped?
May 17, 2016 at 7:58 am
fregatepllada (5/17/2016)
With all due respect getting output parameter and return value from stored procedure via EF should not be a "black belt" coding endevour 🙁 What really escapes me that EF team consciously decided not be bothered with SQL Server improvements (like synonyms) or TVP.
I fully agree, however for us at least the rest of the framework makes it well worth using, certainly for MVC projects. Not sure about Web API / Angular hybrids yet!
To some extent Steve's editorial dealt with this:
http://www.sqlservercentral.com/articles/Editorial/140658/
"Making the decision on how to actually implement these changes shouldn't be taken lightly, and I'm happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version."
May 17, 2016 at 8:00 am
I appreciate the article - I've had developers anxious to try entity, and we've experienced each of these issues ad-naseum. Performance was terrible, and the 'new and improved' applications ate up twice the cpu and bandwidth of the old ASP.NET apps they were replacing. There are some other Entity features to turn off - when they found that a single web page was making a dozen or so calls to the database before it even hit the SQL request.
May 17, 2016 at 8:57 am
The attitude of many developers toward SQL and relational databases has always puzzled me.
The database provides data persistence, can help enforce data integrity, and simplifies many relationship oriented patterns.
All interesting development work is "difficult" and "tedious" in some sense. When we turn to automated code generators to "ease our task" we are depending on deterministic, rule driven code to produce the desired result. Performance, efficiency, and readability are not the main objectives of the tool. It does not matter whether the tool is producing C#, HTML, or SQL.
If you are working on a one-off, demo, or prototype then generate away. If you are putting code in production then Plan For Success and make sure the code has good performance, is efficient, and is readable.
May 17, 2016 at 11:36 am
I'm just curious. I'm just getting started with EF. Should this code:
[font="Courier New"][font="Courier New"]SELECT
[Extent1].[State] AS [State],
[Extent1].[Name] AS [Name],
[Extent1].[Name] AS [Address],
[Extent1].[Name] AS [Email]
FROM
[dbo].[Customer] AS [Extent1]
WHERE
[Extent1].[CreateDate] > @p__linq__0[/font]
[/font]actually read:
[font="Courier New"]SELECT
[Extent1].[State] AS [State],
[Extent1].[Name] AS [Name],
[Extent1].[Address] AS [Address],
[Extent1].[Email] AS [Email]
FROM
[dbo].[Customer] AS [Extent1]
WHERE
[Extent1].[CreateDate] > @p__linq__0
[/font]
If not, why?
May 17, 2016 at 12:19 pm
When using EF/Linq2SQL, I always use method calls to stored procedures to do any CRUD work.
Any tool that treats logical and physical models as the same thing is asking for disaster when it's time to scale.
May 17, 2016 at 12:54 pm
You gotta just love the way ORM does this:
var results = db.Customer.Where(c => c.State == "Ohio");
what really was running behind the scenes was this:
SELECT
*
FROM
Customer
WHERE
State = 'Connecticut'
And there you go... ORM changed the where clause on you, from Ohio to Connecticut.
(Yes, I know it's just a typo in the article.... but it was too good to pass up)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply