tag:blogger.com,1999:blog-5238131531249720750.post7783512568940446918..comments2024-03-19T13:28:26.291+01:00Comments on Everything SQL Server Compact: Fix for Entity Framework poor INSERT performance with SQL Server Compact and server generated keysErikEJhttp://www.blogger.com/profile/12641272161018114573noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-5238131531249720750.post-32265824144318916712013-02-22T22:31:02.611+01:002013-02-22T22:31:02.611+01:00Thanks Eric, got it working. Your blog was invalua...Thanks Eric, got it working. Your blog was invaluable for that.Alexander Kohlhttps://www.blogger.com/profile/03374852780752404115noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-1209602943016397742013-02-21T22:33:20.555+01:002013-02-21T22:33:20.555+01:00Anthony: Thanks very much, your proposal is the &q...Anthony: Thanks very much, your proposal is the "correct" fix. And LINQ to SQL suffers from the same issue, yes, but has much less overhead when it comes to database ceation and adding objects (both less than 500 ms)ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-3180839131624724662013-02-21T19:15:11.580+01:002013-02-21T19:15:11.580+01:00Anthony: Thanks, that was very useful information,...Anthony: Thanks, that was very useful information, I will do some testing with rowversion in my model. The only place this can be fixed as I see it is in the EF provider, as SQL Compact and LINQ to SQL are both eol.ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-81537762005691358932013-02-21T18:00:34.726+01:002013-02-21T18:00:34.726+01:00This is also a problem in LINQ-to-SQL.
Note that ...This is also a problem in LINQ-to-SQL.<br /><br />Note that in L2S (and maybe also EF?), the query to return the server generated ID will also potentially include other columns, such as a rowversion column, e.g:<br /><br />SELECT [Id], [RowVersion] FROM [Student] WHERE [Id] = @@IDENTITY<br /><br />As such, I'm not sure that simply doing "SELECT CAST(@@IDENTITY AS int) AS [Id]" is sufficient.<br /><br />However, the query optimizer *will* do the right thing if you cast the @@IDENTITY variable to int before doing the select:<br /><br />SELECT [Id], [RowVersion] FROM [Student] WHERE [Id] = CAST(@@IDENTITY AS INT)<br /><br />I think the 'correct' place to fix this would be in SQL CE itself, I'd see this as being a issue with the CE query optimizer rather than the queries generated by EF/L2S.<br /><br />Unfortunately I can't see any obvious way to bodge L2S to do the right thing...Anonymoushttps://www.blogger.com/profile/06835734053171690433noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-17126345785003205542013-02-20T23:34:17.093+01:002013-02-20T23:34:17.093+01:00Alex: Use the Toolbox file version checker to dete...Alex: Use the Toolbox file version checker to determine the version, and I will tell you how to proceedErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-85082153165112767362013-02-20T23:27:30.646+01:002013-02-20T23:27:30.646+01:00This comment has been removed by the author.Alexander Kohlhttps://www.blogger.com/profile/03374852780752404115noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-35858494249591433952013-02-20T14:46:17.555+01:002013-02-20T14:46:17.555+01:00Andre: I think the team will take the fix seriousl...Andre: I think the team will take the fix seriously, but I just need to submit it, and then they will revei/polish and include in the master branch - after taht it will be available in the source and all future builds.ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-83808255498064657542013-02-20T08:34:13.268+01:002013-02-20T08:34:13.268+01:00Thanks for your good work! How will we know if and...Thanks for your good work! How will we know if and when the EF-team will act on this problem?Anonymoushttps://www.blogger.com/profile/10755848661590131454noreply@blogger.com