http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660The SQL statement has uppercase keywords in Short SQL is upper, but it appears with lowercase keywords under the Details tab http://ayende.com/4566/ef-prof-and-code-only#comment27http://ayende.com/4566/ef-prof-and-code-only#comment27Mon, 02 Aug 2010 19:38:27 GMTIf you add: [Timestamp] public byte[] Timestamp { get; set; } to the NerdDinner sample then profile it, you can see the ROWVERSION being pulled back alongside the IDENTITY. http://ayende.com/4566/ef-prof-and-code-only#comment26http://ayende.com/4566/ef-prof-and-code-only#comment26Fri, 30 Jul 2010 12:56:56 GMTDiego, a) I am sorry, I absolutely didn't consider what DB I am targeting, you are correct. b) I am working through the provider API, previous versions had made assumptions about how things work (accepting IDbConnection), which made things hard. I run into none of that trouble with CTP4 http://ayende.com/4566/ef-prof-and-code-only#comment25http://ayende.com/4566/ef-prof-and-code-only#comment25Fri, 30 Jul 2010 10:37:12 GMTHi Oren, Good to hear that it was easier with the Feature CTP 4. Whatever technique you are using, I would like to make sure that we don't break it in future versions of Code-First. By the way, EF4/SqlClient generally produce inserts that look like this for SQL Server: insert [dbo].[Customers]([Name]) values (@0) select [Id] from [dbo].[Customers] where @@ROWCOUNT > 0 and [Id] = scope_identity() I suspect this version of Nerd Dinner is using SQL Server Compact 4, which doesn't have SCOPE_IDENTITY(), just @@IDENTITY. As far as I know doesn't have trigger support either, so that should be ok :) http://ayende.com/4566/ef-prof-and-code-only#comment24http://ayende.com/4566/ef-prof-and-code-only#comment24Fri, 30 Jul 2010 10:29:50 GMTOh no! :X Maannnnn... I'm stupid, this early on a friday morning. :) The first name is the table name of course... *hits wall with head* http://ayende.com/4566/ef-prof-and-code-only#comment23http://ayende.com/4566/ef-prof-and-code-only#comment23Fri, 30 Jul 2010 07:36:18 GMTFrans, I am sorry, but I don't see what you are talking about. Are you talking about the indentation? http://ayende.com/4566/ef-prof-and-code-only#comment22http://ayende.com/4566/ef-prof-and-code-only#comment22Fri, 30 Jul 2010 07:35:48 GMTThe '()' wrapping in the INSERT statement is wrong: the opening '(' is after the first field, not before it. :) Is that the bug? @@IDENTITY can be beneficial btw, when insert triggers are used, @@IDENTITY then is the right value instead of SCOPE_IDENTITY(), but in general, this of course should be configurable (and SCOPE_IDENTITY() should be the default, not @@IDENTITY) http://ayende.com/4566/ef-prof-and-code-only#comment21http://ayende.com/4566/ef-prof-and-code-only#comment21Fri, 30 Jul 2010 07:33:57 GMTShouldn't some values be displayed in the "Application Statistics" section? http://ayende.com/4566/ef-prof-and-code-only#comment20http://ayende.com/4566/ef-prof-and-code-only#comment20Fri, 30 Jul 2010 05:39:04 GMT@Pure, yes that is what I believe it does. Sure, it could also special case to just return the identity itself when there is only one too. Even better would be to use OUTPUT values. I've no idea why it isn't using SCOPE_IDENTITY. [)amien http://ayende.com/4566/ef-prof-and-code-only#comment19http://ayende.com/4566/ef-prof-and-code-only#comment19Fri, 30 Jul 2010 05:11:21 GMTMakes sense about the select, except it's only selecting the ID, so the select is unnecessary except in a trigger case where @@IDENTITY returned the wrong identity value. (from a different table due to a trigger) SELECT @@IDENTITY would have returned the incorrect ID, while that SELECT construct would return #null instead. @@IDENTITY vs SCOPE_IDENTITY() definitely looks like a problem. I goofed, I thought Guids could be marked as Identities, but they can only be numeric types. Type-mismatch is a moot issue, oddly enough. Even if the identity numeric types are different sizes (smallint vs. bigint) and the @@IDENTITY returned is out of range of the inspected data type, #null is returned in the SELECT WHERE ID = @@IDENTITY scenario. I'm guessing EF returns the PK (identity) so it can wire up the 1:M references. I'm wondering if they aren't checking for a #null PK, then doing some "match this combination" expensive query to find newly inserted rows in the exceptional cases where the identity match doesn't come back. http://ayende.com/4566/ef-prof-and-code-only#comment18http://ayende.com/4566/ef-prof-and-code-only#comment18Fri, 30 Jul 2010 02:47:56 GMTPure Krome, That is how I understand what he is saying http://ayende.com/4566/ef-prof-and-code-only#comment17http://ayende.com/4566/ef-prof-and-code-only#comment17Fri, 30 Jul 2010 01:12:19 GMTHi Ayende. So Damien means that if the table has default values on some fields like … OnCreated DATETIMEOFFSET, LastModified DATETIMEOFFSET ..etc .. those fields will also be returned as part of the statement? Eg. SELECT [DinnerId]. [OnCreated]. [LastModified] FROM [Dinners] WHERE [DinnerId] = @@IDENTITY ? :) http://ayende.com/4566/ef-prof-and-code-only#comment16http://ayende.com/4566/ef-prof-and-code-only#comment16Fri, 30 Jul 2010 01:10:14 GMTPure Krome, Imagine that you had a field that is being set by a default value, or a trigger. That is what Damien means when he is talking about server generated value. http://ayende.com/4566/ef-prof-and-code-only#comment15http://ayende.com/4566/ef-prof-and-code-only#comment15Fri, 30 Jul 2010 01:05:09 GMTTuna, No, that one is always on for my builds, a private joke http://ayende.com/4566/ef-prof-and-code-only#comment14http://ayende.com/4566/ef-prof-and-code-only#comment14Fri, 30 Jul 2010 01:01:26 GMTDamien, Thanks for the clarification Why does it uses @@IDENTITY, then? The recommendation is SCOPE_IDENTITY() to avoid issues with triggers. http://ayende.com/4566/ef-prof-and-code-only#comment13http://ayende.com/4566/ef-prof-and-code-only#comment13Fri, 30 Jul 2010 01:00:26 GMTI am guessing it is selecting from the table rather than just returning the identity to support situations where more than one 'auto-generated' column might need to be returned (ROWVERSION in SQL Server comes to mind). They should definitely be using SCOPE_IDENTITY(), but that's a different issue obviously. http://ayende.com/4566/ef-prof-and-code-only#comment12http://ayende.com/4566/ef-prof-and-code-only#comment12Fri, 30 Jul 2010 00:18:50 GMT@Damien Guard : You said "It does a full select rather than just the ID so that it can pull back *all* server-generated values rather than just the identity." .. er. I don't understand what you mean *blush*. Can you elaborate? From looking at that sql code, it's just returning the Identity value for that table ... which to me is the same as Scope_Identity (which IMO should be what it should be doing .. as what a few other peeps are saying). What are "*all* server-generated values" ?? don't we just want the ID ? *confused* -PK- http://ayende.com/4566/ef-prof-and-code-only#comment11http://ayende.com/4566/ef-prof-and-code-only#comment11Fri, 30 Jul 2010 00:13:23 GMTHere is the difference between 3 ways of getting the identity from MSDN: IDENT_CURRENT(tablename) returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. All of them return a single value and SCOPE_IDENTITY is the obvious choice. http://ayende.com/4566/ef-prof-and-code-only#comment10http://ayende.com/4566/ef-prof-and-code-only#comment10Thu, 29 Jul 2010 23:02:16 GMTIs the bug thing "New version available? thing? http://ayende.com/4566/ef-prof-and-code-only#comment9http://ayende.com/4566/ef-prof-and-code-only#comment9Thu, 29 Jul 2010 22:48:14 GMT@Damien: @@IDENTITY returns back the last generated ID across the current connection. (not session) It doesn't return back multiple IDs, just the last one the server created before the call. That means as soon as you try and insert a record that has a trigger that inserts a new log entry you'll get back the log entry's identity, then try and select that log's key from the dinners and likely get either an exception (incompatible types on the indentiy columns: i.e. int vs. guid or datetime) Null, (not found) or worse, the wrong record key. (log ID match found in dinners table.) http://ayende.com/4566/ef-prof-and-code-only#comment8http://ayende.com/4566/ef-prof-and-code-only#comment8Thu, 29 Jul 2010 22:26:51 GMTRe: @@IDENTITY, I'll say! To me that looks like a potential bug-bear of a problem that I'm surprised hasn't been raised and fixed by now. As soon as you bind that object to a table in a schema with a trigger or something that's inserting log entries, etc. that's going to bomb. The "SELECT [DinnersID] FROM [Dinners] WHERE [DinnersID] = @@IDENTITY" is probably some attempt to throw an exception or something in the above case. Why on earth they didn't use SCOPE_IDENTITY() is completely beyond me. I don't know if you want to raise it with them, but I'd certainly like to know if there is a reason for that. http://ayende.com/4566/ef-prof-and-code-only#comment7http://ayende.com/4566/ef-prof-and-code-only#comment7Thu, 29 Jul 2010 22:18:49 GMTNo, POCO is not code first (what code only has been called for over 6 months). POCO is plain old CLR objects. EF4 can use these today but you have to describe the mapping using EDMX files - we have tools to help you generate them. Code First can generate the mapping files from your POCO objects for you - and lets you tweak them with it's fluent API. @ayende: It does a full select rather than just the ID so that it can pull back *all* server-generated values rather than just the identity. As for the bug: Possible queries executed 0? When in fact it isn't a bug as it's an insert and the select included with it is not a user-query? [) http://ayende.com/4566/ef-prof-and-code-only#comment6http://ayende.com/4566/ef-prof-and-code-only#comment6Thu, 29 Jul 2010 22:15:27 GMTBas, Yep :-) Martin, Um... no :-) configurator, Damn, but that was fast. Yes on both counts. That actually confused _me_. And yes, POCO is Code Only http://ayende.com/4566/ef-prof-and-code-only#comment5http://ayende.com/4566/ef-prof-and-code-only#comment5Thu, 29 Jul 2010 22:02:44 GMTIs "Code Only" what Scott refers to when he mentions "POCO support"? Or did I miss something? http://ayende.com/4566/ef-prof-and-code-only#comment4http://ayende.com/4566/ef-prof-and-code-only#comment4Thu, 29 Jul 2010 21:59:21 GMT> Just for fun, the following screen shot looks like it contains a bug, but it doesn’t (at least, not to my knowledge). If you can spot what the bug is, I am going to hand you a 25% discount coupon for EF Prof. There is a picture of what looks to be a bug in the top left hand corner of the window. > If you can tell me why it is not a bug, I would double that. It's actually not a picture of a bug, but of a rhino head. ;) http://ayende.com/4566/ef-prof-and-code-only#comment3http://ayende.com/4566/ef-prof-and-code-only#comment3Thu, 29 Jul 2010 21:58:31 GMTMy guess: Why is the application statistics all on 0? We can see three object contexts and a statement right there and yet it says there weren't any. It's not a bug because you have a selection thing where OdbcFactory is selected - and for that factory there weren't any object contexts/queries/etc. http://ayende.com/4566/ef-prof-and-code-only#comment2http://ayende.com/4566/ef-prof-and-code-only#comment2Thu, 29 Jul 2010 21:57:15 GMT0 transactions? http://ayende.com/4566/ef-prof-and-code-only#comment1http://ayende.com/4566/ef-prof-and-code-only#comment1Thu, 29 Jul 2010 21:50:20 GMT