Ayende @ Rahien

Refunds available at head office

EF Prof and Code Only

I just finish touching up a new feature for EF Prof, support for Entity Framework’s Code Only feature. What you see below is EF Prof tracking the Code Only Nerd Dinner example:

image

I tried to tackle the same thing in CTP3, but I was unable to resolve it. Using CTP4, it was about as easy as I could wish it.

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. If you can tell me why it is not a bug, I would double that.

As an aside, am I the only one that is bothered by the use of @@IDNETITY by EF? I thought that we weren’t supposed to make use of that. Moreover, why write this complex statement when you can write SELECT @@IDENTITY?

Comments

Bas
07/29/2010 09:50 PM by
Bas

0 transactions?

configurator
07/29/2010 09:57 PM by
configurator

My 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.

Martin Aatmaa
07/29/2010 09:58 PM by
Martin Aatmaa

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.

;)

configurator
07/29/2010 09:59 PM by
configurator

Is "Code Only" what Scott refers to when he mentions "POCO support"? Or did I miss something?

Ayende Rahien
07/29/2010 10:02 PM by
Ayende Rahien

Bas,

Yep :-)

Martin,

Um... no :-)

configurator,

Damn, but that was fast.

Yes on both counts. That actually confused me.

And yes, POCO is Code Only

Damien Guard
07/29/2010 10:15 PM by
Damien Guard

No, 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?

[)

Steve Py
07/29/2010 10:18 PM by
Steve Py

Re: @@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.

Steve Py
07/29/2010 10:26 PM by
Steve Py

@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.)

Tuna Toksoz
07/29/2010 10:48 PM by
Tuna Toksoz

Is the bug thing "New version available? thing?

Dmitry
07/29/2010 11:02 PM by
Dmitry

Here 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.

Pure Krome
07/30/2010 12:13 AM by
Pure Krome

@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-

Matthew Wills
07/30/2010 12:18 AM by
Matthew Wills

I 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.

Ayende Rahien
07/30/2010 01:00 AM by
Ayende Rahien

Damien,

Thanks for the clarification

Why does it uses @@IDENTITY, then? The recommendation is SCOPE_IDENTITY() to avoid issues with triggers.

Ayende Rahien
07/30/2010 01:01 AM by
Ayende Rahien

Tuna,

No, that one is always on for my builds, a private joke

Ayende Rahien
07/30/2010 01:05 AM by
Ayende Rahien

Pure 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.

Pure Krome
07/30/2010 01:10 AM by
Pure Krome

Hi 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

?

:)

Ayende Rahien
07/30/2010 01:12 AM by
Ayende Rahien

Pure Krome,

That is how I understand what he is saying

Steve Py
07/30/2010 02:47 AM by
Steve Py

Makes 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.

Damien Guard
07/30/2010 05:11 AM by
Damien Guard

@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

Yitzchok
07/30/2010 05:39 AM by
Yitzchok

Shouldn't some values be displayed in the "Application Statistics" section?

Frans Bouma
07/30/2010 07:33 AM by
Frans Bouma

The '()' 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 SCOPEIDENTITY(), but in general, this of course should be configurable (and SCOPEIDENTITY() should be the default, not @@IDENTITY)

Ayende Rahien
07/30/2010 07:35 AM by
Ayende Rahien

Frans,

I am sorry, but I don't see what you are talking about.

Are you talking about the indentation?

Frans Bouma
07/30/2010 07:36 AM by
Frans Bouma

Oh 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

Diego Vega
07/30/2010 10:29 AM by
Diego Vega

Hi 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

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 :)

Ayende Rahien
07/30/2010 10:37 AM by
Ayende Rahien

Diego,

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
Matthew Wills
07/30/2010 12:56 PM by
Matthew Wills

If 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.

Richard Dingwall
08/02/2010 07:38 PM by
Richard Dingwall

The SQL statement has uppercase keywords in Short SQL is upper, but it appears with lowercase keywords under the Details tab

Comments have been closed on this topic.